MySQL Transactional and Locking Statements

MySQL5.0 supports local transactions by statements like START TRANSACTION, SET AUTOCOMMIT, COMMIT and ROLLBACK. Transaction means a group of SQL Statements, which executes as a unit. And MySQL either executes all the statement successfully or it doesn?t execute anyone. This can be achieved by the commit and rollback. When all the statements executes successfully then you can commit it to effect the database permanently. But if any error has occurred then you can roll it back for cancellation it.

START TRANSACTION, COMMIT and ROLLBACK

The general syntax of Start Transaction, commit and rollback is:

START TRANSACTION | BEGIN [WORK] COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] SET AUTOCOMMIT = {0 | 1}

START TRANSACTION and BEGIN statement is used start a new transaction. And COMMIT is used to commit (saved) the current transaction means that makes the changes permanently. ROLLBACK is used to rolls back the current transaction means canceling the changes. SET AUTOCOMMIT statement is used to disable or enable the default autocommit mode for current transaction.

The optional keyword WORK is used to support for COMMIT and ROLLBACK. And CHAIN and RELEASE keyword are used to additional control over the completion of transaction. The AND CHAIN clause is used to start a new transaction when the current one ends. And this new one has the same isolation level. The RELEASE clause is used for disconnecting the server with current client connection after terminating the current transaction. By default MySQL in autocommit mode. When we are using a transaction safe storage engine like BDB, InnoDB then we can disable the autocommit mode by using the following statement :

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

By using above statement we can disable the autocommit mode, then we must use the COMMIT to store the changes in disk. And if we want to ignore the changes then we can use ROLLBACK. For disabling the autocommit mode to a single series of statement then we can use the START TRANSACTION statement. Example :

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

mysql> UPDATE Emp SET Perks=Perks*1.03 WHERE Salary>15000;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

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

Statements that can not be Rolled Back

In MySQL some statements are available that we cannot be rolled back these include DDL (Data Definition Language) statements like create database, create table, drop database, drop table, alter table. Make sure you transaction does not include these type of statements. If you have issued a DDL statement early in transaction and another statement fails then you cannot the rolled back the full effect of the transaction by issuing the ROLLBACK statement.

SAVEPOINT and ROLLBACK TO SAVEPOINT

The general syntax of SAVEPOINT and ROLLBACK TO SAVEPOINT is :
SAVEPOINT savepoint_name
ROLLBACK [WORK] TO SAVEPOINT savepoint_name
RELEASE SAVEPOINT savepoint_name

SAVEPOINT statement is used to set a named transaction savepoint with any name. The ROLLBACK TO SAVEPOINT statement is used to roll back the transaction for the named savepoint. Means modification to the rows that we made in the current transaction after the savepoint, we are rolled back these modification. We can remove the named savepoint from the set of savepoints of the current transaction by using RELEASE SAVEPOINT. Example :

mysql> SELECT * FROM Emp;
+-----+---------+----------+-------------------+--------+-------+
| Eid | Ename   | City     | Designation       | Salary | Perks |
+-----+---------+----------+-------------------+--------+-------+
| 1   | Rahul   | Delhi    | Manager           | 10300  | 879   |
| 2   | Gaurav  | Mumbai   | Assistant Manager | 10300  | 879   |
| 3   | Chandan | Banglore | Team Leader       | 15450  | 1060  |
| 5   | Tapan   | Pune     | Developer         | 20600  | 1178  |
| 6   | Amar    | Chennai  | Developer         | 16000  | 1193  |
| 7   | Santosh | Delhi    | Designer          | 10000  | 891   |
| 8   | Suman   | Pune     | Web Designer      | 20000  | 698   |
+-----+---------+----------+-------------------+--------+-------+
7 rows in set (0.46 sec)

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

mysql> UPDATE Emp
    -> SET Salary=Salary*1.03 WHERE Salary>=10000;
Query OK, 7 rows affected (0.10 sec)
Rows matched: 7  Changed: 7  Warnings: 0

mysql> SAVEPOINT sve_point;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO Emp VALUES(10,'Chandan','Delhi','Designer',20000,965);
Query OK, 1 row affected (0.03 sec)

mysql> ROLLBACK TO SAVEPOINT sve_point;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO Emp VALUES(9,'Rajesh','Delhi','Developer',15000,965);
Query OK, 1 row affected (0.00 sec)

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

