none
Cursor's ROWLOCK hint not honoured RRS feed

  • Question

  • Hello,

    hopefully someone will be able to help me with this - on MSSQL 2008 I'm trying to open two T-SQL cursors in two separate connections, reading two separate records from the same table using FETCH command, each cursor having a ROWLOCK hint specified for locking their records. Unfortunately, what seems to happen is that the ROWLOCK hint is not honoured and some coarser locks are used, which then leads to locking errors (lock timeouts). ROWLOCK info e.g. http://msdn.microsoft.com/en-us/library/ms172398%28v=sql.100%29.aspx

    The broader context is an application which has been migrated from MSSQL 2000, the cursors are used for seeking, retrieving, locking and updating records by users from the client environment. Currently the users get the mentioned errors when trying to maintain multiple records in the same tables at the same time.

    Bellow is a code to reproduce this. It looks as a bug in MSSQL 2008 to me. Or the least to say the server behaves somewhat quirky when dealing with cursors and locking. If you also think this is a bug then please confirm, many thanks.

    -- cursor_with_rowlock.sql
    -- MSSQL 2008 (10.0.1600.22)
    
    -- DDL prepare
    CREATE DATABASE [TestDb]
    GO
    ALTER DATABASE [TestDb] SET COMPATIBILITY_LEVEL = 100
    GO
    USE [TestDb]
    GO
    
    CREATE TABLE [TestTable] ([Id] int PRIMARY KEY, [Name] varchar(100), [Status] bit)
    GO
    INSERT INTO [TestTable] VALUES (1, 'aaa', 1)
    INSERT INTO [TestTable] VALUES (2, 'bbb', 1)
    INSERT INTO [TestTable] VALUES (3, 'ccc', 1)
    INSERT INTO [TestTable] VALUES (4, 'ddd', 0)
    INSERT INTO [TestTable] VALUES (5, 'eee', 0)
    GO
    
    
    
    -- separate connection #1
    USE [TestDb]
    GO
    SET LOCK_TIMEOUT 1000
    GO
    DECLARE @IdFilter int, @NameFilter varchar(100), @StatusFilter bit
    SELECT @IdFilter = 2, @NameFilter = NULL, @StatusFilter = NULL
    DECLARE TestCursor1 CURSOR GLOBAL SCROLL DYNAMIC SCROLL_LOCKS FOR
    SELECT * FROM [TestTable] WITH (ROWLOCK SERIALIZABLE UPDLOCK)
    WHERE
      ([Id] = @IdFilter OR @IdFilter IS NULL)
    AND ([Name] LIKE @NameFilter OR @NameFilter IS NULL)
    AND ([Status] = @StatusFilter OR @StatusFilter IS NULL)
    GO
    OPEN GLOBAL TestCursor1
    GO
    FETCH FIRST FROM GLOBAL TestCursor1
    GO
    -- returns 1 record: (2, 'bbb', 1)
    
    
    
    -- separate connection #2
    USE [TestDb]
    GO
    SET LOCK_TIMEOUT 1000
    GO
    DECLARE @IdFilter int, @NameFilter varchar(100), @StatusFilter bit
    SELECT @IdFilter = 4, @NameFilter = NULL, @StatusFilter = NULL
    DECLARE TestCursor2 CURSOR GLOBAL SCROLL DYNAMIC SCROLL_LOCKS FOR
    SELECT * FROM [TestTable] WITH (ROWLOCK SERIALIZABLE UPDLOCK)
    WHERE
      ([Id] = @IdFilter OR @IdFilter IS NULL)
    AND ([Name] LIKE @NameFilter OR @NameFilter IS NULL)
    AND ([Status] = @StatusFilter OR @StatusFilter IS NULL)
    GO
    OPEN GLOBAL TestCursor2
    GO
    FETCH FIRST FROM GLOBAL TestCursor2
    GO
    /*
    Msg 1222, Level 16, State 51, Line 1
    Lock request time out period exceeded.
    */
    -- returns error - but it was expected to return 1 record: (4, 'ddd', 0)
    
    
    
    -- separate connection #1 cleanup
    CLOSE GLOBAL TestCursor1
    GO
    DEALLOCATE GLOBAL TestCursor1
    GO
    USE [master]
    GO
    
    
    
    -- separate connection #2 cleanup
    CLOSE GLOBAL TestCursor2
    GO
    DEALLOCATE GLOBAL TestCursor2
    GO
    USE [master]
    GO
    
    
    
    -- DDL cleanup
    USE [master]
    GO
    DROP DATABASE [TestDb]
    GO
    
    

    Wednesday, February 2, 2011 3:49 AM

