none
why the entire table is locked while "WITH (ROWLOCK)" is used in a update statement?

    Question

  • i update one row of a table,using WITH (ROWLOCK), but by executing  "sp_lock" i can see that the entire table is locked. So, before the transaction is committed, other transactions can not update the other rows of the table. why "WITH (ROWLOCK)" doesn't take effect?

     

    thank you for your help

     

     

    Friday, February 22, 2008 4:23 AM

Answers

  • Hi Crystal,

    Query optimizer uses a cost based algorithm when generating execution plans. Since you have only few rows in your table, it selects to do a heap scan on your table (RID  1:1280:0 ). It also ignores your row lock hint. There are situations when table is  small enough that a table scan is the least expensive option to complete the task in hand. That also holds true in your situation.

     

    Therefore, in this case, for both updates, query optimizer elects to do a heap scan. That is why you see RID  1:1280:0  for the update statements.

     

    If you want to resolve reader/writer or writer/writer blocking issues in elegant fashion, I will suggest to upgrade to SQL 2005 and enable row versioning.

     

    I hope it helps.

     

    -Najm

     

     

     

    Sunday, February 24, 2008 2:29 PM

All replies

  • WITH (ROWLOCK) provides a query hint to the optimizer.  If SQL so deems, it can still escalate a rowlock into a page lock or a table lock.  It will normally only do so if you're hitting a very large number of rows though - it's basically trying to save you from consuming extra resources via scores of row locks. 

     

    Does that answer your question?  If you're trying to prevent table locking, you should have a look at your update statement and see if there's any reason for SQL to be escalating the lock.

    Friday, February 22, 2008 4:58 AM
    Moderator
  • thank you.

    i understand that "with (rowlock)" is only a hint. So, is there any way to prevent SQL Server's optimizer from escalating the lock? i want only row lock, not page lock or table lock

    Friday, February 22, 2008 5:29 AM
  • Ehhh...good question.  Try ALLOW_PAGE_LOCKS=OFF in the index creation statement, for whatever index you're accessing.  You can find more info here

     

    Not sure if that'll prevent a table lock or not though.

     

    Friday, February 22, 2008 5:37 AM
    Moderator
  • I have not try your suggestion. Anyway, thank you, TheSQL.

    I think there should be some way to do that, by specifying some hint in the sql statement to tell sql server not to escalate the lock in any condition but rather to just honor my "with (rowlock)". if there is no such way, sql server's throughput will decrease a lot. For example, a table has 1000 rows, and 100 differenct update statements are used to update the table, updating 100 different row respectively. These update statements can not be executed synchronously.

    Friday, February 22, 2008 6:37 AM
  • Crystal,

     

    SQL Server will 'honor' your lock hint at the beginng of the execution but not until the execution is complete. SQL Server if deemed necessary, even though you specified a granular lock, 'will' escalate to a coarser lock depending on the number of locks acquired and the memory available for it. For detailed notes and to look at trace flags to disable the lock escalation look at this article by Sunil Agarwal. 

     

    Friday, February 22, 2008 7:08 AM
    Moderator
  • You may want to post the update statement and table definition here.  We can have a gander and see if there's any way to improve it from a locking perspective.

     

    Friday, February 22, 2008 12:55 PM
    Moderator
  • Hi Crystal,

    It is very classic example of reader/writer or writer/writer contention issue.

    I suppose you are using SQL Server 2005. You can resolve this concurrency issue by using row versioning. In SQL Server 2005, there are two new isolation level that will help you to resolve this issue.

     

    They are READ_COMMITTED_SNAPSHOT and  ALLOW_SNAPSHOT_ISOLATION.

     

    You enable these options with following commands:

     

    ALTER DATABASE AdventureWorks
        SET READ_COMMITTED_SNAPSHOT ON;
    ALTER DATABASE AdventureWorks
        SET ALLOW_SNAPSHOT_ISOLATION ON;
     
    SQL Server 2008 offers better control on the locking.
     
    If you have anyother questions, please don't hesitate to ask.
     
    Best regards,
    Najm
     

     

     

    Friday, February 22, 2008 8:07 PM
  • hi, TheSQL. Hi,everybody, i am quite appreciated for your help.

     

    i use sql server 2000. The following statements are executed in two connections:

    connection A:

    begin transaction
    update table2 with (ROWLOCK)
    set C2 = 10
    where C1 = 1

     

    before the transaction above is committed, connection B try to execute the the following statement:
    connection B:

    begin transaction
    update table2 with (ROWLOCK)
    set C2 = 20
    where C1 =2

     

    though two transactions try to update two different rows, one blocks the other.

     

    i execute "sp_lock" immediately after the connection A's update statement, see the following result:

     

    spid   dbid   ObjId       IndId  Type Resource         Mode     Status
    ------ ------ ----------- ------ ---- ---------------- -------- ------
    51     10     0                0      DB                    S        GRANT
    51     1      85575343     0      TAB                   IS       GRANT
    51     10     933578364   0      RID  1:1280:0         X        GRANT
    51     10     933578364   0      PAG  1:1280           IX       GRANT
    51     10     933578364   0      TAB                   IX       GRANT
    52     10     0                 0      DB                    S        GRANT

    "RID  1:1280:0" is the row that matches "where C1 = 1". Addiction to row lock, you can see that page lock & table lock are granted on object "933578364".  it seems that lock escalating is not the cause of the blocking because only the two statements of my test try to access the "table2" table, there is no other statements that cause other locks.

     

    besides, even there is no way to prevent sql server from granting page lock & table lock, since both the page lock & table lock is IX(intent exclusive) lock, they are compatible with other IX lock on the same page and same table. So, connection B's update statement should have not been blocked.

    Saturday, February 23, 2008 4:31 AM
  • Blocking is ok to have in the system and it doesn't hurt. It hurts only if the query doesn't complete in a reasonable amount of time. Can you tell us if column c2 is unique and if you have any indexes on it and how many rows do you have on that table. Even with millions of records if you have the right index on c2 you may get excellent query response times.

    Saturday, February 23, 2008 4:49 AM
    Moderator
  • Check out traceflags 1211 and 1224 here.  They allow you to disable lock escalation.  Use them with extreme caution, as you can easily overload your server.  If you think that the lock escalation is solely due to the number of rows being updated, and not the memory pressures as a result of the locks, use traceflag 1224 - it disables escalation due to the number of locks, but still lets it kick in as a response to memory pressures.

     

    Does this help?

     

    Saturday, February 23, 2008 6:00 PM
    Moderator
  • do you mean if there is index on c2 and the query completes in a reasonable amount of time, even there are millions of records in table2, the performance hit caused by the blocking  is very low? That is, even there is no blocking, the performance gain i get is very low?

     

    Even i don't consider performance, i still don' t understand:

    1. what's the purpose of granting PAGE LOCK & TABLE LOCK, even "WITH (ROWLOCK)" is present?

    2.The granted PAGE LOCK & TABLE LOCK are both IX lock. So even the connection B's update statement has not been blocked, no lock compatibility problem occurs because IX PAGE LOCK is compatible with other IX PAGE LOCKs and IX TABLE LOCK is compatible with other IX TABLE LOCKs. So, why connection B's update statement is blocked?

     

     

    Sunday, February 24, 2008 3:38 AM
  • The SQL, thank you for your suggestion. But i think that the blocking cause is not lock escalating. In my test, before the connection A's update statement's executing, the table2 is accessed by no other process.

    Sunday, February 24, 2008 5:27 AM
  • If I understand your issue correctly, Connection A is locking out Connection B, right?  Well, Connection A's locks are being escalated to a table lock, which is blocking Connection B.  Unless I'm misunderstanding your issue.

    Sunday, February 24, 2008 6:13 AM
    Moderator
  • Hi Crystal,

    Query optimizer uses a cost based algorithm when generating execution plans. Since you have only few rows in your table, it selects to do a heap scan on your table (RID  1:1280:0 ). It also ignores your row lock hint. There are situations when table is  small enough that a table scan is the least expensive option to complete the task in hand. That also holds true in your situation.

     

    Therefore, in this case, for both updates, query optimizer elects to do a heap scan. That is why you see RID  1:1280:0  for the update statements.

     

    If you want to resolve reader/writer or writer/writer blocking issues in elegant fashion, I will suggest to upgrade to SQL 2005 and enable row versioning.

     

    I hope it helps.

     

    -Najm

     

     

     

    Sunday, February 24, 2008 2:29 PM
  • the blocking is caused by the fact that the test table2 has no index created on C1. After creating the index, the blocking does not occur. But i don't understand why.

    Tuesday, February 26, 2008 12:47 PM
  • I am guessing here a little but an Update is an implicit transactions. Therefore, without an index or statistics the update will scan the table. As it applies changes it must ensure that following the ACID test all the changes are applied or none. Therefore, it could hit many records or only one but the optimiser does not know which until it scans the whole table. It cannot predict the number or spread of pages impacted. Therefore, it must make a judgement that a higher level of lock is appropriate (most likely a table lock). Whereas, with an index the optimiser can quickly pinpoint the pages or rows impacted and take the appropriate locks to ensure that the transaction completes or does a rollback.

     

    Even if the second update can pinpoint specific records the first is likely to hold a table lock.

     

    This has to occur because if both updates hit similar pages then one has to carryout a rollback then what should the resulting pages look like. Only by committing all the pages impacted by an update statement can the implicit transaction be completed. the second update can only then update the previously revised page and not the version prior to the first update. However, by applying the index the update quickly identifies those pages impacted and so long as there is no overlap of pages between the two update statements then there should be a limited chance of locking (assuming the optimiser does not opt for a higher level of locking).

    Tuesday, February 26, 2008 2:26 PM