Transaction Serializable won't work as expected

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Volks,

I just have the old Problem of inserting Data into a Table with an
autoincrement Primary Key: "How to get the f*** generated Key back" ^^

Ok, no Problem, Start a Transaction, insert the Data make a "Select
max(ID_Column) from MyTable" and you have the ID - I thought ...

.... but I get 10% wrong Keys.

My Code (sniped):

//--------------------------------------------------------------------
OleDbConnection conn = new OleDbConnection(dbConnectionString);
conn.Open();
OleDbCommand cmd = conn.CreateCommand();
OleDbTransaction trans = conn.BeginTransaction(IsolationLevel.Serializable);
cmd.Transaction = trans;
cmd.CommandText = "INSERT INTO ins (thread) VALUES (1)";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT max(id) from ins";
int i = Convert.ToInt32(cmd.ExecuteScalar());
trans.Commit();
//--------------------------------------------------------------------

For testing I made 2 tables, one with an autoincrement Field and a
"Thread-ID", one with an Int (for the read ID) and an again the "Thread-ID".
After inserting into the Auto-Table, I read the max(id) and inserted the
same into a Test- Table (just the Key and my Thread-ID). I started two
Proceses of the Program with 10,000 inserts per Program. After this, a
Cross-Join over the two tables showed ~1000 rows with the ID of the other
Program. Tried with OleDB and ODBC at SQLServer 2000.

So, where is my mistake !?

thx

Simon
 
Simon Dallmair said:
Hi Volks,

I just have the old Problem of inserting Data into a Table with an
autoincrement Primary Key: "How to get the f*** generated Key back" ^^

Ok, no Problem, Start a Transaction, insert the Data make a "Select
max(ID_Column) from MyTable" and you have the ID - I thought ...

... but I get 10% wrong Keys.

My Code (sniped):

//--------------------------------------------------------------------
OleDbConnection conn = new OleDbConnection(dbConnectionString);
conn.Open();
OleDbCommand cmd = conn.CreateCommand();
OleDbTransaction trans =
conn.BeginTransaction(IsolationLevel.Serializable);
cmd.Transaction = trans;
cmd.CommandText = "INSERT INTO ins (thread) VALUES (1)";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT max(id) from ins";
int i = Convert.ToInt32(cmd.ExecuteScalar());
trans.Commit();
//--------------------------------------------------------------------
Click to expand...


Just change it to

cmd.CommandText = "SELECT SCOPE_IDENTITY()";

This will retrieve the last inserted identity value for your session.

David


David
 
Back
Top