Using Indexes to Reduce Blocking in Concurrent Transactions

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Nilav Baran Ghosh » Using Indexes to Reduce Blocking in...

21 posts, Page 1 of 3

1 2 3 »»»

Using Indexes to Reduce Blocking in Concurrent Transactions

Rate Topic

Display Mode

Topic Options

Author

Message

nilav.ghosh

Posted Sunday, June 21, 2009 2:59 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 12, 2012 4:35 AM
Points: 27, Visits: 88

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

Post #739002

Bob Fazio

Posted Tuesday, July 07, 2009 6:20 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, January 06, 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

dchintalapudi

Posted Tuesday, July 07, 2009 6:52 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 19, 2013 12:27 PM
Points: 1, Visits: 37

Excellent Article! Concepts were explained very well.

Post #748493

Chris Harshman

Posted Tuesday, July 07, 2009 7:30 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 12:03 PM
Points: 1,564, Visits: 1,718

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

Thomas LeBlanc

Posted Tuesday, July 07, 2009 10:00 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 20, 2013 3:33 PM
Points: 2,705, Visits: 717

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

Noel Jiwanmall

Posted Tuesday, July 07, 2009 10:34 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 08, 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

s.c.simmons

Posted Tuesday, July 07, 2009 1:47 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 8:22 PM
Points: 18, Visits: 107

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

Post #748836

keliu

Posted Tuesday, July 07, 2009 2:37 PM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, August 04, 2012 7:20 AM
Points: 7, Visits: 266

Very good article with clear instructions.

Hope to read more papers from you again.

Post #748864

maechismo_8514

Posted Tuesday, July 07, 2009 3:31 PM

Ten 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

rob.lobbe-964963

Posted Tuesday, July 07, 2009 3:35 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:38 PM
Points: 23, Visits: 177

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 »

21 posts, Page 1 of 3

1 2 3 »»»

Permissions