How to solve SQL Server Error 1222 i.e Unlock a SQL Server table

I faced this error while working with transactions in MS SQL Server with a time consuming query. I was inserting data into a table from a select query which was fetching data from a Linked Server. Because of huge data load the server, query was taking too much time, so I finally stopped the query manually. This was the point which caused the SQL Server Error 1222.

 Lock request time out period exceeded

Since the table was being stuck in a transaction, and I was unable to perform any (read/write) operation on the target table.

Here is the solution I found worked for me.

In the SQL Server Management Studio, first find out details of the active transaction. Execute following command

 DBCC OPENTRAN

You will get the detail of the active transaction, including SPID.

If you want to get the details about the SPID, you can use following commands:

 EXEC sp_who2 <SPID>
 EXEC sp_lock <SPID>

This SPID is the one which you have to kill to unlock the table being stuck in the transaction.

For example the SPID in my case is 65. Kill that process using the following command:

 KILL 65

This will unlock the table and you can perform operations on this table without locking error.