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;

Hack 68. Cope with Unexpected Redo

When a user initiates an operation you can ensure that your code copes well with repeated attempts.

Suppose you are writing user account creation routines for a web application. A user can create an account on your system, where the username is his preferred email address. As part of the process, the user will be given a random password if he was successful, or will be shown an error message if appropriate. The form uses USER for username and PASS for password. The username column is the primary key of usertable :

CREATE TABLE usertable (
  username varchar(20) primary key,
  password varchar(20)
);

Here is the PHP to provide a user creation form:

<?
function randomPass (  ) {
  srand((double)microtime(  )*1000000);
  return rand(0,10000);
}
mysql_connect('localhost','



username



','



password



') or die(mysql_error(  ));
mysql_select_db('



dbname



')                        or die(mysql_error(  ));
if (! array_key_exists('_submit_check', $_POST)) {
  print '<form method="POST">';
  print '<input type="text" name="username" value="">';
  print '<input type="submit" name="Order">';
  print '<input type="hidden" name="_submit_check" value="1"/>';
  print '</form>';
} else {
  $sql = sprintf("SELECT username FROM usertable WHERE username = '%s%%'",
                    mysql_real_escape_string($_POST['username']));
  $cursor = mysql_query($sql) or die(mysql_error(  ));
  $found = 0;
  while ($line = mysql_fetch_array($cursor,MYSQL_ASSOC)){
    $found = 1;
  }
  if ($found == 1) {
    print '<p>That username is already in use</p>';
  } else {
    $pass = randomPass(  );
    $sql = sprintf("INSERT INTO usertable VALUES('%s%%','%s')",
                    mysql_real_escape_string($_POST['username']),$pass);
    $cursor = mysql_query($sql) or die(mysql_error(  ));
    print '<p>Your account has been created, password '.$pass.'</p>';
  }
}
?>

This user creation script is nonrepeatable. This means that running the code with the same input twice results in two different messages. Often this type of approach is accompanied by a warning that appears next to the Submit button: "Click this button only once!" Such warnings are common in "shopping- basket " type sites.

If the user does click the button twice, he receives an error message stating his email address is already being used as an account name. He can't log in using that email address, as the random password was not shown to him. By using a repeatable approach you can make the system much more user-friendly.

One way to make the query repeatable is to put a hidden field, random , in your form. When the form is generated set the hidden field to a random number. Now change the code so that multiple submissions with that value for random involving the same username always appear to succeed. You need to store the random value in usertable , in this case in a column called ccode :

ALTER TABLE usertable ADD ccode INT;

The new PHP code looks like this:

<?
function randomPass (  ) {
  srand((double)microtime(  )*1000000);
  return rand(0,10000);
}
function randomCode (  ) {
  srand((double)microtime(  )*1000000);
  return rand(0,10000000);
}
mysql_connect('localhost','



user



','



password



') or die(mysql_error(  ));
mysql_select_db('



dbname



')                    or die(mysql_error(  ));
if (! array_key_exists('_submit_check', $_POST)) {
  print '<form method="POST">';
  print '<input type="text" name="username" value="">';
  print '<input type="submit" name="Order">';
  print '<input type="hidden" name="_submit_check" value="1"/>';
  print '<input type="hidden" name="random" value="'.randomCode(  ).'"/>';
  print '</form>';
} else {
  $sql = sprintf("SELECT username,password,ccode FROM usertable
                  WHERE username = '%s'",
                    mysql_real_escape_string($_POST['username']));
  $cursor = mysql_query($sql) or die(mysql_error(  ));
  $found = 0;
  $pass = randomPass(  );
  while ($line = mysql_fetch_array($cursor,MYSQL_ASSOC)){
    $found = 1;
    if ($line{ccode} == $_POST['random']) {
      $found = 2;
      $pass=$line{password};
    }
  }
  if ($found == 1) {
    print '<p>That username is already in use</p>';
  } elseif ($found == 2) {
    print '<p>Your pressed the submit button more than once. But thats ok!</p>';
    print '<p>Your account has been created, password '.$pass.'</p>';
  } elseif ($found == 0) {
    if (!preg_match("/^\d+$/", $_POST['random'])){
      print "SQL Injection Attack Alert.";
      exit(1);
    }
    $sql = sprintf("INSERT INTO usertable VALUES('%s','%s',%d)",
                    mysql_real_escape_string($_POST['username']),$pass
                    ,$_POST['random']);
    $cursor = mysql_query($sql) or die(mysql_error(  ));
    print '<p>Your account has been created, password '.$pass.'</p>';
  }
}
?>

The new code allows you to click the Submit Query button as many times as you want, and the system will always seem to work right. Figure 9-2, Figure 9-3, and Figure 9-4 show examples of it in operation.

Figure 9-2. Registering using your email address

Figure 9-3. The first time Submit Query is clicked

Figure 9-4. Second and subsequent submit attempts

The check using the random code is an essential part of this algorithm. If you don't check this, anyone can come along at any point in the future and register with an email address that is already in use, and have the current password of that user displayed.

9.5.1. Shopping Baskets

You can extend this algorithm to shopping-basket processing with only a slight increase in complexity. The important thing to do is to ensure that you have an atomic test-and-set before dealing with the basket, and to store in the database the current state of the purchase process. With this, the first transaction to pass the test-and-set can handle the purchases, and all other repeated transactions display just the current state. For isolation levels such as REPEATABLE READ , READ COMMITTED , and SERIALIZABLE , testing an INSERT transaction should provide you with an atomic test-and-set, providing there is a primary key on the table involved.

The first transaction to start (i.e., the one triggered by your first Submit Query button click) is as follows :

mysql>

INSERT INTO purchase (basketid,state) VALUES ('00001','Processing');

Query OK, 1 row affected (0.00 sec)

mysql>

COMMIT;

Query OK, 0 rows affected (0.00 sec)

Your program processes order, checks availability, works out costs

mysql>

UPDATE purchase SET state = 'Checking your credit card'

->

WHERE basketid='00001';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql>

COMMIT;

Query OK, 0 rows affected (0.00 sec)

Your program asks VISA if the card transaction was successfully completed.

mysql>

UPDATE purchase SET state = 'Order complete. Items dispatched.'

->

WHERE basketid='00001';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql>

COMMIT;

Query OK, 0 rows affected (0.00 sec)

The next transaction to get this far starts the same way, but it finds that the INSERT fails with an error. This means that the script changes to displaying the current order state. The user, or perhaps JavaScript, can refresh the web page to keep the user informed of the current basket-processing state:

mysql>

INSERT INTO purchase (basketid,state) VALUES ('00001','Processing');

ERROR 1062: Duplicate entry '00001' for key 1
mysql>

COMMIT;

Query OK, 0 rows affected (0.00 sec)

With the error in the INSERT you know that another transaction is dealing


with this.

mysql>

SELECT state FROM purchase WHERE basketid='00001';

+----------------------+
 state                
+----------------------+
 Checking your credit 
+----------------------+
1 row in set (0.00 sec)