MySQL Transaction Testing
- Configure MySQL transaction settings:
-- Check autocommit status (1=on, 0=off)
SELECT @@autocommit;
-- Disable autocommit
SET autocommit = 0;
- Prepare test data:
CREATE DATABASE transaction_test;
USE transaction_test;
CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(10)) ENGINE=InnoDB;
INSERT INTO users VALUES(1, 'alice'), (2, 'bob'), (3, 'charlie');
COMMIT;
- Transaction isolation levels:
| Isolasion Level |
Dirty Read |
Non-Repeatable Read |
Phantom Read |
| READ UNCOMMITTED |
Yes |
Yes |
Yes |
| READ COMMITTED |
No |
Yes |
Yes |
| REPEATABLE READ |
No |
No |
Yes |
| SERIALIZABLE |
No |
No |
No |
- Test Case 1: Dirty Read (READ UNCOMMITTED)
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Transaction A:
START TRANSACTION;
UPDATE users SET name = 'dave' WHERE id = 1;
-- Transaction B can see uncommitted changes
- Test Case 2: Non-Repaetable Read (READ COMMITTED)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Transaction A:
START TRANSACTION;
UPDATE users SET name = 'eve' WHERE id = 1;
-- Transaction B won't see changes until commit
- Test Case 3: Phantom Read (REPEATABLE READ)
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Transaction A:
START TRANSACTION;
INSERT INTO users VALUES(4, 'frank');
-- Transaction B won't see new records until commit