Locks in SQL Server 1

[theOneAndOnlyOne]

Hello,

I'm busy investigating locks for my company and I've got probably 100 of questions, but I'll start in the begining .
- I've got at a random interval time-outs. The @@LOCK_TIMEOUT is equal to -1. How is this possible and how can I investigate these statements (in other words, how can I track down the statement that caused the time out?)
- Deadlocks... How can I see which procedures where the victim of a deadlock?

I hope someone can help.

Regards,

_the_one__

 

[harmmeijer]

SET LOCK_TIMEOUT:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_1yr8.asp

At the beginning of a connection, this setting has a value of -1. After it is changed, the new setting stays in effect for the remainder of the connection.

The setting of SET LOCK_TIMEOUT is set at execute or run time and not at parse time.

The READPAST locking hint provides an alternative to this SET option.



I think a deadlock raises an error number 1205 you can set an alert on that (manigement -> sql server agent -> alerts)
This is the error text of a deadlock:
Transaction (Process ID %d) was deadlocked on {%Z} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I gues Process ID %d is the victim.

 

[theOneAndOnlyOne]

Hey Harmmeijer,

thnx for the quick reply, but still I've got a problem (yeah, I now, more than one problem):
I didn't change anything for the LOCK_TIMEOUT setting, it's still default, so every connection should have infinite for time-out... But still these time out's happen?!?

The other sollution you gave is very helpfull for being notified when a deadlock occurs, but it will only return the SPID... If the deadlock has occured, and you run a DBCC Inputbuffer for that spid, the statement that caused the deadlock is already gone. :-(

Any other suggestions?

_the_one__

 

[tlbroadbent]

Setting the lock timeoout value doesn't solve the problem. It merely tells SQL Server to kill the blocked connection while the blocking connection continues to run. I've never had occasion to change the timeout value. The correct approach is to find what causes the locks and fix those problems.

Use sp_lock and sp_who2 to monitor locking and see which spids are blocking others. Use SQL Profiler to trace statements and help find those causing deadlocks.

Check the following resources for details about monitoring locks and deadlocks as well as techniques to avoid these conditions.

Tips for Reducing SQL Server Deadlocks

http://www.sql-server-performance.com/deadlocks.asp

Locking in SQL Server 7.0

http://www.swynk.com/friends/achigrik/SQL70Locks.asp

Transactions, Locking, and Concurrency in SQL Server

http://www.dbazine.com/sharma1.html

Tips for Reducing SQL Server Locks

http://www.sql-server-performance.com/reducing_locks.asp

SQL Server Lock Contention Tamed

http://www.sql-server-performance.com/lock_contention_tamed_article.asp

Avoid Database Deadlocks with Planning

http://gethelp.devx.com/techtips/thesqlpro/10min/10min0702/10min0702.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.