Transaction and Lock Statement

.NET
1

Hi guys, I am confused with the commit statement in MySql. I encountered an error in my program due to lock statement. However, one of my statement was successfully executed even if I have a rollback statement - the INSERT statement successfully executed.

HERE’s the original code that pertains to my confusion of commit and lock table statements.


try
            {
                cnDBConnection.Open();
                cmStudent.Connection = cnDBConnection;

                cmStudent.CommandText = "START TRANSACTION";
                cmStudent.ExecuteNonQuery();
 
                cmStudent.CommandText = "LOCK TABLES ids READ, ids AS ID WRITE";
                cmStudent.ExecuteNonQuery();

                cmStudent.CommandText = "SELECT id FROM ids";
                rdID = cmStudent.ExecuteReader();
                rdSID.Read();
                strID = double.Parse(rdID["id"].ToString()).ToString("00000000"); 
                rdID.Close();

                
                cmStudent.CommandText = "LOCK TABLES studentreg WRITE";
                cmStudent.ExecuteNonQuery();
 
                cmStudent.CommandText = "INSERT INTO reg VALUES(@studentid, @lastname, @firstname," +
                                            "@middlename, @citizenship, @prevschool, @prevcourse, @currcourse," +
                                            "@gender)";
                cmStudent.Parameters.AddWithValue("@studentid", strStudentID);
                cmStudent.Parameters.AddWithValue("@lastname", txtLastName.Text.ToUpper());
                cmStudent.Parameters.AddWithValue("@firstname", txtFirstName.Text.ToUpper());
                cmStudent.Parameters.AddWithValue("@middlename", txtMiddleName.Text.ToUpper());
                cmStudent.Parameters.AddWithValue("@citizenship", strCitizenship);
                cmStudent.Parameters.AddWithValue("@prevschool", txtPreviousSchool.Text.ToUpper());
                cmStudent.Parameters.AddWithValue("@prevcourse", string.Empty);
                cmStudent.Parameters.AddWithValue("@currcourse", txtCourse.Text.ToUpper());
                cmStudent.Parameters.AddWithValue("@gender", cboGender.Text);
                cmStudent.ExecuteNonQuery();

                cmStudent.CommandText = "UPDATE ids AS ID SET studentid=studentid+1";
                cmStudent.ExecuteNonQuery();

                cmStudent.CommandText = "COMMIT";
                cmStudent.ExecuteNonQuery(); 

                cmStudent.CommandText = "UNLOCK TABLES";
                cmStudent.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                Response.Write(ex.Message + "<br />" + ex.Source + "<br />" + ex.ToString());
                cmStudent.CommandText = "ROLLBACK";
                cmStudent.ExecuteNonQuery();
            }
            finally
            {
                cmNewStudent.Connection.Close();
                cnDBConnection.Close();
            }

With the given code above, there will be an error exist specifically on LOCK statement. Again my concern is the “INSERT STATEMENT” executed successfully.

2

This is using mysql database, and here’s some information I can find related to Lock and Commit problems.

Uses of LOCK TABLES with transactional tables:

As we discussed LOCK TABLES is not transaction safe and implicitly performs a commit operation on any active transactions before performing to lock the table. And beginning a transaction implicitly performs an UNLOCK TABLES.
For using LOCK TABLES with transactional tables like InnoDB, we have to set AUTOCOMMIT=0 and we don?t call UNLOCK TABLES until we commit the transactional explicitly. If we are calling LOCK TABLES then InnoDB internally takes it own table lock and MySQL is also takes its own table lock. At the next commit InnoDB released its table lock but for releasing MySQL we have to call UNLOCK TABLES. But if we do AUTOCOMMIT =1 then InnoDB released the table lock immediately after calling of LOCK TABLES that?s why deadlocks can easily occur.
ROLLBACK cannot release the MySQL non transactional table locks.

Still, logically it should not happen.

3

Never used MySql from .NET but shouldn’t you be using .NET transactions here rather than passing commands to MySql. That is how it has worked for most other data platforms that support transactions, at least for me . . .