mysql> SELECT * FROM Emp;
+-----+---------+----------+-------------------+--------+-------+
| Eid | Ename   | City     | Designation       | Salary | Perks |
+-----+---------+----------+-------------------+--------+-------+
| 1   | Rahul   | Delhi    | Manager           | 10609  | 879   |
| 2   | Gaurav  | Mumbai   | Assistant Manager | 10609  | 879   |
| 3   | Chandan | Banglore | Team Leader       | 15914  | 1060  |
| 5   | Tapan   | Pune     | Developer         | 21218  | 1178  |
| 6   | Amar    | Chennai  | Developer         | 16480  | 1193  |
| 7   | Santosh | Delhi    | Designer          | 10300  | 891   |
| 8   | Suman   | Pune     | Web Designer      | 20600  | 698   |
| 9   | Rajesh  | Delhi    | Developer         | 15000  | 965   |
+-----+---------+----------+-------------------+--------+-------+
8 rows in set (0.02 sec)

LOCK TABLES and UNLOCK TABLES

The general syntax for locking and unlocking the tables is:
LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ... UNLOCK TABLES

The LOCK TABLES are used for locking the base table to current thread. If any other thread locked the table then this table is blocked until the all locks can be acquired. The UNLOCK TABLES is used to explicitly release the lock that?s held by the current thread. The current thread implicitly unlocks all tables if it issues another LOCK TABLES or if the connection to the server is closed. After getting a global read lock we can also used the UNLOCK TABLE with FLUSH TABLES WITH READ LOCK for releasing the lock. But for using the LOCK TABLES you need the LOCK TABLES and SELECT privileges for involved tables.

A table locking is used to protect only against inappropriate writes or read by other clients. If any client holds a lock even a read lock then the client can perform the operation of table level like DROP TABLE. But truncate operations cannot be performed because they are not transaction safe.

Uses of LOCK TABLES with transactional tables:

As we discussed LOCK TABLES is not transaction safe and implicitly performs a commit operation on any active transactions before performing to lock the table. And beginning a transaction implicitly performs an UNLOCK TABLES.
For using LOCK TABLES with transactional tables like InnoDB, we have to set AUTOCOMMIT=0 and we don?t call UNLOCK TABLES until we commit the transactional explicitly. If we are calling LOCK TABLES then InnoDB internally takes it own table lock and MySQL is also takes its own table lock. At the next commit InnoDB released its table lock but for releasing MySQL we have to call UNLOCK TABLES. But if we do AUTOCOMMIT =1 then InnoDB released the table lock immediately after calling of LOCK TABLES that?s why deadlocks can easily occur.
ROLLBACK cannot release the MySQL non transactional table locks.
FLUSH TABLES WITH READ LOCK is used to get the global lock rather than table lock.

When we are using LOCK TABLES then we have to lock all the tables which we are using in our statements. LOCK TABLES cannot lock the views that?s why if we are using any view then we have to lock all the base tables on which these views are depend. When we obtained the locks with LOCK TABLES statement then we cannot access any tables which are not locked. But if you using aliases in that case you must obtain a lock for each alias separately. Example :

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

mysql> INSERT INTO Emp VALUES(9,'Rajesh','Delhi','Developer',15000,965);
ERROR 1100 (HY000): Table 'Emp' was not locked with LOCK TABLES
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO Emp VALUES(9,'Rajesh','Delhi','Developer',15000,965);
Query OK, 1 row affected (0.04 sec)

mysql> LOCK TABLE Emp AS ee READ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM Emp;
ERROR 1100 (HY000): Table 'Emp' was not locked with LOCK TABLES
mysql> SELECT * FROM Employee;
ERROR 1100 (HY000): Table 'Employee' was not locked with LOCK TABLES
mysql> SELECT * FROM Emp AS ee;
+-----+---------+----------+-------------------+--------+-------+
| Eid | Ename   | City     | Designation       | Salary | Perks |
+-----+---------+----------+-------------------+--------+-------+
| 1   | Rahul   | Delhi    | Manager           | 10927  | 879   |
| 2   | Gaurav  | Mumbai   | Assistant Manager | 10927  | 879   |
| 3   | Chandan | Banglore | Team Leader       | 16391  | 1060  |
| 5   | Tapan   | Pune     | Developer         | 21855  | 1178  |
| 6   | Amar    | Chennai  | Developer         | 16974  | 1193  |
| 7   | Santosh | Delhi    | Designer          | 10609  | 891   |
| 8   | Suman   | Pune     | Web Designer      | 21218  | 698   |
| 9   | Rajesh  | Delhi    | Developer         | 15000  | 965   |
+-----+---------+----------+-------------------+--------+-------+
8 rows in set (0.00 sec)

