none
SQL Seems to Ignore UPDLOCK & ROWLOCK Hints RRS feed

  • Question

  •  

    I've got a SELECT WITH (UPDLOCK, ROWLOCK) WHERE followed by an UPDATE WHERE statement. The results of the SELECT statement are deserialized in C# and updates are made to the deserialized object. Then the object is serialized back into the table with the UPDATE statement. I've got this code running within a transaction scope with the ReadCommited isolation level.

     

    My service receives requests to update data and the requests can come in on different threads. What I'm seeing, is that once in a while, the log messages from my application indicate that two different threads are able to issue the above SELECT statement and both are receiving results. This is a problem since the thread that issues the last UPDATE will overwrite the changes made by the first. Each thread has its own connection and transaction scope.

     

    I've researched all over the place and have tried a few different things, but all things point to the fact that query hints are just hints and that SQL may or may not pay attention to them. If that's the case, how am I suppose to perform a SELECT with the intention of updating so that no one else can do the same? I haven't tried table level locking, but I'd really like to avoid that if possible.

     

    -Mike

    Monday, December 3, 2007 3:45 PM

Answers

  •  

    I finally created a simple test project to repro the issue and play with it until its fixed. I found what I was doing wrong; I was using TransactionScope like I used the SqlTransaction class (Open, Begin, Commit, Begin Commit, etc. Close):

     


    Code Block

    using(SqlConnection connection = new SqlConnection())

    {

    //

    //Open the connetion

    //

    connection.Open();

     

    //

    // Begin a transaction.

    //

    using(TransactionScope scope = new TransactionScope())

    {

    //

    // SELECT, UPDATE, ETC

    //

     

    scope.Complete();

    }

    }

     

     

     

    I thought that once you opened a connection, the following SqlCommand objects would enlist in the ambient transaction; which would allow me to execute multiple SQL statements within different subsequent transaction scopes. Apparently, it's the connection that enlists in the transaction, because if I opened the connection outside the scope, it didn't matter what I did in the scope, the SQL commands were not in the transaction. The following change was what made it work:

     

    Code Block

    using(SqlConnection connection = new SqlConnection())

    {

    //

    // Begin a transaction.

    //

    using(TransactionScope scope = new TransactionScope())

    {

    //

    //Open the connetion

    //

    connection.Open();

     

    //

    // SELECT, UPDATE, ETC

    //

     

    scope.Complete();

    }

    }

     

     

     

    Opening the connection inside the TransactionScope tells the connection to enlist in the transaction. This feels awkward to me because it forces me to only have one transaction scope with the connection. I tested it and when I try to do something inside of another (subsequent) transaction, the connection ignores the second scope.

     

    Once I made the change, executing 5000 SELECT/UPDATE statements at the same time on the same record in a table with a 100 other records worked great. I used a flag to determine if two SELECT's returned at the same time, and none of them did.

     

    BTW, I kept the HOLDLOCK....just in case....

    Tuesday, December 4, 2007 2:02 AM

All replies

  • Mike:

     

    This is knee-jerk, but it seems like that when I wanted to something similar that you also need the HOLDLOCK optimizer hint.  Try adding this hint and see if it works correctly.  Also, give this a look in books online.

     

    Monday, December 3, 2007 3:59 PM
    Moderator
  • Kent,

     

    Thanks for the reply. I thought about using HOLDLOCK but never actually tried it. BOL states that UPDLOCK holds the lock until the end of the transaction, which implies to me that I shouldn't have to use HOLDLOCK.

     

    I did try it and it "seems" better, but I still get overlaps in my SELECT statement. I'm currently using log4net to determine the order in which things happen. I wait until I see something that doesn't look right, then track it down in the log files. The log file tells me the time (down to the millisecond) and thread. I tried using SQL Profiler, but the locking events seem pretty useless. Is there a better way to verify that SQL is in fact ignoring my locking hints? Can you think of anything else that might be wrong?

     

    -M

    Monday, December 3, 2007 6:15 PM
  •  

    I finally created a simple test project to repro the issue and play with it until its fixed. I found what I was doing wrong; I was using TransactionScope like I used the SqlTransaction class (Open, Begin, Commit, Begin Commit, etc. Close):

     


    Code Block

    using(SqlConnection connection = new SqlConnection())

    {

    //

    //Open the connetion

    //

    connection.Open();

     

    //

    // Begin a transaction.

    //

    using(TransactionScope scope = new TransactionScope())

    {

    //

    // SELECT, UPDATE, ETC

    //

     

    scope.Complete();

    }

    }

     

     

     

    I thought that once you opened a connection, the following SqlCommand objects would enlist in the ambient transaction; which would allow me to execute multiple SQL statements within different subsequent transaction scopes. Apparently, it's the connection that enlists in the transaction, because if I opened the connection outside the scope, it didn't matter what I did in the scope, the SQL commands were not in the transaction. The following change was what made it work:

     

    Code Block

    using(SqlConnection connection = new SqlConnection())

    {

    //

    // Begin a transaction.

    //

    using(TransactionScope scope = new TransactionScope())

    {

    //

    //Open the connetion

    //

    connection.Open();

     

    //

    // SELECT, UPDATE, ETC

    //

     

    scope.Complete();

    }

    }

     

     

     

    Opening the connection inside the TransactionScope tells the connection to enlist in the transaction. This feels awkward to me because it forces me to only have one transaction scope with the connection. I tested it and when I try to do something inside of another (subsequent) transaction, the connection ignores the second scope.

     

    Once I made the change, executing 5000 SELECT/UPDATE statements at the same time on the same record in a table with a 100 other records worked great. I used a flag to determine if two SELECT's returned at the same time, and none of them did.

     

    BTW, I kept the HOLDLOCK....just in case....

    Tuesday, December 4, 2007 2:02 AM