I have a PHP script that I am trying to test. This script executes hundreds of MySQL queries.
For testing, I have been using XDebug in VSCode to step through the script while using MySQL Workbench to SELECT data on an ad hoc basis to understand the state of the data prior to and immediately after certain queries (but during script execution). This has worked well for me. .......So what's the problem?
I would like to begin using transactions so that I can more easily rollback my DB to its pre-test state after each test is completed. However, I have found that even when I use SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, I am not able to see the uncommitted data using MySql Workbench. This defeats the whole purpose of the test, which is to be able to see the data on an ad hoc basis during the test.
EXAMPLE OF WHAT IS HAPPENING:
My test table accessed by MySQL Workbench prior to code execution:
| id |
|---|
| 21 |
| 42 |
Stepping through my PHP code using XDebug (using PDO for DB access):
$pfConn = (new DbConn('test'))->connect();
$sql = "SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
$stmt = $pfConn->prepare($sql);
$stmt->execute();
$sql = "SELECT @@tx_isolation"; //just confirming the isolation level
$stmt = $pfConn->prepare($sql);
$stmt->execute();
$res = $stmt->fetchColumn(); //$res = "READ_UNCOMMITTED"
$pfConn->beginTransaction();
$sql = "INSERT INTO test (id) VALUES (63)";
$stmt = $pfConn->prepare($sql);
$stmt->execute();
At this point, I access my test table again with MySQL Workbench:
| id |
|---|
| 21 |
| 42 |
Uncommitted value of 63 is not being seen by MySQL Workbench despite READ_UNCOMMITTED.
Now I go ahead and continue stepping through my PHP code to commit the value:
$pfConn->commit();
Now, when I access my table using MySQL Workbench, I see the correctly inserted (and now committed) data:
| id |
|---|
| 21 |
| 42 |
| 63 |
The behavior above is also true for updates, not just inserts.
It should be noted that if I instead use SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, MySQL Workbench IS able to see the uncommitted update. However, this is the opposite direction than I would prefer to go. I would ACTUALLY prefer not even to use something so broad as SESSION but rather only a transaction-specific isolation level. I don't want unintentional "dirty reads" to occur.
If I were to guess, I am thinking that I have a fundamental misunderstanding with regard to how 2 separate connections interact with transaction isolation levels, but my efforts to learn about this by searching have been fruitless hence why I'm asking here.
Also, while I am primarily asking why this is occurring and what, if anything, I can do to make this work, I would also secondarily appreciate it if anyone has any ideas on how to better do this kind of testing.
You're doing it backwards. You need to set tx_isolation to READ UNCOMMITTED on the MySQL Workbench session, not the session in your PHP code.
Here's a demo using the mysql client in two windows:
window 1:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user set name = 'bill', created_at = now();
Query OK, 1 row affected (0.00 sec)
I haven't change the isolation level of this session.
Don't commit that transaction yet.
window 2:
mysql> select * from user;
Empty set (0.00 sec)
As expected. This session can't see uncommitted changes while using the default transaction isolation level.
Now change the isolation level of this client and try again.
mysql> set transaction_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+----+------+---------------------+
| id | name | created_at |
+----+------+---------------------+
| 1 | bill | 2023-02-17 14:50:13 |
+----+------+---------------------+
1 row in set (0.00 sec)
It's the isolation level of the client that wants to query uncommitted data that matters.
I wanted to make a demo using MySQL Workbench, but the current version (8.0.32) seems to crash when I run any query. This has been reported as a bug: https://bugs.mysql.com/bug.php?id=109671
I don't normally use MySQL Workbench. It's so buggy that it's more often a hindrance to work than a help.