ERROR 1222: Lock request time out period exceeded.
It says find the offending transaction and terminate it and run the query again. Though sometimes there is a requirement that we cannot terminate anything. If we know which transaction is locking up resources and database, we need to still run the same transaction.
- Locate the transaction that is holding the lock on the required resource, if possible. Use sys.dm_os_waiting_tasks and sys.dm_tran_locksdynamic management views.
- If the transaction is still holding the lock, terminate that transaction if appropriate.
- Execute the query again.
Alternate Fix/WorkAround/Solution:
In this scenario following changes must be done in the offending transaction:
1) Modify the Transaction use query hints (use RECOMPILE,MAXDOPhints)
2) Run big Transaction in smaller transactions.
3) Upgrade Hardware if possible.
4) To prevent this, make sure every BEGIN TRANSACTION has COMMIT
5) If you are running this in SQL Server Management Studio, you can close the query windows and it will automatically close the transaction.
In this scenario following changes must be done in the offending transaction:
1) Modify the Transaction use query hints (use RECOMPILE,MAXDOPhints)
2) Run big Transaction in smaller transactions.
3) Upgrade Hardware if possible.
4) To prevent this, make sure every BEGIN TRANSACTION has COMMIT
5) If you are running this in SQL Server Management Studio, you can close the query windows and it will automatically close the transaction.
6) also check whether SQL audit features enabled or not
If this error occurs frequently change the lock time-out period or modify the offending transactions so that they hold the lock in less time.
