SQL SERVER – Alternate Fix : ERROR 1222 : Lock request time out period exceeded

ERROR 1222 : Lock request time out period exceeded.

MSDN Suggests solution here.

It says find offending transaction and terminate it and run the query again. Though sometime there is requirement that we can not terminate anything. If we know which transaction is locking up resources and database, we need to still run the same transaction.

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.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads