Hack 65. Use Pessimistic Locking

You have a complicated transaction that needs multiple SQL statements, and you want reliable updates with good performance using database locking.

Consider a booking system for a small theatre. The theatre has four seatstwo at the front and two at the back:

CREATE TABLE seat (
 chairid INT,
 location varchar(20),
 booked varchar(20))
 ENGINE=InnoDB; 

INSERT INTO seat (chairid,location,booked) VALUES (1,'front',NULL);
INSERT INTO seat (chairid,location,booked) VALUES (2,'front',NULL);
INSERT INTO seat (chairid,location,booked) VALUES (3,'back',NULL);
INSERT INTO seat (chairid,location,booked) VALUES (4,'back',NULL);

You need to specify ENGINE=InnoDB only if you are using MySQL and InnoDB is not the default; delete this phrase (but keep the semicolon) on all other platforms.

Be sure to run SHOW WARNINGS after you issue the CREATE TABLE statement. If MySQL insists on using the MyISAM table type, your version of MySQL was not compiled with InnoDB support.

Customers phone one of two possible operators (called X and Y) to check seat availability and make bookings. The following transaction attempt will not work properly:

 Operator X checks to see which seats are currently free 
mysql> START TRANSACTION; 
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT chairid FROM seat WHERE booked IS NULL; 
+---------+
| chairid |
+---------+
| 1 |
| 2 |
| 3 |
| 4 |
+---------+
4 rows in set (0.00 sec)
 Operator X intends to book seats 1 and 2 

Meanwhile, operator Y is dealing with another inquiry; she issues the same query and gets the same response. She decides to book seat 1:

START TRANSACTION;
UPDATE seat SET booked='Y' WHERE chairid=1;
COMMIT;

Operator X is a little slower than operator Y and her booking comes through a few seconds later:

UPDATE seat SET booked='X' WHERE chairid=1;
UPDATE seat SET booked='X' WHERE chairid=2;
COMMIT;

With the database in READ COMMITTED, both of these transactions succeed. The seat table looks like this:

mysql> SELECT * FROM seat;
+---------+----------+--------+
| chairid | location | booked |
+---------+----------+--------+
| 1 | front | X |
| 2 | front | X |
| 3 | back | NULL |
| 4 | back | NULL |
+---------+----------+--------+
4 rows in set (0.00 sec)

You have completely lost the booking for operator Y. Obviously this is a serious programming blunder. These errors are possible when concurrency control is not taken seriously (see "Determine Your Isolation Level" [Hack #64]).

You could solve this problem by switching to SERIALIZABLE isolation level transactions. However, in large data sets this solution would affect performance. In most cases, you do not need SERIALIZABLE.

One solution is to use pessimistic locking. With pessimistic locking you directly inform the database about things you likely will be changing. You can do the following in Oracle, MySQL, and PostgreSQL:

SELECT chairid FROM seat WHERE booked IS NULL FOR UPDATE; 
 Operator talks to the customer and get the booking details... then 
UPDATE seat SET booked='X' WHERE chairid=1;
UPDATE seat SET booked='X' WHERE chairid=2;
COMMIT;

In SQL Server, you need to replace FOR UPDATE with WITH HOLDLOCK. The SELECT statement becomes:

SELECT chairid FROM seat WHERE booked IS NULL WITH HOLDLOCK;

With this approach, operator X, being the first to run the SELECT FOR UPDATE, locks all the available front seats. Operator Y, when she runs SELECT FOR UPDATE, is forced to wait until X does a COMMIT. This approach is excellent when you want a transaction to read data from the database and then perform database updates without concern for concurrent changes.

The big disadvantage of this approach is that operator Y cannot deal with her phone call until X has done a COMMIT. However, operator Y can deal with bookings simultaneously with X if X is booking front seats and Y is booking back seats:

SELECT chairid WHERE booked IS NULL AND location='front' FOR UPDATE

By partitioning the seats into smaller groups, you can obtain more concurrency. A good algorithm might be that operator X books seats to the left of center, and Y to the right of center. In this way, you can construct an algorithm whereby blocking is rare, perhaps happening only when the theatre is nearing capacity.