Hack 67. Lock Implicitly Within Transactions

By following some simple rules, you can write queries that avoid the risk of concurrency problems without worrying about locking.

You can avoid having to set locks explicitly by performing several changes in a single UPDATE statement.

With AUTOCOMMIT and single-statement SQL transactions, you can forget about locking and COMMIT/ROLLBACK.

Consider Hapless Bank, which holds its customers' balances in a simple table, shown in Table 9-2.

Suppose you need to transfer $3 from Ritchie's account to Archie's account. You can perform the transfer only if both accounts exist and Ritchie has at least $3 in his account.

You can update both balances and implement the condition in a single UPDATE statement. As every SQL statement is guaranteed to be atomic, you can be sure that either both changes are made or neither change is made. This avoids the potentially disastrous case of Archie's account being credited without Ritchie's account being debited (or vice versa):

UPDATE bank SET balance = CASE WHEN name='Archie' THEN balance+3 WHEN name='Ritchie' THEN balance-3 END WHERE name IN ('Archie','Ritchie') AND EXISTS (SELECT name FROM bank WHERE name='Archie') AND EXISTS (SELECT name FROM bank WHERE name='Ritchie' AND balance>=3);

This works well for SQL Server, Oracle, and PostgreSQL.

In MySQL, you cannot include the table being updated in the WHERE clause of an UPDATE statement. However, MySQL does permit you to update a JOIN, so you can exploit this by performing the tests in a derived table:

UPDATE bank w CROSS JOIN (SELECT COUNT(*) AS c FROM bank WHERE name='Archie' OR (name='Ritchie' AND balance>=3)) t SET w.balance = CASE WHEN w.name='Archie' THEN w.balance+3 WHEN w.name='Ritchie' THEN w.balance-3 END WHERE w.name IN ('Archie','Ritchie') AND t.c=2;