wait to read a row when there is a lock on it -- transaction isolation

Question

I want to lock a table row so that any other reading operations on that row will wait until the lock is released, and I want to avoid to have a lock on the entire table.

I have the following table (InnoDB)

CREATE TABLE account_balance (
    account INTEGER UNIQUE, 
    balance INTEGER NOT NULL, 
    PRIMARY KEY (account)
);

I execute the following transaction to obtain a lock on a row

START TRANSACTION;
SELECT balance FROM account_balance WHERE account = 1 FOR UPDATE;
SELECT SLEEP(10);
COMMIT;

And I would like the following query to wait for the lock to be released

SELECT balance FROM account_balance WHERE account = 1;

To do it, the only way I found is to run the SELECT as follow

SET autocommit = 0; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT ....;

It will wait for the lock to be released but I should prefix every SELECT with autocommit and isolation level settings for this table.

Is there a way to configure autocommit=0 and transaction-isolation = SERIALIZABLE ONLY at table level?

I know that I could set

[mysqld]
transaction-isolation = SERIALIZABLE
autocommit = 0

in my.cnf, but I do not want to affect other operations done on other tables and schemas.

refs:

Welcome to Stack Overflow, and thanks for an interesting question. From what host language are you connecting to MySQL? — O. Jones, Feb 24 '15 at 13:40
Hi Ollie, thanks for your reply. There will be more than one client reading from that table, and they could be written in different languages (PHP & JS). I'm looking for a "server side" solution because I'd like to enforce it regardless of the language one uses to make the SELECT. — Filippo De Santis, Feb 25 '15 at 05:20

score 0 · Answer 1 · edited May 23 '17 at 12:20

The autocommit and transaction-isolation settings are associated not with any table, but with each connection to MySQL.

Most language bindings have a directly callable function to enable or disable autocommit. For example, http://php.net/manual/en/mysqli.autocommit.php

You can set the transaction isolation level the way you have done. Once you set that for a connection, it will remain set. See this for example. mySQL - Set isolation level using PHP's mysqli

It's generally good practice to put such mode-setting code immediately after the code that establishes the connection, so the modes are predictable for the rest of your application.

wait to read a row when there is a lock on it -- transaction isolation

1 Answers1