SQL Server Performance Forum – Threads Archive

deadlock conncurent connections

Hi ! I am new the sql, I know many people have asked the questions about dealock and I have read quite a few questions on this forum but I have few questions which I am not yet clear about . we are developing an application where multiple application server connect the database and update and select info but all this info is usualy taken from unique row not much of queries are made which use common rows but still we are facing lot of deadlock althout the storeprocedures called select or update unique different rows from the tables and the storeprocedures have has proper begin transaction and commit transaction after each update and we have even tried rowlocks and even tried index options to prevent page/table locking but still we have same problem of deadlock . Is it possible that dues to multiple different conncurent connections to DB which are executing sp , migt be the cause of rowlock not working many be because of different connections it could be possible that the different connections dont understand that rowlock has been put because different connections might be creating different sessions . Are there any know issues to consider to prevent dealocks where database table’s rows are updated with sp called from different conncurent connections from different applications . Could some one please help regarding this issue , I would be really greatful and appreciate all the help .

Hi, first of all please run profiler to monitor the event which cause a deadloack , stmt starting and ending time ,lock realse duration ,sp having begin and commit tran or not ? and you may refer following articles : <a href=’http://www.sql-server-performance.com/asp_sql_server.asp’ target=’_blank’ title=’http://www.sql-server-performance.com/asp_sql_server.asp'<a target="_blank" href=http://www.sql-server-performance.com/asp_sql_server.asp>http://www.sql-server-performance.com/asp_sql_server.asp</a></a> <a href=’http://www.sql-server-performance.com/visual_basic_performance.asp’ target=’_blank’ title=’http://www.sql-server-performance.com/visual_basic_performance.asp'<a target="_blank" href=http://www.sql-server-performance.com/visual_basic_performance.asp>http://www.sql-server-performance.com/visual_basic_performance.asp</a></a> <a href=’http://www.sql-server-performance.com/stored_procedures.asp’ target=’_blank’ title=’http://www.sql-server-performance.com/stored_procedures.asp'<a target="_blank" href=http://www.sql-server-performance.com/stored_procedures.asp>http://www.sql-server-performance.com/stored_procedures.asp</a></a> <a href=’http://vyaskn.tripod.com/watch_your_timeout.htm’ target=’_blank’ title=’http://vyaskn.tripod.com/watch_your_timeout.htm'<a target="_blank" href=http://vyaskn.tripod.com/watch_your_timeout.htm>http://vyaskn.tripod.com/watch_your_timeout.htm</a></a> <a href=’http://www.sql-server-performance.com/deadlocks.asp’ target=’_blank’ title=’http://www.sql-server-performance.com/deadlocks.asp'<a target="_blank" href=http://www.sql-server-performance.com/deadlocks.asp>http://www.sql-server-performance.com/deadlocks.asp</a> </a> <a href=’http://www.sql-server-performance.com/art_deadlock_sql_2005.asp’ target=’_blank’ title=’http://www.sql-server-performance.com/art_deadlock_sql_2005.asp'<a target="_blank" href=http://www.sql-server-performance.com/art_deadlock_sql_2005.asp>http://www.sql-server-performance.com/art_deadlock_sql_2005.asp</a> </a> <a href=’http://www.sql-server-performance.com/lock_contention_tamed_article.asp’ target=’_blank’ title=’http://www.sql-server-performance.com/lock_contention_tamed_article.asp'<a target="_blank" href=http://www.sql-server-performance.com/lock_contention_tamed_article.asp>http://www.sql-server-performance.com/lock_contention_tamed_article.asp</a></a> <a href=’http://www.sql-server-performance.com/sf_block_prevention.asp’ target=’_blank’ title=’http://www.sql-server-performance.com/sf_block_prevention.asp'<a target="_blank" href=http://www.sql-server-performance.com/sf_block_prevention.asp>http://www.sql-server-performance.com/sf_block_prevention.asp</a></a> <a href=’http://www.sql-server-performance.com/blocking.asp’ target=’_blank’ title=’http://www.sql-server-performance.com/blocking.asp'<a target="_blank" href=http://www.sql-server-performance.com/blocking.asp>http://www.sql-server-performance.com/blocking.asp</a></a> <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami

hi actually i am just having a single update query which always modify a single unique row as per table architecture but when i run this query from tow different applications deadlock occurs. So how could i avoid update lock.

http://www.sql-server-performance.com/at_sql_locking.asp for a reference.
Update locks exist to prevent deadlocks on resources that are being modified. Typically the first process acquires a shared lock on the resource being modified. A second process also may acquire a shared lock on the same resource. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Tried all the posisble things , would like to know would boundconnections help I read some where that boundconnection does not allow multiple connections at the same time and even when I tried a new connection using boundconnection method it just gave me a message already transaction is using . problem is there are many different conncurent connections from the application and same application runs on multiple machines which makes it more multiple connection ( conncurent ) . so would like to know would some thing like bound help . Also would like to know that is it right that "set isolation level " work on single connection and multiple connections would be able to understand if any one connections has already set isolation level ? Also is it possible that there are some specific indexes needed on each tables to pervent table/page lock if multiple rows are beeing read or update by multiple connections ? tried rowlock , bound I am not clear if conncurent connections work with it and it it does allow conncurent connections to establish connection or not ?

