Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim Rerun the transaction

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim Rerun the transaction


   You had written update or delete  statements without SQL hints like nolock, updlock, readpast etc.
This is very bad practice when you are dealing with multiple udates on rows or tables without locks.
Below scenario will explain how to use those hints to avoid deadlocks aswell as concurrency issues.

e.g.

UPDATE top 10 xyzTable with (updlock)

set x = 1

In this case if second user comes in to update xyzTable, he has to wait for first user operation to finish.

To let second user do update on next top 10 rows which are not locked by first user, you can

modify above query using readpast hint.

ReadPast hint will return row sets which are not locked by any transaction in that contxt.

UPDATE top 10 xyzTable with (updlock,readpast)
set x = 1


ReadPast and NoLock are two different hints
Readpast Only return rows which are not locked, while nolock returns
commited as well as non commited rows by other transaction this is also called as dirty reads.

SP_LOCK will written locks details.

You can avoid concurrency issues with your transaction using those lock hints.

For more information about locks in MS SQL you can visit

http://msdn.microsoft.com/en-us/library/aa213026(SQL.80).aspx


 

Tags:

Android screen navigation example using Activity and IntentGridview select emailAndroid WebView Examplegridview add new rowAndroid EditText TutorialASp.net 2.0 step by step Membership Provider Android Button ClickOLE DB provider SQLNCLI for linked server returned message The partner transaction manager has disabled its support for remotenetwork transactionsAndroid Button onClickListener Exampleasp.net Gridview checkboxJQuery Slide

Author

My name is Satalaj, but people call me Sat. Here is my homepage: . I live in Pune, PN and work as a Software Engineer. I'm former MVP in ASP.net year 2010.
Disclaimer: Views or opinion expressed here are my personal research and it has nothing to do with my employer. You are free to use the code, ideas/hints in your projects. However, you should not copy and paste my original content to other web sites. Feel free to copy or extend the code.
If you want to fight with me, this website is not for you.
 

I'm Satalaj.