Transactions and Locking

Database queries run one after another. For a website serving pages, it doesn't matter which order the database performs the queries, as long as they all get served quickly. But some kinds of queries need to be performed in a specific order, such as those that are dependent on the results of a previous query, or groups of updates that need to be done as a whole. All table types can make use of locking, but only InnoDB and BDB tables have transactional capabilities built in. This section discusses the various transaction and locking mechanisms.

Transactions in InnoDb Tables

The power of the InnoDB table type comes from using transactions, or SQL statements that are grouped as one. A typical example of this is in a bank transaction. For example, if money is transferred from one person's account to another, there will typically be at least two queries:

UPDATE person1 SET balance = balance-transfer_amount; UPDATE person2 SET balance = balance+transfer_amount;

This is all fine and well, but what happens if something goes wrong, and the system crashes after the first query is completed, but before the second one is complete? Person1 will have the money removed from their account, and believe the payment has gone through, but person2 will be irate, believing the payment was never made. In this sort of case, it's vital that either both queries are processed together, or neither not at all. To do this, you wrap the queries together in what is called a transaction, with a BEGIN statement to indicate the start of the transaction, and a COMMIT statement to indicate the end. Only when the COMMIT is processed, will all queries be made permanent. If something goes wrong in between, you can use the ROLLBACK command to reverse the incomplete part of the transaction.

Let's run some queries to see how this works. You'll have to create the table only if you haven't already done so in Chapter 2:

mysql> CREATE TABLE innotest (f1 INT,f2 CHAR(10),INDEX (f1))TYPE=InnoDB; Query OK,0 rows affected (0.10 sec) mysql> INSERT INTO innotest(f1) VALUES(1); Query OK, 1 row affected (0.00 sec) mysql> SELECT f1 FROM innotest; +------+ | f1   | +------+ |    1 | +------+ 1 row in set (0.21 sec)

Nothing surprising so far! Now let's wrap a query in a BEGIN/COMMIT:

mysql> BEGIN; Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO innotest(f1) VALUES(2); Query OK, 1 row affected (0.05 sec) mysql> SELECT f1 FROM innotest; +------+ | f1   | +------+ |    1 | |    2 | +------+ 2 rows in set (0.16 sec)

If you now do a ROLLBACK, you will undo this transaction, as it has not yet been committed:

mysql> ROLLBACK; Query OK, 0 rows affected (0.00 sec) mysql> SELECT f1 FROM innotest; +------+ | f1   | +------+ |    1 | +------+ 1 row in set (0.17 sec) 

Now let's see what happens if you lost the connection before you completed the transaction:

mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO innotest(f1) VALUES(2); Query OK, 1 row affected (0.00 sec) mysql> EXIT Bye C:\MySQL\bin> mysql firstdb Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 8 to server version: 4.0.1-alpha-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SELECT f1 FROM innotest; +------+ | f1   | +------+ |    1 | +------+ 1 row in set (0.11 sec)

You can repeat the previous statement, this time doing a COMMIT before you exit. Once the COMMIT is run, the transaction is complete, so when you reconnect, the new record will be present:

mysql> BEGIN; Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO innotest(f1) VALUES(2); Query OK, 1 row affected (0.06 sec) mysql> COMMIT; Query OK, 0 rows affected (0.05 sec) mysql> EXIT Bye C:\Program Files\MySQL\bin> mysql firstdb Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 9 to server version: 4.0.1-alpha-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SELECT f1 FROM innotest; +------+ | f1   | +------+ |    1 | |    2 | +------+ 2 rows in set (0.11 sec)

Consistent Reads

By default, InnoDB tables perform a consistent read. What this means is that when a SELECT is performed, MySQL returns the values present in the database up until the most recently completed transaction. If any transactions are still in progress, any UPDATE or INSERT statements will not be reflected. Before you disagree, there is one exception: The open transaction itself can see the changes (you probably noticed that when you did the BEGIN-INSERT-SELECT, the inserted result was displayed). To demonstrate this, you need to have two windows open and be connected to the database.

First add a record from within a transaction in Window1:

mysql> BEGIN; Query OK, 0 rows affected (0.11 sec) mysql> INSERT INTO innotest(f1) VALUES(3); Query OK, 1 row affected (0.05 sec)

