'new_entity0'.new_entityId as 'new_entityid'
, 'new_entity0'.OwningBusinessUnit as 'owningbusinessunit'
, 'new_entity0'.OwnerId as 'ownerid'
, 'new_entity0'.OwnerIdType as 'owneridtype'
from new_entity as 'new_entity0'
where ('new_entity0'.new_entityId = @new_entityId0)
where ([new_entityId] = @new_entityId1)
These were deadlocked, with the SELECT statement being the deadlock victim. The locks that caused the deadlock were:
- The SELECT statement had a shared lock on the new_entityExtensionBase table, and was requesting a shared lock on new_entityBase table
- The UPDATE statement had an update lock on the new_entityBase table, and was requesting an update lock on new_entityExtensionBase table
- Although the SELECT statement was requesting fields from the new_entityBase table, it had obtained a lock on the new_entityExtensionBase table to perform the join in the new_entity view
- The UPDATE statement that updates a custom attribute (new_attribute) on the new_entity entity would have been the second statement of 2 in the transaction. The first statement would modify system fields (e.g. modifiedon) in the new_entityBase table, and hence place an exclusive lock on a row in the new_entityBase table, and the second statement is the one above, which is attempting to update the new_entityExtensionBase table
- With just the one entity table, the SELECT statement only needs one lock, and does not need to obtain one lock, then request another
- Only one UPDATE statement is required in the transaction, so locks are only required on the one table and they can be requested together, as they would be part of just one statement
- Both operations will complete more quickly, reducing the time for which the locks are held