Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Using Indexes to Reduce Blocking in Concurrent Transactions Expand / Collapse
Author
Message
Posted Sunday, June 21, 2009 2:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:56 PM
Points: 27, Visits: 90
Comments posted to this topic are about the item Using Indexes to Reduce Blocking in Concurrent Transactions
Post #739002
Posted Tuesday, July 7, 2009 6:20 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, January 6, 2012 2:39 PM
Points: 954, Visits: 683
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)


Post #748458
Posted Tuesday, July 7, 2009 6:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 6:46 AM
Points: 1, Visits: 43
Excellent Article! Concepts were explained very well.
Post #748493
Posted Tuesday, July 7, 2009 7:30 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:30 AM
Points: 1,950, Visits: 2,122
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.
Post #748532
Posted Tuesday, July 7, 2009 10:00 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, December 5, 2014 2:57 PM
Points: 3,175, Visits: 795
Great article with examples and explanations...

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

God Bless,
ThomasLL


Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
Post #748692
Posted Tuesday, July 7, 2009 10:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 8, 2010 1:55 PM
Points: 1, Visits: 17
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
Post #748715
Posted Tuesday, July 7, 2009 1:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 14, 2014 11:39 AM
Points: 18, Visits: 113
Question: Does the index have to be clustered for this to work?
Post #748836
Posted Tuesday, July 7, 2009 2:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 19, 2013 7:40 AM
Points: 7, Visits: 276
Very good article with clear instructions.

Hope to read more papers from you again.



Post #748864
Posted Tuesday, July 7, 2009 3:31 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 24, 2012 8:11 AM
Points: 1,097, Visits: 2,157
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.



Post #748895
Posted Tuesday, July 7, 2009 3:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:17 PM
Points: 27, Visits: 207
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...
Post #748898
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse