SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Transaction and Lock Statement

    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.

    Code:
    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. #2
    Non-Member
    Join Date
    Nov 2010
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is using mysql database, and here’s some information I can find related to Lock and Commit problems.

    http://www.roseindia.net/mysql/mysql...-locking.shtml


    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. #3
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,633
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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 . . .


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •