Can anyone explain this to me? Is this a bug? As per locking concepts, this is not possible (or wasn't until I found it). The update statements are operating on the same table but different columns. One does a IF EXISTS and then issues an UPDATE statement.
The WHERE clause of the IF EXISTS check and the actual UPDATE statement is the same. The other one issues the UPDATE statement directly. The locked resource is the same, the primary key.
The scenario of conversion deadlock is transactions which hold Shared locks on the same resource, and want to convert to the shared lock to the exclusive lock to update the resource. Each one will wait for others to release the shared lock and all of them cannot
release the shared lock until then take the exclusive lock. To avoid this type of deadlock, you can explicitly specify the lock hint UPDLOCK instead of shared lock. The blog below elaborated on the conversion deadlock: http://www.sql-server-performance.com/2004/advanced-sql-locking/
According to your description, did you mean that two transactions with each holding an UPDATE statement lead to a conversion deadlock? Is there any shared lock before the update lock? You can use the view below to see the locks in a database before the transaction
CREATEVIEW DBlocks ASSELECT request_session_id as spid,
db_name(resource_database_id) as dbname,
CASEWHEN resource_type = 'OBJECT'THENobject_name(resource_associated_entity_id)
WHEN resource_associated_entity_id = 0 THEN'n/a'ELSEobject_name(p.object_id)
ENDas entity_name, index_id,
resource_type as resource,
resource_description as description,
request_mode as mode, request_status as status
FROM sys.dm_tran_locks t LEFTJOIN sys.partitions p
ON p.partition_id = t.resource_associated_entity_id
WHERE resource_database_id = db_id();
--obtain the lock informationselect * from DBlocks orderby spid,index_id,resource
If the deadlock can be reproduced, please post the table’s DDL structure and sample data along with queries.