Answers

  • It is doing ROWLOCKs.  What is happening to you is the following:

    When the first connection does the FETCH, you use the hints ROWLOCK, SERIALIZABLE and UPDLOCK.  The SERIALIZABLE is ignored in the case you have given us because it tells SQL Server to take locks that ensure that if the connection later in the current transaction does the same query it will get the same result.  Since in this case you are not in a transaction, there is no such thing as "later in the same transaction".  The UPDLOCK says that when a row is being read by this query, the connection will hold an update lock on that row, and the ROWLOCK says that the update locks taken should be at the row level, not the page or table level.  Row locks would be the default in this case even if you did not specify the ROWLOCK hint.

    The WHERE clause of your query includes ([Id] = @IdFilter OR @IdFilter IS NULL).  Because of the OR @IdFilter IS NULL, this is not a SARGable WHERE clause, so SQL cannot do a seek on the clustered index, so it does a clustered index scan.  (You can see this if you look at the estimated query plan for that query.)  When the query does this scan, since it is going to be doing update locks on rows in the table, it gets an IX (intent exclusive) lock on the table, then it begins with the first page (in this case, the only page) of the clustered index.  Since it will be doing update locks on the rows in this page, it gets an IU (intent update) lock on the page.  It now wants to read the first row of the index, so it gets an U (update) lock on that row, looks at the row, decides it doesn't meet the conditions of the WHERE clause and frees the U lock on the first row.  It now wants to look at the second row, so it gets a U lock on the second row and that does meet the WHERE condition, so it returns it.

    If you now do an sp_lock, you will see that this connection has 4 locks, an S (shared) lock on the database (you get that whenever you connect to a database to ensure the database is not dropped while you are connected to it), an IX lock on the table, an IU lock on the page and a U lock on the row (this lock is a KEY lock on the key to the clustered index, so it will show as a KEY lock).

    So now connection 2 does its query.  It also gets an IX lock on the table, and an IU lock on the page.  This is not a problem, multiple connections can have IX and IU locks on the same resource at the same time.  It also needs to do a clustered index scan, so it begins by getting a U lock on the first row, reads the row, decides it doesn't want it, frees the U lock on the first row.  It now wants to look at the second row.  But you said UPDLOCK, so it needs a U lock on the second row to decide whether or not it wants it.  But connection 1 already has a U lock on that row, so connection 2 must wait, and you get the timeout and the error is returned.

    To the best of my knowledge, all of this is as designed.  If you think it should be different, you can submit a request at connect.microsoft.com.  I would be surprised if they did anything about it, but I've been wrong before.

    So, what can you do about this.  The first thing I would suggest is to change the way you are doing dynamic searches.  This problem would go away if your WHERE clause had ([Id] = @IdFilter instead of ([Id] = @IdFilter OR @IdFilter IS NULL) because then SQL would do an index seek to the fourth row instead of an index scan and so it would not conflict.  See http://www.sommarskog.se/dyn-search.html for ideas on more efficient ways to do dynamic searches.  Also, cursors are often an inefficient way to do things.  Sometimes they are the best or only way, but not usually.  If you can rewrite it so it doesn't use cursors and runs faster, then you will have fewer blocking problems.  Also, generally, if you are using cursors, you want LOCAL STATIC cursors.  I wouldn't use either GLOBAL or DYNAMIC unless I had a reason to desire the features. 

    Tom

    • Marked as answer by dents Wednesday, February 2, 2011 6:39 PM
    Wednesday, February 2, 2011 5:19 PM

All replies

  • Consider applying optimistic concurrency control in multi-user environment:

    http://www.sqlusa.com/articles2005/rowversion/

    Let us know if helpful.

     


    Kalman Toth, Business Intelligence Developer; SQL 2008 GRAND SLAM
    Wednesday, February 2, 2011 8:26 AM
    Moderator
  • Thank you for your reply SQLUSA. I think your proposed workaround makes things bit complicated.

    As I understand, you suggest creating my own locking mechanism - creating a lock entry on record read, and dropping the lock entry after leaving the record. With T-SQL cursors, the "read" events are the FETCH commands, and "leave record" are another FETCH commands, closing the cursor, or closing the session. In the client app it would be needed to call appropriate locking/unlocking routines after these events. During possible updates the live records need to be compared to the lock entries to verify they have not changed meanwhile. Lot of changes in the code using this approach. It also means the client app is now responsible for locking their data which is a step in wrong direction in my view.

    I'm still interested in the original issue i.e. why the ROWLOCK feature doesn't work properly, as shown here. Is it a bug? Is it a misunderstanding of the feature? It is just out there and I'm expecting it to work :) One better link to MSSQL 2008 books - "Table Hints" http://msdn.microsoft.com/en-us/library/ms187373%28v=SQL.100%29.aspx

    Thanks

    Wednesday, February 2, 2011 2:25 PM
  • It is doing ROWLOCKs.  What is happening to you is the following:

    When the first connection does the FETCH, you use the hints ROWLOCK, SERIALIZABLE and UPDLOCK.  The SERIALIZABLE is ignored in the case you have given us because it tells SQL Server to take locks that ensure that if the connection later in the current transaction does the same query it will get the same result.  Since in this case you are not in a transaction, there is no such thing as "later in the same transaction".  The UPDLOCK says that when a row is being read by this query, the connection will hold an update lock on that row, and the ROWLOCK says that the update locks taken should be at the row level, not the page or table level.  Row locks would be the default in this case even if you did not specify the ROWLOCK hint.

    The WHERE clause of your query includes ([Id] = @IdFilter OR @IdFilter IS NULL).  Because of the OR @IdFilter IS NULL, this is not a SARGable WHERE clause, so SQL cannot do a seek on the clustered index, so it does a clustered index scan.  (You can see this if you look at the estimated query plan for that query.)  When the query does this scan, since it is going to be doing update locks on rows in the table, it gets an IX (intent exclusive) lock on the table, then it begins with the first page (in this case, the only page) of the clustered index.  Since it will be doing update locks on the rows in this page, it gets an IU (intent update) lock on the page.  It now wants to read the first row of the index, so it gets an U (update) lock on that row, looks at the row, decides it doesn't meet the conditions of the WHERE clause and frees the U lock on the first row.  It now wants to look at the second row, so it gets a U lock on the second row and that does meet the WHERE condition, so it returns it.

    If you now do an sp_lock, you will see that this connection has 4 locks, an S (shared) lock on the database (you get that whenever you connect to a database to ensure the database is not dropped while you are connected to it), an IX lock on the table, an IU lock on the page and a U lock on the row (this lock is a KEY lock on the key to the clustered index, so it will show as a KEY lock).

    So now connection 2 does its query.  It also gets an IX lock on the table, and an IU lock on the page.  This is not a problem, multiple connections can have IX and IU locks on the same resource at the same time.  It also needs to do a clustered index scan, so it begins by getting a U lock on the first row, reads the row, decides it doesn't want it, frees the U lock on the first row.  It now wants to look at the second row.  But you said UPDLOCK, so it needs a U lock on the second row to decide whether or not it wants it.  But connection 1 already has a U lock on that row, so connection 2 must wait, and you get the timeout and the error is returned.

    To the best of my knowledge, all of this is as designed.  If you think it should be different, you can submit a request at connect.microsoft.com.  I would be surprised if they did anything about it, but I've been wrong before.

    So, what can you do about this.  The first thing I would suggest is to change the way you are doing dynamic searches.  This problem would go away if your WHERE clause had ([Id] = @IdFilter instead of ([Id] = @IdFilter OR @IdFilter IS NULL) because then SQL would do an index seek to the fourth row instead of an index scan and so it would not conflict.  See http://www.sommarskog.se/dyn-search.html for ideas on more efficient ways to do dynamic searches.  Also, cursors are often an inefficient way to do things.  Sometimes they are the best or only way, but not usually.  If you can rewrite it so it doesn't use cursors and runs faster, then you will have fewer blocking problems.  Also, generally, if you are using cursors, you want LOCAL STATIC cursors.  I wouldn't use either GLOBAL or DYNAMIC unless I had a reason to desire the features. 

    Tom

    • Marked as answer by dents Wednesday, February 2, 2011 6:39 PM
    Wednesday, February 2, 2011 5:19 PM
  • All "Hints" = it's entirely up to the query optimizer to honor the hint. If the QO determines a requirement, it may ignore the hint altogether.

    Edit: If you want a stronger assurance, use the isolation levels.

    Adam


    Ctrl+Z
    Wednesday, February 2, 2011 5:47 PM
  • This makes sense, esp. "It also needs to do a clustered index scan, so it begins by getting a U lock on the first row ...". This apparently deals with SQL server internals, but obviously I don't expect a locking conflict on the first or second row, while I'm really trying to lock the fourth row. But the provided background info makes sense. Btw this worked well on MSSQL 2000 but there may be one other aspect to mention - it was using S locks instead of U locks. As of MSSQL 2008 the S locks were no more possible (haven't got that page link right now) so switched to more restrictive U locks, etc.

    From the suggested solutions I like the dynamic searches through EXEC, I have intended this previously but have been working on MSSQL 2000 until recently. The main challenge seems to be the security - 1. SQL injection, in fact just in theory as the users are not really vicious programmers, plus 2. there are various security settings and considerations on the server, sometimes preventing use of advanced features. First I will have to make some estimate how much it would take to implement the dynamic searches. The bug reporting alternative - my wild guess is it shouldn't be diffucult to fix for them, as they are not supposed to be locking everything with U locks except the final record, but it may be just my point of view. I have also considered leaving out the locking completely and letting the users to "manage their records" as an alternative to experiencing locking errors.

    Anyway, many thanks for the insightful reply.

    Wednesday, February 2, 2011 6:38 PM
  • OkThen, I wonder if this is mentioned somewhere in the BOL because so far I've had impression that the hints are honored only except the cases when they are in some invalid combination. E.g. if you specify some bad LOOP join, it is going to be honored by QO even if it slows down the query significantly.

    But actually I wanted to say that I have found a solution for my issue within the existing framework. The key is the UPDLOCK hint, and the query should be re-written as:

     

    DECLARE TestCursor1 CURSOR GLOBAL SCROLL DYNAMIC SCROLL_LOCKS FOR
    SELECT * FROM [TestTable] WITH (ROWLOCK SERIALIZABLE /*UPDLOCK*/)
    WHERE
      ([Id] = @IdFilter OR @IdFilter IS NULL)
    AND ([Name] LIKE @NameFilter OR @NameFilter IS NULL)
    AND ([Status] = @StatusFilter OR @StatusFilter IS NULL)
    
    

     

    When UPDLOCK is there - what I would expect is that it locks the individual rows (matching the where clause) with U locks; what actually happens is that it locks also half of the table with U locks as it scans for the matching records.

    When UPDLOCK is not there - I would expect similar to the above just with S locks; what actually happens is that it locks the matching records with U locks, but fortunately not the scanned indexes, and therefore the cursor locking conflict is avoided. It is not perfect but it's fine with me. Perfect would be if it were using S locks, so users would receive errors only when actually trying to update records locked by someone else. It used to have the S locking functionality on MSSQL 2000, hence "Cursor Concurrency" 2000 vs. "Cursor Concurrency" 2008 . As you could see there is a subtle change in the "matrix", in MSSQL 2008 it is needed to specify SCROLL_LOCKS so there could be U locks, otherwise there are no locks, while in MSSQL 2000 there was a possibility to use HOLDLOCK in combination with READ_ONLY to obtain S locks (btw HOLDLOCK = SERIALIZABLE). So this is how I accepted the U locks in MSSQL 2008, and even made it obvious by specifying the UPDLOCK hint, but as it showed up it works better withouth that hint.

    It is confusing.

    Thanks and have a good day.

     

    Wednesday, February 2, 2011 9:53 PM