| Bug #31147 | Isolation level not working | ||
|---|---|---|---|
| Submitted: | 22 Sep 2007 14:42 | Modified: | 10 Jan 2008 14:09 |
| Reporter: | newbie Shai | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | Connector / NET | Severity: | S2 (Serious) |
| Version: | 1.0.9 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[23 Sep 2007 5:34]
Valeriy Kravchuk
Thank you for a problem report. I had not checked your entire code, but here: connectionLocal1.BeginTransaction(IsolationLevel.Snapshot); you explicitely begins a transaction that will see all the data as they are when it begins (.Snapshot). Do you read from this connectionLocal1?
[24 Sep 2007 2:31]
newbie Shai
Dear Valeriy,
No I have a separate connection for the select because is not part of the transaction that is the reason I am using it separately. For the snapshot I was actually trying all kind of different isolation level. Hope to hear from you. Thanks.
[5 Dec 2007 12:11]
Tonci Grgin
Newbie, are you using transactional engine (like innodb) and what is your server mode? Does it include STRICT_TRANS_TABLES?
[5 Dec 2007 12:45]
Tonci Grgin
Newbie, there is a fundamental error in your test case... You are using NET FW 2 functions (IsolationLevel.Snapshot) with NET FW 1 driver (c/NET 1.x). Please either use c/NET 5.x or NET FW 1 with c/NET 1.x.
[6 Dec 2007 15:39]
newbie Shai
Dear Tonce,
I dont get you about STRICT_TRANS_TABLES? What is it all about ? Ok on default what is the read type ? Is snapshot, read committed or which one ? Thanks. Yes I will download the latest .net connector.
[7 Dec 2007 10:20]
Tonci Grgin
Newbie: 1) Please read manual bout SQL modes, especially regarding transactional storage engines. 2) Please note that: a) You can use c/NET 1.x *with* NET FW 1.x and b) You can use c/NET 5.x with NET FW 2.x do not mix them as you do. Waiting on your results after you make match between NET FW and c/NET.
[7 Dec 2007 13:44]
newbie Shai
Dear Tonci,
I have tried to searh on the innodb sql mode but I dont find any information on it? Thanks for the rest of the help. I really appreciate it.
[10 Dec 2007 14:09]
Tonci Grgin
Newbie, http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html. Did you match c/NET version with your NET framework version yet?
[11 Jan 2008 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: Dear All, I have this code where I do 2 reads and 2 updates. The first update will read the original quantity which will be udpated by my first update statement. Following that I have a second read the problem here is that my second read doesnt read the updated value by my first udpate value but give me the original as it was in the first select query. I have tried all kinds of isolationlevel too. I dont know why I cant get my updated value. Any idea why none of the isolation is not working. private void btnSubmit_Click(object sender, EventArgs e) { int rollbackBoolean = 0; MySqlConnection connectionLocal1, connectionLocal2; MySqlTransaction transactionLocal1 = null; connectionLocal1 = new MySqlConnection("Address='localhost';Database='test1';User Name='root';Password='12345'"); connectionLocal2 = new MySqlConnection("Address='localhost';Database=’test1’;User Name='root';Password='12345'"); try { connectionLocal1.Open(); connectionLocal2.Open(); transactionLocal1 = connectionLocal1.BeginTransaction(IsolationLevel.Snapshot); } catch (MySql.Data.MySqlClient.MySqlException ex) { MessageBox.Show( ex.Message); } // my first read String mySelectQuery1 = "Select tblProduct.productTotalStock " + "From tblProduct " + "Where tblProduct.productID=265";; MySqlDataReader myReader1 = null; int currentQuantity = 0; int totalQuantity = 0; MySqlCommand myCommand1 = new MySqlCommand(mySelectQuery1); myCommand1.Connection = connectionLocal2; try { myReader1 = myCommand1.ExecuteReader(); while (myReader1.Read()) { MessageBox.Show("first read : " + Convert.ToInt16(myReader1.GetValue(0).ToString())); currentQuantity = Convert.ToInt16(myReader1.GetValue(0).ToString()); } } catch (MySql.Data.MySqlClient.MySqlException ex) { MessageBox.Show("mySelectQuery1" + ex.Message); } myReader1.Close(); myCommand1.Dispose(); totalQuantity = currentQuantity + 100; //my first update String myUpdateQuery2 = "Update tblProduct " + " Set tblProduct.productTotalStock = " + totalQuantity + " Where tblProduct.productID = 265"; MySqlCommand myCommand2 = new MySqlCommand(myUpdateQuery2); myCommand2.Connection = connectionLocal1; myCommand2.Transaction = transactionLocal1; try { myCommand2.ExecuteNonQuery(); } catch (MySql.Data.MySqlClient.MySqlException ex) { rollbackBoolean = 1; MessageBox.Show("myUpdateQuery2d" + ex.Message); } myCommand2.Dispose(); //my second read String mySelectQuery2 = "Select tblProduct.productTotalStock " + "From tblProduct " + "Where tblProduct.productID=265"; ; MySqlDataReader myReader2 = null; MySqlCommand myCommand4 = new MySqlCommand(mySelectQuery2); myCommand4.Connection = connectionLocal2; try { myReader2 = myCommand4.ExecuteReader(); while (myReader2.Read()) { MessageBox.Show("second read : " + Convert.ToInt16(myReader2.GetValue(0).ToString())); currentQuantity = Convert.ToInt16(myReader2.GetValue(0).ToString()); } } catch (MySql.Data.MySqlClient.MySqlException ex) { MessageBox.Show("mySelectQuery2" + ex.Message); } myReader2.Close(); myCommand4.Dispose(); totalQuantity = currentQuantity + 400; //my second update String myUpdateQuery3 = "Update tblProduct " + " Set tblProduct.productTotalStock = " + totalQuantity + " Where tblProduct.productID = 265"; MySqlCommand myCommand3 = new MySqlCommand(myUpdateQuery3); myCommand3.Connection = connectionLocal1; myCommand3.Transaction = transactionLocal1; try { myCommand3.ExecuteNonQuery(); } catch (MySql.Data.MySqlClient.MySqlException ex) { rollbackBoolean = 1; MessageBox.Show("myUpdateQuery3" + ex.Message); } myCommand3.Dispose(); if (rollbackBoolean == 1) { transactionLocal1.Rollback(); } else { try { //transactionLocal.Commit(); } catch (MySql.Data.MySqlClient.MySqlException ex) { try { transactionLocal1.Rollback(); } catch (MySqlException ex1) { MessageBox.Show("An exception of type " + ex.GetType() + " was encountered while inserting the data."); if (transactionLocal1.Connection != null) { MessageBox.Show("An exception of type " + ex1.GetType() + " was encountered while attempting to roll back the transaction."); } } } finally { connectionLocal1.Close(); } } if (rollbackBoolean == 1) { } else { } } How to repeat: Isolation is not working.