Now switch to Window2:

mysql> SELECT f1 FROM innotest; +------+ | f1   | +------+ |    1 | |    2 | +------+ 2 rows in set (0.16 sec)

The 3 you inserted, because it is part of an as yet incomplete transaction, is not returned. Returning results from an incomplete transaction would be an inconsistent read.

Now return to Window1:

mysql> SELECT f1 FROM innotest; +------+ | f1   | +------+ |    1 | |    2 | |    3 | +------+

The 3 is visible here because you are inside the transaction.

Now commit the transaction, while still in Window1:

mysql> COMMIT; 

And now the query in Window2 will reflect the completed transaction:

mysql> SELECT f1 FROM innotest; +------+ | f1   | +------+ |    1 | |    2 | |    3 | +------+ 

Read Locks for Updating

Consistent reads are not always what you need. What if more than one user is trying to add a new record to the innotest table? Each new record inserts a unique ascending number. As in this example, the f1 field is not a PRIMARY KEY or an AUTO_INCREMENT field, so there is nothing in the table structure to prevent a duplicate occurring. But you want to ensure a duplicate could never occur. You would expect to read the existing value of f1 and then insert a new value, incremented by 1. But this does not guarantee a unique value. Look at the following example, starting with Window1:

mysql> BEGIN; mysql> SELECT MAX(f1) FROM innotest; +---------+ | MAX(f1) | +---------+ |       3 | +---------+

At the same time, another user is doing the same in Window2:

mysql> BEGIN; mysql> SELECT MAX(f1) FROM innotest; +---------+ | MAX(f1) | +---------+ |       3 | +---------+ 1 row in set (0.11 sec)

Now, both users (Window1 and Window2) add a new record and commit their transaction:

mysql> INSERT INTO innotest(f1) VALUES(4); Query OK, 1 row affected (0.11 sec) mysql> COMMIT; Query OK, 0 rows affected (0.00 sec)

Now if either user does a SELECT, they'll see the following:

mysql> SELECT f1 FROM innotest; +------+ | f1   | +------+ |    1 | |    2 | |    3 | |    4 | |    4 | +------+ 

The consistent read has not produced what you'd hoped for: records with values 4 and 5. The way to avoid this is with an update lock on the SELECT. By letting MySQL know you are reading in order to update, it will not let anyone else read that value until your transaction is finished.

First, let's remove the incorrect 4 from the table, so you can do it properly this time:

mysql> DELETE FROM innotest WHERE f1=4; Query OK, 2 rows affected (0.00 sec)

Now, set the update lock as follows in Window1:

mysql> BEGIN; mysql> SELECT MAX(f1) FROM innotest FOR UPDATE; +---------+ | MAX(f1) | +---------+ |       3 | +---------+ mysql> INSERT INTO innotest(f1) VALUES(4); Query OK, 1 row affected (0.05 sec)

Meantime, Window2 also tries to set an update lock:

mysql> BEGIN; mysql> SELECT MAX(f1) FROM innotest FOR UPDATE; 

Notice how no results are returned. MySQL is waiting for the transaction in Window1 to complete.

Complete the transaction in Window1:

mysql> COMMIT; Query OK, 0 rows affected (0.00 sec)

Window2 will now return the results of its query, having waited for the INSERT to be completed.

mysql> SELECT MAX(f1) FROM innotest FOR UPDATE; +---------+ | MAX(f1) | +---------+ |       4 | +---------+ 1 row in set (4 min 32.65 sec)

Now, safe in the knowledge that 4 is the latest value, you can add 5 to the table in Window2:

mysql> INSERT INTO innotest(f1) VALUES(5); Query OK, 1 row affected (0.06 sec) mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) 

Read Locks for Sharing

There is another kind of read lock that does not return a value only if the value it is reading has been changed by another incomplete transaction. It only returns the latest data, but it is not part of a transaction that wants to change the value itself. For example, let's use the f2 field created in the innotest table. Assume that the f1 field is populated first, but then only at a later stage in the transaction is a value for f2 added. When you SELECT, you never want to see a record that has a value for the f1 field, but not a value for the f2 field, but you always want the latest record. In this case, you need to wait for the transaction to be finished before you return the results. For example, a transaction begins in Window1:

mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO innotest(f1) VALUES(6); Query OK, 1 row affected (0.00 sec) mysql> UPDATE innotest set f2='Sebastian' WHERE f1=6; Query OK, 1 row affected (0.05 sec) Rows matched: 1  Changed: 1  Warnings: 0

If you do an ordinary SELECT now in Window2, you will not get the latest value (because the previous transaction is incomplete, and InnoDB defaults to a consistent read). However, if you do a SELECT with a LOCK IN SHARE MODE, you will not get a result until the transaction in Window1 is complete.

Running an ordinary query in Window2 returns the following:

mysql> SELECT MAX(f1) FROM innotest; +---------+ | MAX(f1) | +---------+ |       5 | +---------+ 1 row in set (0.17 sec)

Still in Window2, performing the same query with a LOCK IN SHARE MODE will not yet deliver any results:

mysql> SELECT MAX(f1) FROM INNOTEST LOCK IN SHARE MODE; 

Complete the transaction in Window1:

mysql> COMMIT; Query OK, 0 rows affected (0.00 sec)

Now Window2 will return the correct result:

mysql> SELECT MAX(f1) FROM innotest LOCK IN SHARE MODE; +---------+ | MAX(f1) | +---------+ |       6 | +---------+ 1 row in set (4 min 32.98 sec) mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) 

Automatic COMMITs

By default, unless you specify a transaction with BEGIN, MySQL automatically commits statements. For example, a query in Window1 returns the following:

mysql> SELECT f1 FROM innotest; +------+ | f1   | +------+ |    1 | |    2 | |    3 | |    4 | |    5 | |    6 | +------+ 6 rows in set (0.11 sec)

Now, the user in Window2 inserts a record:

mysql> INSERT INTO innotest(f1) VALUES (7); Query OK, 1 row affected (0.00 sec)

It's immediately available in Window1 (remember to complete all your previous examples with a COMMIT statement):

mysql> SELECT f1 FROM innotest; +------+ | f1   | +------+ |    1 | |    2 | |    3 | |    4 | |    5 | |    6 | |    7 | +------+ 7 rows in set (0.11 sec) 

The INSERT from Window2 is immediately available to other windows because the AUTOCOMMIT is set by default. However, with transaction-safe tables (InnoDB, BDB), you can change this behavior by setting AUTOCOMMIT to zero.

First, set AUTOCOMMIT to 0 in Window1:

mysql> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec)

And run this query on Window2:

mysql> SELECT f1 FROM innotest; +------+ | f1   | +------+ |    1 | |    2 | |    3 | |    4 | |    5 | |    6 | |    7 | +------+ 7 rows in set (0.22 sec)

Now, Window1 inserts a record:

mysql> INSERT INTO innotest(f1) VALUES(8); Query OK, 1 row affected (0.00 sec)

This time it's not immediately available to Window2:

mysql> SELECT f1 FROM innotest; +------+ | f1   | +------+ |    1 | |    2 | |    3 | |    4 | |    5 | |    6 | |    7 | +------+ 7 rows in set (0.16 sec)

Because you have AUTOCOMMIT off, the INSERT from Window1 will not be committed until a specific COMMIT is run.

Commit the transaction from Window1:

mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) 

Now the new record is available to Window2:

mysql> SELECT f1 FROM innotest; +------+ | f1   | +------+ |    1 | |    2 | |    3 | |    4 | |    5 | |    6 | |    7 | |    8 | +------+ 8 rows in set (0.11 sec)

However, AUTOCOMMIT=0 does not set this across the entire server, only for that particular session. If Window2 also sets AUTOCOMMIT to 0, you'll experience different behavior.

First, set AUTOCOMMIT in both Window1 and 2:

mysql> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec)

Now, run the following in Window1 to see what's present:

mysql> SELECT f1 FROM innotest; +------+ | f1   | +------+ |    1 | |    2 | |    3 | |    4 | |    5 | |    6 | |    7 | |    8 | +------+ 8 rows in set (0.17 sec)

Add a record in Window2, and commit the transaction:

mysql> INSERT INTO innotest(f1) VALUES(9); Query OK, 1 row affected (0.00 sec) mysql> COMMIT; Query OK, 0 rows affected (0.00 sec)

