Using Indexes to Reduce Blocking in Concurrent Transactions

  • nilav.ghosh

    SSC Rookie

    Points: 39

    Comments posted to this topic are about the item Using Indexes to Reduce Blocking in Concurrent Transactions

  • Bob Fazio

    SSChampion

    Points: 10734

    A good article, that with a thorough proof read would have resulted in a very good article.

    11757457 ObjID is actually the ID for the Bibby.Profit_Act table. 1:153:1 is the first row in the Bibby.Profit_Act table. The interesting thing to note is the X lock has been granted on this first row, but the transaction goes into a waiting state when it tries to lock the second row as well which is 1:153:1 (Note the Status column showing ‘WAIT’ status) for the update lock being requested.

    11757457 - Should be 117575457

    1:153:1 is the first row - should be 1:153:0 is the first row

    It was easy for me to catch these mistakes (But I knew this topic already).

    Also an explanation of the lock resource values and how to understand how you know that

    1:153:0 is the first row would have been appreciated.

    DatabaseID:FileID:PageID:Slot(row) or FileID:PageID:Slot(row)

  • dchintalapudi

    Grasshopper

    Points: 24

    Excellent Article! Concepts were explained very well.

  • Chris Harshman

    SSC-Forever

    Points: 41969

    It was a fairly good article, and brings an interesting approach to the table, but I think the concept is a little outdated. This would have been more relevant in SQL Server 2000 where you couldn't avoid the dirty read vs. shared locks question. In SQL Server 2005 and 2008 you can use multi-version concurrency via the READ_COMMITTED_SNAPSHOT database option in an OLTP system to avoid the problems of both dirty reads and shared locks.

  • Thomas LeBlanc

    SSCertifiable

    Points: 7929

    Great article with examples and explanations...

    I follow up article would be great, please advance the level.

    God Bless,

    ThomasLL

    Thomas LeBlanc, MVP Data Platform Consultant

  • Noel Jiwanmall

    Grasshopper

    Points: 21

    Hello All,

    I am new to SQL Server Databases so please forgive if my question doesn't make sense. So here I go, how can one ever know that only Mutually Exclusive rows will be updated in a table at the same time so not to apply the Update Lock to at least prevent the deadlock situations from occuring? What I mean to say is that if I were to prioritize the interactions of different queries with a table, I would first want to make sure that no deadlocks are ever created which would lead me to use the Update lock, but then I would also like to acheive better performance so not to use the update lock and simply applying the index to the table. In other words, is it possible to use both an Index as well as the Update Lock to achieve improved performance as well as avoid deadlocks?

    If you were able to understand what I was getting at, please help clarify.

    Regards

  • s.c.simmons

    SSC Enthusiast

    Points: 172

    Question: Does the index have to be clustered for this to work?

  • keliu

    SSC Rookie

    Points: 37

    Very good article with clear instructions.

    Hope to read more papers from you again.

  • maechismo_8514

    SSCoach

    Points: 17510

    s.c.simmons (7/7/2009)


    Question: Does the index have to be clustered for this to work?

    Doesn't have to be. Basically, the trick is to show queries different paths.

  • rob.lobbe-964963

    Old Hand

    Points: 307

    Of course this all falls in a screaming heap when larger 'row selections' are used, and 'Lock escalation' kicks in.

    A selection of 5000+ rows - will escalate to a single Table-lock (rather than 5000+ row locks) and you're back to where you started.


    But in essence, any indexing will reduce blocking (not eliminate it) as the database touches fewer elements of the table -- unless you have an index scan going on...

  • leigh.shayler

    SSC Rookie

    Points: 28

    Thanks, Provides a good (and brief) overview of resource locking and lock investigation as well.

  • guruprasat85

    SSC Enthusiast

    Points: 136

    I actually tried to run the query without adding an index and it still worked for me. There were no waiting locks. Any reason why?

  • william-812661

    Grasshopper

    Points: 13

    When there is no index, why database engine need to place RID lock on both row 1 & 2?

  • leigh.shayler

    SSC Rookie

    Points: 28

    Without an index, the database will have to perform a table scan meaning it will check each row in the table so it will place a lock on each of the rows to ensure none of them change while it does the processing.

    william (7/8/2009)


    When there is no index, why database engine need to place RID lock on both row 1 & 2?

  • guruprasat85

    SSC Enthusiast

    Points: 136

    Yes It actually worked for me now. My table has no index. First I ran the below statement in one query window

    BEGIN TRAN

    update dbo.Profit_Act1 with(rowlock)

    --rowlock is the default lock level

    set France = 24000

    where ID=2

    Then I opened another query window and ran the below statement

    BEGIN TRAN

    update dbo.Profit_Act1 with(rowlock)

    --rowlock is the default lock level

    set France = 22000

    where ID=1

    The first one ran but the second didnt since I had not commited the tran on my query window

    Then i commited them and added index to the table. Then I recreated the same scenario but at this time the query in my second window ran without any problems

    Also the execution plan clearly showed the table scan missing after adding the index

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply