I've some funny deadlock triggered with a stupid simple SQL UPDATE query, on the flat plain table, under default "READ COMMITED" transaction.

UPDATE table SET column=@P1 WHERE PK=@P2 While PK varchar(11), includes a clustered index onto it. no trigger or table relation..etc up for grabs.

Used to do some check and discover the deadlock happen on "PAGE" level, not at ROW/record level. Then, I've found that for every update query, it will take 100(and much more) PAGE locks. (It doesn't seem sensible in my experience because I'm upgrading one row at the same time)

Can there be in whatever way to avoid deadlock being happen? Or, just how can reduce the amount of locks it requires for just one single row update without needing cursor?

--

Interesting suggestion.

I'd attempted to rebuild the index a couple of occasions, with everywhere fill factor. I'd attempted to create processes update different position/slice. But nothing got enhanced or worst.

--

I attempted the SQL Server Profiler. I captued some "Lock:Deadlock Chain" and "Lock:Deadlock", but no "Deadlock Graph" was taken. Both side do the easy 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