And now see if it appears in Window1:

mysql> SELECT f1 FROM innotest; +------+ | f1   | +------+ |    1 | |    2 | |    3 | |    4 | |    5 | |    6 | |    7 | |    8 | +------+ 8 rows in set (0.11 sec) 

The 9 from the new record does not appear, even though you have committed the results! The reason is that the SELECT in Window1 is also part of a transaction. The consistent read has been assigned a timepoint, and this timepoint only moves forward when the transaction it was set in is completed.

Commit the transaction in Window1:

mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) mysql> SELECT f1 FROM innotest; +------+ | f1   | +------+ |    1 | |    2 | |    3 | |    4 | |    5 | |    6 | |    7 | |    8 | |    9 | +------+ 9 rows in set (0.22 sec)

As you saw before, the only way to see the latest results is to SELECT with a LOCK IN SHARE MODE. This would have waited until the transaction doing the inserting had done a COMMIT.

Transactions in BDB Tables

BDB tables handle transactions slightly differently than InnoDB tables. First create the table (only if you haven't already in Chapter 2) and then insert a record from Window1:

mysql> CREATE TABLE bdbtest(f1 INT,f2 CHAR(10))TYPE=BDB; Query OK, 0 rows affected (0.28 sec) mysql> BEGIN; Query OK, 0 rows affected (0.06 sec) mysql> INSERT INTO bdbtest(f1) VALUES(1); Query OK, 1 row affected (0.00 sec) 

Now try the following in Window2:

mysql> SELECT f1 FROM bdbtest; 

Window2 waits for the transaction in Window1 to complete. (It does not return a set of results based on the situation before the Window1 transaction began, like InnoDB.)

Only after Window1 commits does Window2 receive the results. Complete the transaction in Window1:

mysql> COMMIT; Query OK, 0 rows affected (0.00 sec)

And the query in Window2 completes (you don't need to type it again):

mysql> SELECT f1 FROM bdbtest; +------+ | f1   | +------+ |    1 | +------+ 1 row in set (3 min 13.99 sec)

Note the long period of time the query took. The fact that there is not a "quick" SELECT in BDB tables means that any transactions that are delayed could have serious performance problems.

As with InnoDB tables, the default mode is AUTOCOMMIT=1. That means that unless you place your changes within a transaction (starting with BEGIN), they are immediately finalized.

Run the following query from Window1:

mysql> SELECT f1 FROM bdbtest; +------+ | f1   | +------+ |    1 | +------+ 1 row in set (0.17 sec)

Now run an INSERT from Window2:

mysql> INSERT INTO bdbtest(f1) VALUES(2); Query OK, 1 row affected (0.06 sec)

It is immediately retrievable from Window1:

mysql> SELECT f1 FROM bdbtest; +------+ | f1   | +------+ |    1 | |    2 | +------+ 2 rows in set (0.16 sec) 

By setting AUTOCOMMIT to 0, the effect is the same as having all statements wrapped in
a BEGIN.

Set AUTOCOMMIT to 0 and insert a record in Window1:

mysql> SET OPTION AUTOCOMMIT=0; Query OK, 0 rows affected (0.11 sec) mysql> INSERT INTO bdbtest(f1) VALUES(3); Query OK, 1 row affected (0.11 sec)

A query run from Window2 will wait while the transaction is active:

mysql> SELECT f1 FROM bdbtest; 

Only when the transaction is committed will the result appear.

Commit the transaction in Window1:

mysql> COMMIT; Query OK, 0 rows affected (0.05 sec)

And now the query retrieves its results in Window2 (you do not have to retype the query):

mysql> SELECT f1 FROM bdbtest; +------+ | f1   | +------+ |    1 | |    2 | |    3 | +------+

3 rows in set (2 min 8.14 sec)

Further Transactional Behavior

There are a number of other commands that automatically end a transaction (in other words, they behave as if you'd performed a COMMIT):

  • BEGIN

  • ALTER TABLE

  • CREATE INDEX

  • RENAME TABLE (this is a synonym for ALTER TABLE x RENAME)

  • TRUNCATE

  • DROP TABLE

  • DROP DATABASE

Even if the command is unsuccessful, just attempting the command will effectively cause a COMMIT. For example, begin the following transaction inWindow1:

mysql> BEGIN; mysql> SELECT MAX(f1) FROM innotest FOR UPDATE; +---------+ | MAX(f1) | +---------+ |       9 | +---------+

And begin another transaction in Window2:

mysql> BEGIN; mysql> SELECT MAX(f1) FROM innotest FOR UPDATE; 

The results are not displayed, as Window1 has locked the row for updating. However, the user on Window1 changes their mind and decides to change the structure of the table first.

  • Run the ALTER TABLE on Window1:

    mysql> ALTER TABLE innotest add f1 INT; ERROR 1060: Duplicate column name 'f1'

Even though the ALTER was a failure, the lock was released and the transaction committed, and the query waiting on Window2 completes (you do not need to retype).

mysql> SELECT MAX(f1) FROM innotest FOR UPDATE; +---------+ | MAX(f1) | +---------+ |       9 | +---------+ 1 row in set (2 min 23.52 sec)

Locking Tables

In the discussions on InnoDB and BDB tables, you've come across the concept of row-level locking, where individual rows are locked for a period of time. Row-level locks are much more efficient when the table needs to perform high volumes of INSERTs or UPDATEs. Row-level locking, though, is available only to transaction-safe table types (BDB and InnoDB). MySQL also has table-level locking, which is available to all table types.

There are two kinds of table locks: read locks and write locks. Read locks mean that only reads may be performed on the table, and writes are locked. Write locks mean that no reads or writes may be performed on the table for the duration of the lock. The syntax to lock a table is as follows:

LOCK TABLE tablename {READ|WRITE}

To unlock a table simply use the UNLOCK TABLES statement, as follows:

UNLOCK TABLES 

The following demonstrates a table-level lock in action, and will work with any table type. First, lock the table from Window1:

mysql> LOCK TABLE customer READ; Query OK, 0 rows affected (0.01 sec)

Other threads can now read, but not write, as you can see by trying the following from Window2:

mysql> SELECT * FROM customer; +------+------------+-------------+ | id   | first_name | surname     | +------+------------+-------------+ |    1 | Yvonne     | Clegg       | |    2 | Johnny     | Chaka-Chaka | |    3 | Winston    | Powers      | |    4 | Patricia   | Mankunku    | +------+------------+-------------+ 

mysql> INSERT INTO customer(id,first_name,surname) VALUES(5,'Francois','Papo'); 

The INSERT statement is not processed until the lock is released by Window1:

mysql> UNLOCK TABLES; 

The INSERT on Window2 now completes (no need to type it again):

mysql> INSERT INTO customer(id,first_name,surname) VALUES(5,'Francois','Papo'); Query OK, 1 row affected (7 min 0.74 sec)

You can also lock more than one table at a time. Place the following locks from Window1:

mysql> LOCK TABLE customer READ,sales WRITE; 

Other threads can now read the customer table, but not the sales table. Try to run a SELECT from Window2:

mysql> SELECT * FROM sales; 

If the thread that created the lock tries to add a record to the customer table, it will fail. It will not wait for the lock to be released (because it created the lock, if it hung it would never be able to release the lock); rather, the INSERT just fails. Try this from Window1:

mysql> INSERT INTO customer VALUES (1,'a','b'); ERROR 1099: Table 'customer' was locked with a READ lock and can't be updated 

However, it can perform reads on the table it write locked, as follows, still on Window1:

mysql> SELECT * FROM sales; +------+-----------+------+-------+ | code | sales_rep | id   | value | +------+-----------+------+-------+ |    1 |         1 |    1 |  2000 | |    2 |         4 |    3 |   250 | |    3 |         2 |    3 |   500 | |    4 |         1 |    4 |   450 | |    5 |         3 |    1 |  3800 | |    6 |         1 |    2 |   500 | |    7 |         2 | NULL |   670 | +------+-----------+------+-------+ mysql> UNLOCK TABLES; 
 

And with the write lock released, Window2 now processes the SELECT (no need to retype):

mysql> SELECT * FROM sales; +------+-----------+------+-------+ | code | sales_rep | id   | value | +------+-----------+------+-------+ |    1 |         1 |    1 |  2000 | |    2 |         4 |    3 |   250 | |    3 |         2 |    3 |   500 | |    4 |         1 |    4 |   450 | |    5 |         3 |    1 |  3800 | |    6 |         1 |    2 |   500 | |    7 |         2 | NULL |   670 | +------+-----------+------+-------+ 7 rows in set (5 min 59.35 sec)
Note 

You can use either the singular or the plural form. Both [UN]LOCK TABLE and [UN]LOCK TABLES are valid, no matter how many tables you're locking. MySQL doesn't care about grammar!

Write locks have a higher priority than read locks, so if one thread is waiting for a read lock, and a request for a write lock comes along, the read lock has to wait until the write lock has been obtained, and released, before it obtains its read lock, as follows:

Place a write lock from Window1:

mysql> LOCK TABLE customer WRITE; Query OK, 0 rows affected (0.00 sec)

And try to place a read lock from Window2:

mysql> LOCK TABLE customer READ; 

The read lock cannot be obtained until the write lock is released. In the meantime, another request for a write lock comes along, which also has to wait until the first write lock is released.

Attempt to place another write lock from a third window, Window3:

mysql> LOCK TABLE customer WRITE; 

Now release the lock from Window1:

mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)

The write lock from Window 3 is now obtained, even though it was requested after the read lock, as follows (no need to retype the LOCK statement):

mysql> LOCK TABLE customer WRITE; Query OK, 0 rows affected (33.93 sec) mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)

Only when the write lock on Window 3 is released can the read lock from Window2 be obtained (no need to retype):

mysql> LOCK TABLE customer READ; Query OK, 0 rows affected (4 min 2.46 sec) mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)

