May 17, 2012 at 2:02 am
Is there anyway to create some type of procedure whereby SQL Server will e-mail me when a file lock occurs?
May 17, 2012 at 2:19 am
can you clarify what you mean by file lock please?
MVDBA
May 17, 2012 at 2:26 am
michael vessey (5/17/2012)
can you clarify what you mean by file lock please?
Hi Mike,
Thanks for such a fast reply. Our users get a DEADLOCK when multiple people are accessing the same supplier in SQL and at the moment they call me and then I log into Management Studio run an SP called "SP_WHO2" and it shows me who has locked the other user so I can either call the other user and ask them to get out of that supplier which clears the lock or issue a KILL "SPID Number" which clears it.
Hope this is enough info..
May 17, 2012 at 2:34 am
you need to do the following
1) ensure database mail is configured
2) go into sql server agent and set up an operator with your email address
3)in sql agent set up an alert with the error number you receive in the error log when there is a deadlock
4)set the response to the alert as Notify Operator
what i also do is set up a scheduled job to run every 5 minutes and run an sp_who2 - then store any rows that are locking and blocking in a table.
when the job runs the next time, if the same spids are locked/blocked then make a call to sp_senddbmail to notify me that spids have been potentially blocking for 5 minutes
MVDBA
May 17, 2012 at 2:36 am
also have a look at the performance condition alerts - such as lock wait time, deadlocks per second etc
MVDBA
May 17, 2012 at 2:57 am
David, your getting confused over blocking & locking and deadlocks, what you are seeing is typical behavour within SQL and is blocking as two sessions are waiting for the same piece of information.
This link referes to Visual Studio 6, but is in relation to locking & deadlocks http://msdn.microsoft.com/en-us/library/aa266504%28v=vs.60%29.aspx.
The first thing I would look at is, is the application using transactions and not releasing the lock it holds on the supplier for the scope of the transaction, can it be changed to not do a transaction around the supplier, there are also a couple of lock aviodence things at the bottom of that link.
A deadlock happens when two sessions want each others already locked information.
A typical example would be
Session 1 does this
BEGIN TRANSACTION
UPDATE table1 SET col1 = '1234' WHERE col2 = 0987 -- This will succeed --X lock
Session 2 connects and does this
BEGIN TRANSACTION
UPDATE table2 SET col1 = '0987' WHERE col2 = 1234 --This will succeed --X lock
UPDATE table1 SET col1 = 'abcd' WHERE col2 = 0987 --This will wait and could be chosen as the deadlock victim
Session 1 then issues
UPDATE table2 SET col1 = 'abcd' WHERE col2 = 1234 --This will wait and could be chosen as the deadlock victim
What this is trying to do is 2 sessions are updating the same information but in a different order, as the updates have exclusive locks on the data there is no way in which either of the two sessions can carry on with the second updates. SQL then chooses one of the sessions to terminate which is classified as a deadlock. Once SQL kills one of the sessions it then allows the other session to continue with its update.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply