InnoDB row locking - how to implement

Question

I've been looking around now, reading the mysql site and I still can't see exactly how it works.

I want to select and row lock the result for writing, write the change and release the lock. audocommit is on.

scheme

id (int)
name (varchar50)
status (enum 'pending', 'working', 'complete')
created (datetime)
updated (datetime) 

Select an item with a status of pending, and update it to working. Use an exclusive write to make sure that the same item is not picked up twice.

so;

"SELECT id FROM `items` WHERE `status`='pending' LIMIT 1 FOR WRITE"

get the id from the result

"UPDATE `items` SET `status`='working', `updated`=NOW() WHERE `id`=<selected id>

Do I need to do anything to release the lock, and does it work like I've done above?

Answer 1

What you want is SELECT ... FOR UPDATE from within the context of a transaction. SELECT FOR UPDATE puts an exclusive lock on the rows selected, just as if you were executing UPDATE. It also implicitly runs in READ COMMITTED isolation level regardless of what the isolation level is explicitly set to. Just be aware that SELECT ... FOR UPDATE is very bad for concurrency and should only be used when absolutely necessary. It also has a tendency to multiply in a codebase as people cut and paste.

Here's an example session from the Sakila database which demonstrates some of the behaviors of FOR UPDATE queries.

First, just so we're crystal clear, set the transaction isolation level to REPEATABLE READ. This is normally unnecessary, as it is the default isolation level for InnoDB:

session1> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
session1> BEGIN;
session1> SELECT first_name, last_name FROM customer WHERE customer_id = 3;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| LINDA      | WILLIAMS  |
+------------+-----------+
1 row in set (0.00 sec)    

In the other session, update this row. Linda got married and changed her name:

session2> UPDATE customer SET last_name = 'BROWN' WHERE customer_id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Back in session1, because we were in REPEATABLE READ, Linda is still LINDA WILLIAMS:

session1> SELECT first_name, last_name FROM customer WHERE customer_id = 3;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| LINDA      | WILLIAMS  |
+------------+-----------+
1 row in set (0.00 sec)

But now, we want exclusive access to this row, so we call FOR UPDATE on the row. Notice that we now get the most recent version of the row back, that was updated in session2 outside of this transaction. That's not REPEATABLE READ, that's READ COMMITTED

session1> SELECT first_name, last_name FROM customer WHERE customer_id = 3 FOR UPDATE;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| LINDA      | BROWN     |
+------------+-----------+
1 row in set (0.00 sec)

Let's test out the lock set in session1. Note that session2 cannot update the row.

session2> UPDATE customer SET last_name = 'SMITH' WHERE customer_id = 3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

But we can still select from it

session2> SELECT c.customer_id, c.first_name, c.last_name, a.address_id, a.address FROM customer c JOIN address a USING (address_id) WHERE c.customer_id = 3;
+-------------+------------+-----------+------------+-------------------+
| customer_id | first_name | last_name | address_id | address           |
+-------------+------------+-----------+------------+-------------------+
|           3 | LINDA      | BROWN     |          7 | 692 Joliet Street |
+-------------+------------+-----------+------------+-------------------+
1 row in set (0.00 sec)

And we can still update a child table with a foreign key relationship

session2> UPDATE address SET address = '5 Main Street' WHERE address_id = 7;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

session1> COMMIT;

Another side effect is that you greatly increase your probability of causing a deadlock.

In your specific case, you probably want:

BEGIN;
SELECT id FROM `items` WHERE `status`='pending' LIMIT 1 FOR UPDATE;
-- do some other stuff
UPDATE `items` SET `status`='working', `updated`=NOW() WHERE `id`=<selected id>;
COMMIT;

If the "do some other stuff" piece is unnecessary and you don't actually need to keep information about the row around, then the SELECT FOR UPDATE is unnecessary and wasteful and you can instead just run an update:

UPDATE `items` SET `status`='working', `updated`=NOW() WHERE `status`='pending' LIMIT 1;

Hope this makes some sense.

Answer 2

If you are using InnoDB storage engine it uses row-level locking. In conjunction with multi-versioning, this results in good query concurrency because a given table can be read and modified by different clients at the same time. Row-level concurrency properties are as follows:

Different clients can read the same rows simultaneously.

Different clients can modify different rows simultaneously.

Different clients cannot modify the same row at the same time. If one transaction modifies a row, other transactions cannot modify the same row until the first transaction completes. Other transactions cannot read the modified row, either, unless they are using the READ UNCOMMITTED isolation level. That is, they will see the original unmodified row.

Basically, you do not have to specify explicit locking InnoDB handles it iteslf although in some situation you may have to give explicit lock details about explicit lock is given below:

The following list describes the available lock types and their effects:

READ

Locks a table for reading. A READ lock locks a table for read queries such as SELECT that retrieve data from the table. It does not allow write operations such as INSERT, DELETE, or UPDATE that modify the table, even by the client that holds the lock. When a table is locked for reading, other clients can read from the table at the same time, but no client can write to it. A client that wants to write to a table that is read-locked must wait until all clients currently reading from it have finished and released their locks.

WRITE

Locks a table for writing. A WRITE lock is an exclusive lock. It can be acquired only when a table is not being used. Once acquired, only the client holding the write lock can read from or write to the table. Other clients can neither read from nor write to it. No other client can lock the table for either reading or writing.

READ LOCAL

Locks a table for reading, but allows concurrent inserts. A concurrent insert is an exception to the "readers block writers" principle. It applies only to MyISAM tables. If a MyISAM table has no holes in the middle resulting from deleted or updated records, inserts always take place at the end of the table. In that case, a client that is reading from a table can lock it with a READ LOCAL lock to allow other clients to insert into the table while the client holding the read lock reads from it. If a MyISAM table does have holes, you can remove them by using OPTIMIZE TABLE to defragment the table.

Answer 3

Another alternative would be to add a column which stored the time of the last successful lock and then anything else that wanted to lock the row would need to wait until it was either cleared or 5 minutes (or whatever) had elapsed.

Something like...

Schema

id (int)
name (varchar50)
status (enum 'pending', 'working', 'complete')
created (datetime)
updated (datetime)
lastlock (int)

lastlock is an int as it stores the unix timestamp as its easier (and maybe quicker) to compare against.

// Excuse the semantics, I haven't checked they acutally run, but they should be close enough if they don't.

UPDATE items 
  SET lastlock = UNIX_TIMESTAMP() 
WHERE 
  lastlock = 0
  OR (UNIX_TIMESTAMP() - lastlock) > 360;

Then check to see how many rows were updated, because rows cannot be updated by two processes at once, if you updated the row, you got the lock. Assuming you're using PHP, you'd use mysql_affected_rows(), if the return from that was 1, you successfully locked it.

Then you can either update the lastlock to 0 after you've done what you need to do, or be lazy and wait 5 minutes when the next lock attempt would succeed anyway.

EDIT: You may need to a bit of work to check it works as expected around summer time changes as the clocks would go back an hour, perhaps rendering the check void. You'd need to ensure the unix timestamps were in UTC - which they may be anyway.

Answer 4

Alternatively, you could fragment the record fields to allow parallel writing and bypass the row locking (fragmented json pairs style). So if one field of a compound read record was an integer/real you could have fragment 1-8 of that field (8 write records/rows in effect). Then sum the fragments round-robin after every write into a separate read lookup. This allows upto 8 concurrent users in parallel.

As you are only working with each fragment creating a partial total, there is no collision and true parallel updates (ie you write lock each fragment rather than the whole unified read record). This only works on numerical fields obviously. Something that relies on mathematical modification to store a result.

Thus, multiple write fragments per unified read field per unified read record. These numerical fragments also lend themselves to ECC, encryption and block level transfer/storage. The more write fragments there are, the greater the parallel/concurrent write access speeds on saturated data.

MMORPG suffer massively with this issue, when large numbers of players all start hitting each other with Area of Effect skills. Those multiple players all need to write/update every other player at exactly the same time, in parallel, creating a write row locking storm on unified player records.