Reduce deadlock on PAGE level on update query on MS SQL

I have some funny deadlock caused by a stupid simple SQL UPDATE query, on a flat plain table, under default "READ COMMITED" transaction.

UPDATE table SET column=@P1 WHERE PK=@P2; While PK varchar(11), has a clustered index on it. no trigger or table relation..etc on the table.

I did some check and find that the deadlock happen on "PAGE" level, not at ROW/record level. Then, I find that for each update query, it does take 100(and more) PAGE locks. (It does not make sense to me because I am updating one row at once)

Is there any way to prevent deadlock being happen? Or, how can reduce the number of locks it takes for one single row update without using cursor?

--

Thanks for your suggestion.

I had tried to rebuild the index a few times, with high and low fill factor. I had tried to make processes update different position/slice. But nothing got improved or worst.

--

I tried the SQL Server Profiler. I captued some "Lock:Deadlock Chain" and "Lock:Deadlock", but no "Deadlock Graph" was captured. Both side are doing the simple update query in read commited, auto-commit mode.

Lock:Deadlock Chain 17887475	1		0X01	4	myserver		2008-11-28 10:16:46.210	Parallel query worker thread was involved in a deadlock																	0			971497	102 - Resource type Exchange				    Lock:Deadlock Chain	17887476	1		0X01	4	myserver		2008-11-28 10:16:46.210	Deadlock Chain SPID = 209 1:438102                                                                                                                                                                                                                                                       	265006271		0	0X56AF060001000000000000001B0006		27				0 - LOCK	4 - U			0	72057594040352768	1 - TRANSACTION	0	6 - PAGE	mydatabase	971497	101 - Resource type Lock				
Lock:Deadlock Chain 17887477	1		0X01	4	myserver		2008-11-28 10:16:46.210	Parallel query worker thread was involved in a deadlock																	0			971497	102 - Resource type Exchange				    Lock:Deadlock Chain	17887478	1		0X01	4	myserver		2008-11-28 10:16:46.210	Deadlock Chain SPID = 54 1:426206                                                                                                                                                                                                                                                       	265006240		0	0XDE80060001000000000000001B0006		27				0 - LOCK	4 - U			0	72057594040352768	1 - TRANSACTION	0	6 - PAGE	mydatabase	971497	101 - Resource type Lock				
Lock:Deadlock Chain 17887479	1		0X01	4	myserver		2008-11-28 10:16:46.210	Deadlock Chain SPID = 209 1:426206                                                                                                                                                                                                                                                       	265006271		0	0XDE80060001000000000000001B0006		27				0 - LOCK	4 - U			0	72057594040352768	1 - TRANSACTION	0	6 - PAGE	mydatabase	971497	101 - Resource type Lock				
Lock:Deadlock Chain 17887480	1		0X01	4	myserver		2008-11-28 10:16:46.210	Parallel query worker thread was involved in a deadlock																	0			971497	102 - Resource type Exchange				    Lock:Deadlock Chain	17887481	1		0X01	4	myserver		2008-11-28 10:16:46.210	Deadlock Chain SPID = 54 1:426066                                                                                                                                                                                                                                                       	265006240		0	0X5280060001000000000000001B0006		27				0 - LOCK	4 - U			0	72057594040352768	1 - TRANSACTION	0	6 - PAGE	mydatabase	971497	101 - Resource type Lock				
Lock:Deadlock Chain 17887482	1		0X01	4	myserver		2008-11-28 10:16:46.210	Deadlock Chain SPID = 209 1:426066                                                                                                                                                                                                                                                       	265006271		0	0X5280060001000000000000001B0006		27				0 - LOCK	4 - U			0	72057594040352768	1 - TRANSACTION	0	6 - PAGE	mydatabase	971497	101 - Resource type Lock				
Lock:Deadlock Chain 17887483	1		0X01	4	myserver		2008-11-28 10:16:46.210	Parallel query worker thread was involved in a deadlock																	0			971497	102 - Resource type Exchange				    Lock:Deadlock Chain	17887484	1		0X01	4	myserver		2008-11-28 10:16:46.210	Deadlock Chain SPID = 209 1:425614                                                                                                                                                                                                                                                       	265006271		0	0X8E7E060001000000000000001B0006		27				0 - LOCK	4 - U			0	72057594040352768	1 - TRANSACTION	0	6 - PAGE	mydatabase	971497	101 - Resource type Lock				
Lock:Deadlock Chain 17887485	1		0X01	4	myserver		2008-11-28 10:16:46.210	Parallel query worker thread was involved in a deadlock																	0			971497	102 - Resource type Exchange				    Lock:Deadlock Chain	17887486	1		0X01	4	myserver		2008-11-28 10:16:46.210	Deadlock Chain SPID = 209 1:426687                                                                                                                                                                                                                                                       	265006271		0	0XBF82060001000000000000001B0006		27				0 - LOCK	4 - U			0	72057594040352768	1 - TRANSACTION	0	6 - PAGE	mydatabase	971497	101 - Resource type Lock				
Lock:Deadlock Chain 17887487	1		0X01	4	myserver		2008-11-28 10:16:46.210	Parallel query worker thread was involved in a deadlock																	0			971497	102 - Resource type Exchange				
Lock:Deadlock Chain 17887488	1		0X01	4	myserver		2008-11-28 10:16:46.210	Deadlock Chain SPID = 209 1:425392                                                                                                                                                                                                                                                       	265006271		0	0XB07D060001000000000000001B0006		27				0 - LOCK	4 - U			0	72057594040352768	1 - TRANSACTION	0	6 - PAGE	mydatabase	971497	101 - Resource type Lock				
Lock:Deadlock Chain 17887489	1		0X01	4	myserver		2008-11-28 10:16:46.210	Parallel query worker thread was involved in a deadlock																	0			971497	102 - Resource type Exchange				
Lock:Deadlock Chain 17887491	1		0X01	4	myserver		2008-11-28 10:16:46.210	Parallel query worker thread was involved in a deadlock																	0			971497	102 - Resource type Exchange				
Lock:Deadlock Chain 17887493	1		0X01	4	myserver		2008-11-28 10:16:46.210	Parallel query worker thread was involved in a deadlock																	0			971497	102 - Resource type Exchange				
Lock:Deadlock Chain 17887494	1		0X01	4	myserver		2008-11-28 10:16:46.210	Deadlock Chain SPID = 209 1:435792                                                                                                                                                                                                                                                       	265006271		0	0X50A6060001000000000000001B0006		27				0 - LOCK	4 - U			0	72057594040352768	1 - TRANSACTION	0	6 - PAGE	mydatabase	971497	101 - Resource type Lock				
Lock:Deadlock Chain 17887495	1		0X01	4	myserver		2008-11-28 10:16:46.210	Parallel query worker thread was involved in a deadlock																	0			971497	102 - Resource type Exchange				
Lock:Deadlock Chain 17887496	1		0X01	4	myserver		2008-11-28 10:16:46.210	Deadlock Chain SPID = 209 1:438206                                                                                                                                                                                                                                                       	265006271		0	0XBEAF060001000000000000001B0006		27				0 - LOCK	4 - U			0	72057594040352768	1 - TRANSACTION	0	6 - PAGE	mydatabase	971497	101 - Resource type Lock				
Lock:Deadlock   17887497		myuser	0XCD85FBB269700B4AA2F4E8579D118999	209	myserver	myuser	2008-11-28 10:16:45.930	1:426206	265006271	myapps	0	0XDE80060001000000000000001B0006	123	27	281	2008-11-28 10:16:46.210	myclient	0 - LOCK	4 - U			0	72057594040352768	1 - TRANSACTION	0	6 - PAGE	mydatabase	971498

Answers


You have 2 options to reduce the lock escalation:

1) add the WITH (ROWLOCK) hint to ask sql server to take finer granularity locks (your mileage may vary:

UPDATE table WITH (ROWLOCK) SET column=@P1 WHERE PK=@P2; While PK varchar(11), has a clustered index on it. no tigger or table relation..etc on the table.

2) update rows in a random order, which reduce the likelihood of row locks being escalated to page locks.

Also, ensuring that the indexes on that table are up to date can often reduce locking. As can leaving a Fill factor (90 is good) if you are going to be doing lots of inserts.


Need Your Help

different behaviours for UIImage Orientation in iOS6 and iOS5

iphone objective-c ios uiimageorientation

I have created an iphone app which is only in portrait orientation,even though i have made one view supporting landscape orientation also while rotating the device

Extract MySQL Blob and add to PHP array?

php mysql arrays string blob

I need to extract a list of keywords from a MySQL database. Each keyword is separated by a comma. I need to read it from the blob, then add it to the array. How would this be done?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.