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.

Table 9-2. Bank balances

Name Balance
Ritchie $10
Archie $10

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;


SQL Fundamentals

Joins, Unions, and Views

Text Handling

Date Handling

Number Crunching

Online Applications

Organizing Data

Storing Small Amounts of Data

Locking and Performance

Reporting

Users and Administration

Wider Access

Index



SQL Hacks
SQL Hacks
ISBN: 0596527993
EAN: 2147483647
Year: 2004
Pages: 147

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net