asked on

Transaction deadlocked

I'm using CS 4.0. I'm getting an SQL exception: "Transaction (process ID 56) was deadlocked on lock | generic waitable object resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

The query being executed is

UPDATE PatientProtocols
SET
	LockProcessGuid = @LockProcessGuid,
	LockInstanceNumber = @LockInstanceNumber
WHERE	PatientProtocolId = @PatientProtocolId

Open in new window

where the appropriate parameters have been passed in.

When the exception occurs, I can try to run the update from MSSMS. When I do, it does not terminate; I have to stop it.

My guess is that either the table or a record is being locked. It does not seem to depend on the values of the parameters, so probably the table. I've tried running

SELECT * FROM sys.dm_tran_locks

Open in new window

but am not sure what the results mean.

Advice?

BlearyEye

ASKER

After looking around more, I have the following code to give lock info:

SELECT 
	 object_name(resource_associated_entity_id) as 'TableName',
     SessionID = s.Session_id,
     resource_type,   
     DatabaseName = DB_NAME(resource_database_id),
     request_mode,
     request_type,
     login_time,
     host_name,
     program_name,
     client_interface_name,
     login_name,
     nt_domain,
     nt_user_name,
     s.status,
     last_request_start_time,
     last_request_end_time,
     s.logical_reads,
     s.reads,
     request_status,
     request_owner_type,
     objectid,
     dbid,
     a.number,
     a.encrypted ,
     a.blocking_session_id,
     a.text       
 FROM   
     sys.dm_tran_locks l
     JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
     LEFT JOIN   
     (
         SELECT  *
         FROM    sys.dm_exec_requests r
         CROSS APPLY sys.dm_exec_sql_text(sql_handle)
     ) a ON s.session_id = a.session_id
 WHERE  
     s.session_id > 50 AND resource_database_id = DB_ID()
ORDER BY program_name

Open in new window

However, this hasn't helped, as it hasn't shown so far the conflicting lock.

BlearyEye

ASKER

I hope someone chimes in here ... to the query mentioned at the beginning I added the line

DBCC TRACEON(3605, 1204)

Open in new window

In the log file there are messages that these traces have been turned on. However, there is no record of a deadlock detection.

SOLUTION

BlearyEye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lcohan

You could set SQL Alert on deadlocks - just look under SQL Agent in SSMS and add a new alert, then select appropriate event you want to alert on if it occurs.

http://msdn.microsoft.com/en-us/library/ms180982.aspx
http://www.simple-talk.com/sql/database-administration/sql-server-alerts-soup-to-nuts/
http://www.mssqltips.com/sqlservertip/1523/how-to-setup-sql-server-alerts-and-email-operator-notifications/

BlearyEye

ASKER

So the use of TRACEON wasn't the right thing to do?

lcohan

I didn't say that - the traceflag 3605 in conjunction with 1204 should send deadlock info to SQL error log but you asked also about why you got no alert? You must setup an alert in SQL in order to get it if your app is not capturing deadlock errors from sql db.

BlearyEye

ASKER

I guess I'm confused. What I was trying to ask is why I don't see anything in the log.

So do I need to set up an alert in order to see something in the log? Or are those two independent?

ASKER CERTIFIED SOLUTION

Daniel_PL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BlearyEye

ASKER

I added my own comment inasmuch as it indicates the resolution to the underlying problem.