You can override this behavior by specifying that the write lock should be a lower priority, with the LOW_PRIORITY keyword. If you run the previous example again with a low-priority request for a write lock, the earlier read lock will be obtained first.

First, place the write lock on Window1:

mysql> LOCK TABLE customer WRITE; Query OK, 0 rows affected (0.00 sec)

Next attempt a read lock from Window2:

mysql> LOCK TABLE customer READ; 

And a low priority write lock from Window3:

mysql> LOCK TABLE customer LOW_PRIORITY WRITE; 

Now release the lock from Window1:

mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)

This time Window2 is first to obtaining its lock (no need to retype the LOCK statement):

mysql> LOCK TABLE customer READ; Query OK, 0 rows affected (20.88 sec) 
mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)

And finally the write lock from Window3 is obtained (no need to retype):

mysql> LOCK TABLE customer LOW_PRIORITY WRITE; Query OK, 0 rows affected (1 min 25.94 sec)

Again, release the lock so that you can use the table later:

mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) 
Warning 

The LOCK TABLES statement is not transaction safe. It will commit all active transactions before it attempts to lock the tables.

Table locks are mostly used in this way on tables that do not support transactions. If you're using an InnoDB or a BDB table, use BEGIN and COMMIT instead to avoid anomalies in your data. The following is an example of where it could be used. If your customer_ sales_values table is empty, populate it with some records:

