Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted Jul 20, 2004

Transactions in MySQL - Page 2

By Ian Gilfillan

Read locks for Updating

Sometimes, the default consistent read is not what you want. There are cases where we would want to read a record in order to update it, knowing that we are not conflicting with any other connection that is doing the same thing. For example, two connections read a record, in order to insert a new value incremented by one from the current maximum.

Connection 1:

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

mysql> SELECT MAX(f) FROM t;
+--------+
| MAX(f) |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

mysql> INSERT INTO t(f) VALUES (4);
Query OK, 1 row affected (0.00 sec)

Connection 2:

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

mysql> SELECT MAX(f) FROM t;
+--------+
| MAX(f) |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

mysql> INSERT INTO t(f) VALUES(4);
Query OK, 1 row affected (0.00 sec)

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

Connection 1:

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t; 
+------+
| f    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    4 |
+------+
5 rows in set (0.00 sec)

The result is that we have two records with the value 4, where we wanted to have one with 4, one with 5. We can overcome this by creating an update lock. This specifies that no other connections can read that data until the transaction is complete. Here is an update lock in action. First, we delete the erroneous record:

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

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

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

mysql> SELECT MAX(f) FROM t FOR UPDATE;
+--------+
| MAX(f) |
+--------+
|      3 |
+--------+
1 row in set (0.01 sec)

mysql> INSERT INTO t(f) VALUES (4);
Query OK, 1 row affected (0.00 sec)

Connection 2:

mysql> SELECT MAX(f) FROM t FOR UPDATE;

No results are returned - MySQL is waiting until the active transaction is complete, and only then will it return the results Connection 1:

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

The results are now returned to connection 2. Note that the read may have timed out if you have taken too long:

Connection 2:

mysql> SELECT MAX(f) FROM t FOR UPDATE;
+--------+
| MAX(f) |
+--------+
|      4 |
+--------+
1 row in set (4.23 sec)

mysql> INSERT INTO t(f) VALUES(5);
Query OK, 1 row affected (0.00 sec)

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

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

Read locks for sharing

Another kind of lock ensures you always read the latest data, but is not part of the transaction wanting to change the data itself. This is the LOCK IN SHARE MODE. It will stop any updates or deletes of the row being read, and if the latest data is still uncommitted, will wait until that transaction is committed before returning any results. Here's an example:

Connection 1:

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

mysql> SELECT MAX(f) FROM t LOCK IN SHARE MODE;
+--------+
| MAX(f) |
+--------+
|      5 |
+--------+
1 row in set (0.00 sec)

Meanwhile, a second connection attempts to perform an update:

Connection 2:

mysql> UPDATE t SET f = 55 WHERE f=5;

The update waits until the lock from the other connection is released.

Connection 1:

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

Connection 2:

mysql> UPDATE t SET f = 55 WHERE f=5;
Query OK, 0 rows affected (6.95 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE t SET f = 55 WHERE f=5;
Query OK, 1 row affected (43.30 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|   55 |
+------+
5 rows in set (0.00 sec)

BDB tables handle transactions slightly differently, and the default MyISAM tables only in a very limited way (table locks only, as opposed to read locks), but if you are using transactions, you are most likely to be using InnoDB tables. If there's enough demand, I will write a follow up article on transactions in these table types, but hopefully you have enough to keep you busy in the meantime. Good luck!

» See All Articles by Columnist Ian Gilfillan



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date