Can you post the update statement you’re having problems with and a general idea of where the indexes lie on the related table(s)? Chances are that the WHERE clause is taking out a shared lock and it is then converted to an exclusive lock and that the update part of the statement is trying to update a column that has an index on it (which results in the need for an index key lock). Look up conversion deadlocks. Either way, without seeing the update statement and knowing much about the indexes on it there isn’t much more I can say. You could force the use of an update lock using WITH (UPDLOCK).

quote:Originally posted by DBVictim Tried all the posisble things , would like to know would boundconnections help I read some where that boundconnection does not allow multiple connections at the same time and even when I tried a new connection using boundconnection method it just gave me a message already transaction is using . problem is there are many different conncurent connections from the application and same application runs on multiple machines which makes it more multiple connection ( conncurent ) . so would like to know would some thing like bound help . Also would like to know that is it right that "set isolation level " work on single connection and multiple connections would be able to understand if any one connections has already set isolation level ? Also is it possible that there are some specific indexes needed on each tables to pervent table/page lock if multiple rows are beeing read or update by multiple connections ? tried rowlock , bound I am not clear if conncurent connections work with it and it it does allow conncurent connections to establish connection or not ?

Karl Grambow www.sqldbcontrol.com

Hi , We have an update statement in one of the sps update revision set has_commentType = @has_commentType from revision ( updlock ) where revision_id = @doc_revision_id We were getting deadlocks without the updlock hint before. We presumed that it was happening because of the shared lock getting converted into exclusive locks. Hence we added the updlock hint. Still the deadlock happens but with a different trace now (and may be different reason). Deadlock encountered …. Printing deadlock information 2006-06-27 15:11:05.10 spid4 2006-06-27 15:11:05.10 spid4 Wait-for graph 2006-06-27 15:11:05.10 spid4 2006-06-27 15:11:05.10 spid4 Node:1 2006-06-27 15:11:05.10 spid4 KEY: 7:53575229:23 (b6017c666c8c) CleanCnt:1 Mode: U Flags: 0x0 2006-06-27 15:11:05.10 spid4 Grant List 0:: 2006-06-27 15:11:05.10 spid4 Owner:0x462f44e0 Mode: U Flg:0x0 Ref:1 Life:02000000 SPID:195 ECID:0 2006-06-27 15:11:05.10 spid4 SPID: 195 ECID: 0 Statement Type: UPDATE Line #: 126 2006-06-27 15:11:05.10 spid4 Input Buf: RPC Event: storeComment;1 2006-06-27 15:11:05.10 spid4 Requested By: 2006-06-27 15:11:05.10 spid4 ResType:LockOwner Stype:’OR’ Mode: U SPID:185 ECID:0 Ec0x4016151<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Value:0x29b40ce0 Cost0/601<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> 2006-06-27 15:11:05.10 spid4 2006-06-27 15:11:05.10 spid4 Node:2 2006-06-27 15:11:05.10 spid4 KEY: 7:53575229:23 (90014995cf5d) CleanCnt:1 Mode: U Flags: 0x0 2006-06-27 15:11:05.10 spid4 Grant List 3:: 2006-06-27 15:11:05.10 spid4 Owner:0x2f953000 Mode: U Flg:0x0 Ref:1 Life:02000000 SPID:185 ECID:0 2006-06-27 15:11:05.10 spid4 SPID: 185 ECID: 0 Statement Type: UPDATE Line #: 126 2006-06-27 15:11:05.10 spid4 Input Buf: RPC Event: storeComment;1 2006-06-27 15:11:05.10 spid4 Requested By: 2006-06-27 15:11:05.10 spid4 ResType:LockOwner Stype:’OR’ Mode: U SPID:195 ECID:0 Ec0x4C05751<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Value:0x4833f060 Cost0/8B6<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> 2006-06-27 15:11:05.10 spid4 Victim Resource Owner: 2006-06-27 15:11:05.10 spid4 ResType:LockOwner Stype:’OR’ Mode: U SPID:185 ECID:0 Ec0x4016151<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Value:0x29b40ce0 Cost0/601<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> 2006-06-27 15:12:02.63 spid4 Deadlock encountered …. Printing deadlock information I am not able to understand why these deadlock is happening. First thing I do not understand is that how 2 spids are having U lock on the same resource. My understanding is that U locks are not shared on the same resource. That was the whole purpose of providing the updlock hint. Thanks in Advance, Devendra

]]>