lock_wait_timeout

Controls the timeout in seconds for how long a pessimistic transaction statement waits to acquire a row-level lock. When the timeout is exceeded, the server returns error 20710 Lock wait timeout exceeded; try restarting transaction and rolls back the entire transaction.

Description

lock_wait_timeout sets the maximum time, in seconds, that a statement in a pessimistic transaction waits to acquire a row-level lock held by another transaction. Applicable only when SELECT ... FOR UPDATE or DML statements require row-level locks in a pessimistic transaction.

When the timeout expires before the lock is acquired, MatrixOne returns error 20710 (ErrLockWaitTimeout) with the message “Lock wait timeout exceeded; try restarting transaction” and rolls back the entire current transaction.

Syntax

Set the lock_wait_timeout at the global or session level:

SET GLOBAL lock_wait_timeout = 120;
SET SESSION lock_wait_timeout = 5;
SET lock_wait_timeout = 5;  -- equivalent to SET SESSION

Query the current value:

SELECT @@global.lock_wait_timeout;
SELECT @@session.lock_wait_timeout;
SELECT @@lock_wait_timeout;
SHOW VARIABLES LIKE 'lock_wait_timeout';

Arguments

Property

Value

Variable Type

int

Scope

Global, Session

Dynamic

Yes

Default Value

120

Optional Value

1 – 31536000

Unit

seconds

Permissions

  • Setting the global variable requires SYSTEM_VARIABLES_ADMIN or SUPER privilege.

  • Setting the session variable requires no special privileges.

Examples

DROP DATABASE IF EXISTS lock_wait_timeout_demo;
CREATE DATABASE lock_wait_timeout_demo;
USE lock_wait_timeout_demo;

-- Check the default value
SELECT @@global.lock_wait_timeout, @@session.lock_wait_timeout;

-- Change the session timeout to 5 seconds
SET SESSION lock_wait_timeout = 5;
SELECT @@session.lock_wait_timeout;

DROP DATABASE lock_wait_timeout_demo;

Notes

  • In v4.1.4 the default value changed from 31536000 (approximately 1 year, effectively unlimited) to 120 seconds to prevent a single slow or abandoned transaction from blocking all other waiters behind the same row lock.

  • A lock wait timeout error rolls back the entire transaction, not just the timed-out statement. Any uncommitted writes made earlier in the transaction are discarded.