Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

MySQL FAQs - Transaction Management: Commit or Rollback

By: FYIcenter.com

Part:   1  2  3  4  5  6   7  8  9  10 

(Continued from previous part...)

What Happens to the Current Transaction If the Session Is Killed?

If a session is killed by the DBA, the current transaction in that session will be rolled back and ended. All the database changes made in the current transaction will be removed. This is called an implicit rollback when session is killed. The following tutorial exercise shows you that the KILL command forces the current transaction to be rolled back with all the changes:

>\mysql\bin\mysql -u dev -piyf fyi

mysql> DELETE FROM fyi_links where id = 112;
Query OK, 1 row affected (0.07 sec)

mysql> DELETE FROM fyi_links where id = 113;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM fyi_links;
+-----+---------------+-------+--------+--------------------
| id  | url           | notes | counts | created           
+-----+---------------+-------+--------+--------------------
| 101 | fyicenter.com | Good  |    999 | 2006-07-01 20:34:10
| 110 | centerfyi.com | Wrong |      0 | 2006-07-01 20:34:12
+-----+---------------+-------+--------+--------------------
2 rows in set (0.01 sec)

Keep the "dev" mysql window as is, and open another window to run another instance of mysql:

>\mysql\bin\mysql -u root -pretneciyf

mysql> SHOW PROCESSLIST;
+----+------+----------------+------+---------+------+------
| Id | User | Host           | db   | Command | Time | State
+----+------+----------------+------+---------+------+------
|  3 | dev  | localhost:4723 | fyi  | Sleep   |  171 |     
|  5 | root | localhost:4728 | fyi  | Query   |    0 | NULL
+----+------+----------------+------+---------+------+------
2 rows in set (0.08 sec)

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

Go back to the "dev" mysql window:

mysql> COMMIT;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6
Current database: fyi
Query OK, 0 rows affected (0.21 sec)

mysql> SELECT * FROM fyi_links;
+-----+---------------+-------+--------+--------------------
| id  | url           | notes | counts | created           
+-----+---------------+-------+--------+--------------------
| 101 | fyicenter.com | Good  |    999 | 2006-07-01 20:34:10
| 110 | centerfyi.com | Wrong |      0 | 2006-07-01 20:34:12
| 112 | oracle.com    | NULL  |   NULL | 2006-07-01 20:41:12
| 113 | mysql.com     | NULL  |   NULL | 2006-07-01 20:41:21
+-----+---------------+-------+--------+--------------------
4 rows in set (0.00 sec)

As you can see, two deleted records were rolled back as the session got killed by the DBA.

How Does MySQL Handle Read Consistency?

Read consistency is a concept that describes how consistent the output will be on two subsequent read operations. A read operation is usually a stand alone SELECT statement or a SELECT subquery in a parent statement.

A database server can support up to 3 levels of read consistency:

  • No Read Consistency - Two subsequent read operations will get no consistent output, if other sessions are making changes to the database.
  • Statement-Level Read Consistency - Two subsequent read operations will get consistent output, if they are used as subqueries within a single SQL statement.
  • Transaction-Level Read Consistency - Two subsequent read operations will get consistent output, if they are used as subqueries or SELECT statements within a single transaction.

By default, MySQL server offers the highest level, Transaction-Level Read Consistency, on tables with transaction-safe storage engines, like InnoDB.

What Are Transaction Isolation Levels?

There are 4 transaction isolation levels defined by SQL-1992 standard:

  • READ UNCOMMITTED - The SELECT statements in one transaction will read uncommitted data changes from transactions of all connected sessions. In this level, "dirty read" could happen, where transaction A reads a record of data including changes made by transaction B, but not committed yet.
  • READ COMMITTED - The SELECT statements in one transaction will read only committed data changes from other transactions, and uncommitted data changes from itself. In this level, "dirty read" will not happen. But "non-repeatable read" could happen, where transaction A executes the same query twice and gets two different outputs, because transaction B committed some data changes between the two executions.
  • REPEATABLE READ - Extending the READ COMMITTED level to ensure that same SELECT statement will get the same data on the same records if executed multiple times. Database server will use locks or data snapshots to make this happen. In this level, "dirty read" and "non-repeatable read" will not happen. But "phantom records" could happen, there transaction A executes the same query twice and gets extra records the second time, because transaction B inserted some new records between the two executions.
  • SERIALIZABLE - Extending the REPEATABLE READ level to ensure that "phantom records" will not happen by using locks or data snapshots.

By default, MySQL server offers the REPEATABLE READ level on tables with transaction-safe storage engines, like InnoDB.

(Continued on next part...)

Part:   1  2  3  4  5  6   7  8  9  10 


Selected Developer Jobs:

More...