Impact of Indexes on locking

Indexes are mostly used as a technique to make the queries run faster.But they do have a major impact on the way locks are handled, which I will be covering in this post.

To illustrate the same,as a first step, I am trying to Update a table using a column which doesnt have any index.

Consider the following query:

BEGIN TRAN

UPDATE jobs
SET job_desc = 'index_update'
WHERE job_id = 10

Note that I have removed the default Clustered Index present on jobs table(job_id column) in pubs( default sample database provided by Microsoft ) database.The spid for this connection is 53.A look at query plan indicates that query took a table scan as there was no index on it.

Querying Lockinfo table to check locking schemes for this connection(spid 53)shows that the query has taken a Row-Level Exclusive Lock ( Write lock )on the Row ID 1:115:9.The query has acquired Shared Exclusive locks at Page and table levels.

The transaction is not committed and I am opening another connection to update the same table, but a different row. So I attempt to exeute the following query.

BEGIN TRAN

UPDATE jobs
SET job_desc = 'index_update_2'
WHERE job_id = 8

ConnectionID (SPID) is 54. You will notice that the query doesn't complete at all and has to wait till the query in the other window ( spID 53) commits or rollsback.


The Lock Status for the connection 54 indicates that 54 has successfully acquired locks at page and table levels and for one row( 1:115:7 - which has to update). But spid 54 has to wait to obtain a Row Level Update lock on Row 1:115:9 (which is held by sp53).This is in spite of the fact that it has to update a completely row different from the one getting updated at other connection.

The main reason for this case is the 'table scan' taken by first and second query.As the optimizer opts for a scan( as there is no index on job_id), it has read every row in the table to find the row that meets the required criteria. As the Second query does the scan, it has cross the row exclusively held by other connection(spid 53), though it doesn't satisfy the condition and hence has to wait.

Let us create an index and see how it works.I create the following index.

CREATE UNIQUE CLUSTERED INDEX [PK__jobs__117F9D94] ON [dbo].[jobs] (
[job_id])
ON [PRIMARY]

I execute the same query.

BEGIN TRAN

UPDATE jobs
SET job_desc = 'index_update'
WHERE job_id = 10

The query plan this time indicates an index/update as it uses an index.The lockinfo table details indicate a exclusive lock on Key which is the index key and not on Row ID.
Without Committing the row, the other query is also executed using a different connection(spid 54). But this time, the query completes right away without waiting for the first query(spid 53) to commit or rollback.


The lock info also indicates that spid 54 has successfully obtained locks at page,table and an exclusive lock at the relevant key. spid 54 doesn't apply any lock for row/key that didnt meet its criterion.


The reason why this query works is because of the clustered index on job_id.The optimizer for scanning thro uses the index instead of the table, and hence obtains a lock only on the index key. So, when the other query is executed it doesn't have to wait for other rowlocks to release as the query optimizer directly accesses the row it intends to update using the clustered index. This makes a major difference in improving query performance and reducing deadlock occurrence.

One of the intentions in writing this post was to clarify a myth which said 'Never create indexes on the table which is updated as it slows down updates'. Yes Indexes do slowdown the Updates, but only if it happens to update indexed column. But if you plan to write a Update query, make sure your 'where' clause is properly indexed or else this would potentially run your application into high lock waits and deadlocks.