WRITE locks have higher priority to READ locks that means updates are processed as soon as possible. Means if any thread get a READ LOCK and another thread request for a WRITE lock then following READ lock requests have to wait until the WRITE thread has released the lock. For obtaining a READ lock to other threads we can use LOW_PRIORITY WRITE locks.

Table locking is deadlock free. You have to be aware if you are using a LOW_PRIORITY WRITE lock , that means only MySQL is waiting for thi particular lock until there no threads are available which want a READ lock. Any thread has obtained the WRITE lock and its waiting to obtain a lock for next table in lock table list then all other threads have to wait for WRITE lock to be released.

If we wish to terminate a thread which is waiting for a table lock then we can use KILL statement. The general syntax of KILL statement is:
KILL [CONNECTION | QUERY] thread_id

Each connection for mysqld running in a separate thread. By SHOW PROCESSLIST statement we can see which threads are running and we can kill the thread by KILL statement. KILL CONNECTION is same as the KILL statement with no modifier. It is used to terminate a connection associated with the given thread_id. KILL QUERY is used to terminate the statement that connection is currently executing but it leaves the connection itself intact. For getting the list of all threads you need the PROCESS privilege and for kill the all threads and statements then you need the SUPER privilege else you can see and kill only your threads and statements. Example :

mysql> SHOW PROCESSLIST \G;
*************************** 1. row ***************************
     Id: 9
   User: root
   Host: localhost:1786
     db: employee
Command: Query
   Time: 0
  State: NULL
   Info: SHOW PROCESSLIST
1 row in set (0.00 sec)

mysql> KILL 9;
ERROR 2013 (HY000): Lost connection to MySQL server during query

If you are using the INSERT DELAYED statement to any table then you don't have to lock that table because here the INSERT statement is performed by a separate thread.

SET TRANSACTION

The general syntax of SET TRANSACTION is:
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

SET TRANSACTION is used to set the transaction isolation level for the global or the current session or for the next transaction. When we are using GLOBAL keyword then it sets the default transaction level global but you require the SUPER privilege for doing this. And SESSION keyword is used to set the default transaction level for all future transactions which performed on current connection.

REPEATABLE READ ? It is the default isolation level for InnoDB tables. In this, all reads are consistent within a transaction.
READ UNCOMMITTED ? It is used, where in one transaction queries are affected by uncommitted changes in another transaction.
READ COMMITTED ? If we are using this setting then committed updates are visible in another transaction also.
SERIALIZABLE ? In this setting updates are not allowed in other transactions.

REPEATABLE READ Example :

Connection 1
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL
    -> REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

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

mysql> SELECT * FROM Emp;
+-----+-------+-------+-------------+--------+-------+
| Eid | Ename | City  | Designation | Salary | Perks |
+-----+-------+-------+-------------+--------+-------+
| 1   | Rahul | Delhi | Manager     | 10927  | 879   |
+-----+-------+-------+-------------+--------+-------+
1 row in set (0.00 sec)

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

mysql> INSERT INTO Emp VALUES(2,'Suman','Mumbai','Designer',20000,865);
Query OK, 1 row affected (0.00 sec)

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

mysql> SELECT * FROM Emp;
+-----+-------+--------+-------------+--------+-------+
| Eid | Ename | City   | Designation | Salary | Perks |
+-----+-------+--------+-------------+--------+-------+
| 1   | Rahul | Delhi  | Manager     | 10927  | 879   |
| 2   | Suman | Mumbai | Designer    | 20000  | 865   |
+-----+-------+--------+-------------+--------+-------+
2 rows in set (0.00 sec)

Connection 1
mysql> SELECT * FROM Emp;
+-----+-------+-------+-------------+--------+-------+
| Eid | Ename | City  | Designation | Salary | Perks |
+-----+-------+-------+-------------+--------+-------+
| 1   | Rahul | Delhi | Manager     | 10927  | 879   |
+-----+-------+-------+-------------+--------+-------+
1 row in set (0.00 sec)

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

mysql> SELECT * FROM Emp;
+-----+-------+--------+-------------+--------+-------+
| Eid | Ename | City   | Designation | Salary | Perks |
+-----+-------+--------+-------------+--------+-------+
| 1   | Rahul | Delhi  | Manager     | 10927  | 879   |
| 2   | Suman | Mumbai | Designer    | 20000  | 865   |
+-----+-------+--------+-------------+--------+-------+
2 rows in set (0.01 sec)