mysql> INSERT INTO customer_sales_values(first_name, surname, value,  value2) VALUES('Johnny', 'Chaka-Chaka', 500, NULL), ('Patricia',  'Mankunku', 450, NULL), ('Winston', 'Powers', 750, NULL), ('Yvonne',  'Clegg', 5800, NULL), ('Charles', 'Dube', 0, NULL), ('Charles',  'Dube', 0, NULL), ('Gladys', 'Malherbe', 5, 10); 

Now assume that Johnny Chaka-Chaka has made two sales, both of which are being processed by different clerks. The one sale is worth $100 and the other $300. Both clerks go through a process of reading the existing value, then adding either 100 or 300 to this, and then updating the record. The problem comes if both perform the SELECT before either is updated. Then the one update will overwrite the other, and the one value will be lost, as follows.

First, perform the query on Window1:

mysql> SELECT value from customer_sales_values WHERE  first_name='Johnny' and surname='Chaka-Chaka'; +-------+ | value | +-------+ |   500 | +-------+

Then perform the query on Window2:

mysql> SELECT value from customer_sales_values WHERE  first_name='Johnny' and surname='Chaka-Chaka'; +-------+ | value | +-------+ |   500 | +-------+

This is Window1:

mysql> UPDATE customer_sales_values SET value=500+100 WHERE  first_name='Johnny' and surname='Chaka-Chaka'; Query OK, 1 row affected (0.01 sec) 

