CODEWITHSUNDEEP
mysqltransactionslockinginnodb
luky
luky

Reputation: 2370

MySQL "LOCK TABLES" timeout?

What's the timeout for mysql LOCK TABLES statement?

Can't find it anywhere.

I tried to set variable innodb_lock_wait_timeout ini my.cnf but it seems it's related to another (row level) locking not to table locking.

Simply it has no effect for LOCK TABLES.

I want to set some low timeout value for case of deadlock, because if some operation will LOCK tables and something will go wrong, it will hang up the whole site!

Which is stupid for example in case of finishing purchase on your site.

Upvotes: 6

Views: 13979

Answers (5)

Rishabh
Rishabh

Reputation: 2021

The correct answer is the lock_wait_timeout system variable.

From the documentation:

This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000 (1 year). The default is 31536000.

This timeout applies to all statements that use metadata locks. These include DML and DDL operations on tables, views, stored procedures, and stored functions, as well as LOCK TABLES, FLUSH TABLES WITH READ LOCK, and HANDLER statements.

Upvotes: 2

Alex R
Alex R

Reputation: 11881

My work-around is to create a dedicated lock table and just lock a row in that table. This has the advantage of only locking the processes that specifically want to be locked. Other parts of the application can continue to access the tables even if they are at some point touched by the update processes.

Setup

CREATE TABLE `mutex` (
    EMPTY ENUM('') NOT NULL,
    PRIMARY KEY (EMPTY)
);

Usage

set innodb_lock_wait_timeout = 1;
start transaction;
insert into `mutex` values();

[... do the real work here ... or somewhere else ... even a different machine ...]

delete from `mutex`;
commit;

Upvotes: 2

Programster
Programster

Reputation: 12754

I think you are after the table_lock_timout variable which was introduced in MySQL 5.0.10 but subsequently removed in 5.5. Unfortunately, the release notes don't specify an alternative to use, and I'm guessing that the general attitude is to switch over to using InnoDB transactions as @Rick James has stated in his answer.

I think that removing the variable was unhelpful. Others may regard this as a case of the XY Problem, where we are trying to fix a symptom (deadlocks) by changing the timeout period of locking tables when really we should resolve the root cause by switching over to transactions instead. I think there may still be cases where table locks are more suitable to the application than using transactions and are perhaps a lot easier to comprehend, even if they are worse performing.

The nice thing about using LOCK TABLES, is that you can state the tables that you're queries are dependent upon before proceeding. With transactions, the locks are grabbed at the last possible moment and if they can't be fetched and time-out, you then need to check for this failure and roll back before trying everything all over again. It's simpler to have a 1 second timeout (minimum) on the lock tables query and keep retrying to get the lock(s) until you succeed and then proceeding with your queries before unlocking the tables. This logic is at no risk of deadlocks.

I believe the developer's attitude is summed up by the following excerpt from the documetation:

...avoid using the LOCK TABLES statement, because it does not offer any extra protection, but instead reduces concurrency.

Upvotes: 1

Rick James
Rick James

Reputation: 142208

Why are you using LOCK TABLES?

If you are using MyISAM (which sometimes needs LOCK TABLES), you should convert to InnoDB.

If you are using InnoDB, you should never use LOCK TABLES. Instead, depend on innodb_lock_wait_timeout (default is an unreasonably high 50 seconds). And you should check for errors.

InnoDB Deadlocks are caught and immediately cause an error. Certain non-deadlocks may wait for innodb_lock_wait_timeout.

Edit

Since the transaction looks like

BEGIN;
SELECT ...;
compute some stuff
UPDATE ... (using that stuff);
COMMIT;

You need to add FOR UPDATE on the end of the SELECT.

Upvotes: 1

Rahul
Rahul

Reputation: 77846

I think you meant to say the default timeout value; which is 50 Seconds per MySQL Documentation it says

innodb_lock_wait_timeout Default 50 The timeout in seconds an InnoDB transaction may wait for a row lock before giving up. The default value is 50 seconds

Upvotes: 0

Related Questions