READ UNCOMMITTED Example :

Connection 1
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL
    -> READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

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

mysql> INSERT INTO Emp VALUES(3,'Chandan','Delhi','G Manager',30000,999);
Query OK, 1 row affected (0.00 sec)

Connection 2
mysql> SELECT * FROM Emp;
+-----+-------+--------+-------------+--------+-------+
| Eid | Ename | City   | Designation | Salary | Perks |
+-----+-------+--------+-------------+--------+-------+
| 1   | Rahul | Delhi  | Manager     | 10927  | 879   |
| 2   | Suman | Mumbai | Designer    | 20000  | 865   |
+-----+-------+--------+-------------+--------+-------+
2 rows in set (0.01 sec)

Connection 1
mysql> ROLLBACK;
Query OK, 0 rows affected (0.08 sec)

Connection 2
mysql> SELECT * FROM Emp;
+-----+-------+--------+-------------+--------+-------+
| Eid | Ename | City   | Designation | Salary | Perks |
+-----+-------+--------+-------------+--------+-------+
| 1   | Rahul | Delhi  | Manager     | 10927  | 879   |
| 2   | Suman | Mumbai | Designer    | 20000  | 865   |
+-----+-------+--------+-------------+--------+-------+
2 rows in set (0.01 sec)

READ COMMITTED Example :

Connection 1
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL
    -> READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> INSERT INTO Emp VALUES(3,'Chandan','Delhi','G Manager',25000,986);
Query OK, 1 row affected (0.00 sec)

Connection 1
mysql> SELECT * FROM Emp;
+-----+-------+--------+-------------+--------+-------+
| Eid | Ename | City   | Designation | Salary | Perks |
+-----+-------+--------+-------------+--------+-------+
| 1   | Rahul | Delhi  | Manager     | 10927  | 879   |
| 2   | Suman | Mumbai | Designer    | 20000  | 865   |
+-----+-------+--------+-------------+--------+-------+
2 rows in set (0.00 sec)

Connection 2
mysql> COMMIT;
Query OK, 0 rows affected (0.03 sec)

Connection 1
mysql> SELECT * FROM Emp;
+-----+---------+--------+-------------+--------+-------+
| Eid | Ename   | City   | Designation | Salary | Perks |
+-----+---------+--------+-------------+--------+-------+
| 1   | Rahul   | Delhi  | Manager     | 10927  | 879   |
| 2   | Suman   | Mumbai | Designer    | 20000  | 865   |
| 3   | Chandan | Delhi  | G Manager   | 25000  | 986   |
+-----+---------+--------+-------------+--------+-------+
3 rows in set (0.00 sec)

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

SERIALIZABLE Example :

Connection 1
mysql> SET TRANSACTION ISOLATION LEVEL
    -> SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

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

mysql> SELECT * FROM Emp;
+-----+---------+--------+-------------+--------+-------+
| Eid | Ename   | City   | Designation | Salary | Perks |
+-----+---------+--------+-------------+--------+-------+
| 1   | Rahul   | Delhi  | Manager     | 10927  | 879   |
| 2   | Suman   | Mumbai | Designer    | 20000  | 865   |
| 3   | Chandan | Delhi  | G Manager   | 25000  | 986   |
+-----+---------+--------+-------------+--------+-------+
3 rows in set (0.00 sec)

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

mysql> UPDATE Emp SET Salary=Salary*1.03;

In the first connection we executes the SELECT statement that?s why the UPDATE is locked.

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

Connection 2
Query OK, 3 rows affected (9.12 sec)
Rows matched: 3  Changed: 3  Warnings: 0

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

mysql> SELECT * FROM Emp;
+-----+---------+--------+-------------+--------+-------+
| Eid | Ename   | City   | Designation | Salary | Perks |
+-----+---------+--------+-------------+--------+-------+
| 1   | Rahul   | Delhi  | Manager     | 11255  | 879   |
| 2   | Suman   | Mumbai | Designer    | 20600  | 865   |
| 3   | Chandan | Delhi  | G Manager   | 25750  | 986   |
+-----+---------+--------+-------------+--------+-------+
3 rows in set (0.00 sec)

MySQL Transactional and Locking Statements

MySQL Transactional and Locking Statements

MySQL Transactional and Locking Statements

Tutorials