This is Window2:

mysql> UPDATE customer_sales_values SET value=500+300 WHERE  first_name='Johnny' and surname='Chaka-Chaka'; Query OK, 1 row affected (0.01 sec)

After both sales have been captured, the total value of Johnny's sales is $800, which is $100 short! If you'd taken the care to lock the table, you'd have avoided the problem.

After you reset the data and start again, run the following UPDATE:

mysql> UPDATE customer_sales_values SET value=500 WHERE  first_name='Johnny' and surname='Chaka-Chaka'; Query OK, 1 row affected (0.00 sec)

Now, place a write lock with Window1:

mysql> LOCK TABLE customer_sales_values WRITE; mysql> SELECT value from customer_sales_values WHERE  first_name='Johnny' and surname='Chaka-Chaka'; +-------+ | value | +-------+ |   500 | +-------+

Window2 attempts to place a write lock as well:

mysql> LOCK TABLE customer_sales_values WRITE; 

The lock is not obtained, as Window1 has obtained a write lock already. Now Window1 can update the record, before releasing the lock and allowing Window2 to continue.

Run the following UPDATE statement onWindow1, and release the lock:

mysql> UPDATE customer_sales_values SET value=500+100 WHERE  first_name='Johnny' and surname='Chaka-Chaka'; Query OK, 1 row affected (0.00 sec) mysql> UNLOCK TABLES; 

Window2 obtains the lock (no need to retype), and can complete the rest of the transaction as follows:

mysql> LOCK TABLE customer_sales_values WRITE; Query OK, 0 rows affected (1 min 35.87 sec) mysql> SELECT value from customer_sales_values WHERE  first_name='Johnny' and surname='Chaka-Chaka'; +-------+ | value | +-------+ |   600 | +-------+ 1 row in set (0.00 sec) mysql> UPDATE customer_sales_values SET value=600+300 WHERE  first_name='Johnny' and surname='Chaka-Chaka'; Query OK, 1 row affected (0.01 sec) mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) 

Johnny receives credit due to him; the table correctly reflects the $900 worth of sales he has made.

Avoiding Table Locks

You should avoid table locks as much as possible in high-update volume tables, as, in the case of a write lock, no records from the table can be read or written for the duration of the lock. And because write locks have by default a higher priority than a read locks, no records can be read until all updates or inserts are complete, potentially causing MySQL to jam up horribly. There are ways to avoid table locks, though. One way is to perform the read and the update in the same statement (called an incremental update).

Run the following incremental update from Window1:

mysql> UPDATE customer_sales_values SET value=value+300 WHERE  first_name='Johnny' and surname='Chaka-Chaka'; 

Window2 can perform its UPDATE as well:

mysql> UPDATE customer_sales_values SET value=value+100 WHERE  first_name='Johnny' and surname='Chaka-Chaka'; 

Now, no matter which order the statements are placed, the update will always be performed on the most recent value.

Transaction Levels

You can change the default behavior when dealing with transactions by setting the transaction level. There are a number of transaction levels in MySQL. MySQL supports the following transaction isolation levels:

READ UNCOMMITTED  This level allows transactions to read the uncommitted data from other transactions (called a dirty read ).

READ COMMITTED  This level does not allow dirty reads.

REPEATABLE READ  This level does not allow nonrepeatable reads (which are when the data has been changed by another transaction, even if it has been committed).

SERIALIZABLE  This level does not allow phantom reads, which is when another transaction has committed a new row that matches the results of your query. The data will be the same each time.

To change the transaction level, use the following syntax:

SET [scope] TRANSACTION ISOLATION LEVEL { isolation_level } 

The scope option can be either GLOBAL or SESSION. This option overrides the usual scope of the statement, which is to set the transaction isolation level for the next transaction to be started. GLOBAL sets the level for all new transactions, and SESSION for all new transactions on that thread. The isolation_level option is one of the four transaction isolation levels listed above.



Mastering MySQL 4
Mastering MySQL 4
ISBN: 0782141625
EAN: 2147483647
Year: 2003
Pages: 230
Authors: Ian Gilfillan

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