Lock request time out period exceeded

Lock request time out period exceeded

ОБЛАСТЬ ПРИМЕНЕНИЯ: SQL Server База данных SQL Azure Azure Synapse Analytics (хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

Сведения Details

Название продукта Product Name SQL Server SQL Server
Идентификатор события Event ID 1222 1222
Источник события Event Source MSSQLSERVER MSSQLSERVER
Компонент Component SQLEngine SQLEngine
Символическое имя Symbolic Name LK_TIMEOUT LK_TIMEOUT
Текст сообщения Message Text Истекло время ожидания запроса на блокировку. Lock request time out period exceeded.

Объяснение Explanation

Другая транзакция удерживает блокировку требуемого ресурса дольше, чем данный запрос может ее ожидать. Another transaction held a lock on a required resource longer than this query could wait for it.

Действие пользователя User Action

Для решения проблемы попробуйте выполнить следующие задачи. Perform the following tasks to alleviate the problem:

Если это возможно, найдите на требуемом ресурсе транзакцию, удерживающую эту блокировку. Locate the transaction that is holding the lock on the required resource, if possible. Используйте динамические административные представления sys.dm_os_waiting_tasks и sys.dm_tran_locks. Use sys.dm_os_waiting_tasks and sys.dm_tran_locks dynamic management views.

Если транзакция все еще удерживает блокировку, завершите эту транзакцию, если это адекватно. If the transaction is still holding the lock, terminate that transaction if appropriate.

Выполните запрос еще раз. Execute the query again.

Если данная ошибка возникает часто, измените время ожидания блокировки или вызывающие ошибку транзакции так, чтобы они удерживали блокировку на меньший период времени. If this error occurs frequently change the lock time-out period or modify the offending transactions so that they hold the lock for less time.

I tried to find something similar, but couldn’t so decided to ask directly.

Читайте также:  Philips xenium e181 инженерное меню

I’m running long bulk update script which is updating

50m records. The updated is broken in batches, with checkpoints to allow clearing the transaction log. Tried to run the script two times now, the first time it broke after updating roughly 17m rows, the second time it stopped at 14m.

Error message: Msg 1222, Level 16, State 43, Line 122 Lock request time out period exceeded

Here is the pseudo code:

I ran it on another database where the number of rows is smaller (

I’m having a problem trying to extend the lock timeout in a sql server SP. No matter what I try it keeps throwing "Lock request time out period exceeded". I’m using java + jtds 1.2.2, c3p0 0.9.1 and sql server 2008. The settings I tried:

SET LOCK_TIMEOUT 10000 inside the SP and with con.createStatement().execute("SET LOCK_TIMEOUT 10000 ") before calling the SP. and in the SP statement : statement.setQueryTimeout( 10 );

The SP is called by : s tatement = con.prepareCall("dbo.store_procedure . ", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); and it sets "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ" inside

any sugestions? anyone with similar problems? thanks in advance

3 Answers 3

If you are not using SQL Server 2008 and onwards, and specifying LOCK_ESCALATION=Disabled, you can forget about the ROWLOCK Hint. SQL Server can, and from my own experience, probably will ignore it and take any lock (page or table) it fills like. There is no forcing locking hints before SQL Server 2008.

Having cleared that, you can use SET LOCK_TIMEOUT -1 to specify an endless timeout.

I highly discourage you to do so and instead try and troubleshoot and optimize (assuming you’re responsible for this) the queries that really need to take locks on that table, speed them up as much as possible to reduce time locked.

Читайте также:  Ntldr is compressed windows 7

Monitor locked resources with EXEC sp_lock TargetSPID to check what kind of locks are actually being taken

Another remark: SET LOCK_TIMEOUT sets the timeout for the current connection, if you are using a connection pool, you are setting the lock timeout for everything that reuses that connection possibly causing unintended behavior in your application

Ссылка на основную публикацию
Adblock detector