Home >> FAQs/Tutorials >> MySQL Tutorials

MySQL Tutorial - Change Transaction Isolation Level

By: FYIcenter.com

(Continued from previous topic...)

How To View and Change the Current Transaction Isolation Level?

If you want to view or change the current transaction isolation level, you can use the following commands:

  • SELECT @@TX_ISOLATION FROM DUAL; -- Viewing the current transaction isolation level.
  • SET TRANSACTION ISOLATION LEVEL levelName; -- Changing the current transaction isolation level.

The tutorial exercise below shows you how to view and change transaction isolation level:

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

mysql> SELECT @@TX_ISOLATION FROM DUAL;
+-----------------+
| @@TX_ISOLATION  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.46 sec)

mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.10 sec)

mysql> SELECT @@TX_ISOLATION FROM DUAL;
+----------------+
| @@TX_ISOLATION |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.02 sec)

How To Test Transaction Isolation Levels?

If you want to test transaction isolation levels, you need to make sure that:

  • The tables are created with transaction-safe storage engines, like InnoDB.
  • Multiple-statement transactions are started with "START TRANSACTION" commands.
  • "SET TRANSACTION ISOLATION LEVEL" commands are used to set the right isolation level.

The tutorial exercise below shows you a good example of testing the REPEATABLE-READ transaction isolation level:

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

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

mysql> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows 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
| 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)

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

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

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

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

Go back to the first "dev" mysql window.

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)

mysql> COMMIT;
Query OK, 0 rows 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.00 sec)

As you can see that two records were deleted from the second session between two executions of the same SELECT statement in the first session. The deleted records were still showing up in the output until the transaction was ended with the COMMIT statement.

(Continued on next topic...)

  1. What Is a Transaction?
  2. How To Start a New Transaction?
  3. How To End the Current Transaction?
  4. How To Create a Table for Transaction Testing?
  5. How To Switch between Autocommit-On and Autocommit-Off Modes?
  6. How To Find Out the Current Transaction Mode?
  7. How To Start a New Transaction Explicitly?
  8. How To Commit the Current Transaction?
  9. How To Rollback the Current Transaction?
  10. What Happens to the Current Transaction If a START TRANSACTION Is Executed?
  11. What Happens to the Current Transaction If a DDL Statement Is Executed?
  12. What Happens to the Current Transaction If the Session Is Ended?
  13. What Happens to the Current Transaction If the Session Is Killed?
  14. How Does MySQL Handle Read Consistency?
  15. What Are Transaction Isolation Levels?
  16. How To View and Change the Current Transaction Isolation Level?
  17. What Is a Data Lock?
  18. How To Experiment Data Locks?
  19. How Long a Transaction Will Wait for a Data Lock?
  20. What Happens to Your Transactions When ERROR 1205 Occurred?
  21. What Is a Dead Lock?
  22. How To Experiment Dead Locks?
  23. What Happens to Your Transactions When ERROR 1213 Occurred?
  24. What Are Impacts on Applications from Locks, Timeouts, and DeadLocks?

MySQL Tutorials:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...