Collections:
Change Transaction Isolation Level in MySQL
How To View and Change the Current Transaction Isolation Level in MySQL?
✍: FYIcenter.com
If you want to view or change the current transaction isolation level, you can use the following commands:
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 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.
⇒ What Is a Data Lock in MySQL
⇐ Transaction Isolation Levels in MySQL
2017-08-08, 2741🔥, 0💬
Popular Posts:
What Is the Difference Between GETDATE() and GETUTCDATE() in SQL Server Transact-SQL? The difference...
How To Locate and Take Substrings with CHARINDEX() and SUBSTRING() Functions in SQL Server Transact-...
What Is Program Global Area (PGA) in Oracle? A Program Global Area (PGA) is a memory buffer that is ...
Where to find answers to frequently asked questions on Transaction Management: Commit or Rollback in...
How To Start Instance with a Minimal Initialization Parameter File in Oracle? The sample initializat...