Deadlock - Implicit Conversion

Can ‘Implicit Conversions’ cause deadlocks in SQL Server?

Let me start defining deadlocks –

Deadlock - refers to a specific condition when two or more processes are waiting for the other to release a resource, or more than two processes are waiting for resources in a circular chain.

There can be number of reasons for deadlocks –  one such reason can be ‘Implicit Conversions’

What is Implicit Conversion?

A database system where in some tables has columns defined as a particular data type and a procedure is written with the wrong data type with reference to the same column. 

Consider for example that our table actually has ID defined as an INT, but the developer knowingly or unknowingly developed a stored procedure as if the ID column was a Varchar(20) data type.

So, now every time SQL Server has to look for ID it has to convert @ID from Varchar to INT. This is an implicit conversion of the data type.

How does implicit conversion cause performance bottleneck leading to deadlock?

SQL server internally uses a function ‘CONVERT_IMPLICIT’ to do conversion and when the conversion happens the INDEXES  are not used effectively due to the uncertainty involved in the result of the function – which means it has to convert the value for each and every row. This results in SQL Server scanning the entire table looking for the value. This takes time and, under default locking modes, places a share lock on the entire table preventing other processes from updating records while the scan is taking place.

This hold on the entire set of rows in a table might lead to a deadlock when explicit DML operations are required to be performed on the same set of rows.

Solution?

To make sure to have the correct Data type defined in all definitions. Happy Reading!

The following execution plan depicts the usage of Implicit Conversion function