# Transaction Isolation Levels in TiDB ## Overview + In these exercises, you will experience two different transaction isolation levels in TiDB. ### Notes + Some of the steps require that you type commands in two different terminal windows. You might find it convenient to launch two terminals and keep them open for the duration of the exercises. + Your environment may be different with these exercises, including but not limited to `IP`, `port`, `username`, `password` and `directory`. + The `$` in your terminal window is an OS prompt. + The "`tidb>`" or "`mysql>`" in your terminal window is SQL command prompt. + These exercises can be run on a single machine, such as your laptop. ### Assumptions + You are using macOS or Linux. + You have cloned the [lab repository](https://github.com/pingcap/tidb-course-201-lab.git). + You have completed the Exercises - Creating a TiDB Cluster for Testing Purposes. ## Exercise 1: Isolation Level - `REPEATABLE-READ` ### Overview + In this exercise, you experience the `REPEATABLE-READ` isolation level. ### Duration + This exercise should take you approximately 10 minutes to complete. ### Tasks 1. Create two database sessions and ensure that the system variable `transaction_isolation` is configured to `REPEATABLE-READ`. 2. Attempt to read data from an active transaction and a committed transaction from different sessions. ### Solution 1. Connect to TiDB Playground Cluster TiDB server by executing `connect-4000.sh` or `connect-4001.sh`. ``` $ cd tidb-course-201-lab/scripts $ ./connect-4000.sh ... tidb:4000> ``` 2. Change the prompt to `Session A ` (Note that there is a trailing whitespace). ``` \R Session A> ``` ``` tidb:4000> \R Session A> PROMPT set to 'Session A> ' Session A> ``` 3. Open a new terminal, connect to the TiDB server by executing `connect-4000.sh` or `connect-4001.sh`. ``` ./connect-4001.sh ... tidb:4001> ``` 4. Change the prompt to `Session B ` (Note that there is a trailing whitespace). ``` \R Session B> ``` ``` tidb:4001> \R Session B> PROMPT set to 'Session B> ' Session B> ``` 5. In BOTH terminals, verify that the system variable `transaction_isolation` is `REPEATABLE-READ`. ```sql SHOW SESSION VARIABLES LIKE 'transaction_isolation'; ``` ``` Session A> SHOW SESSION VARIABLES LIKE 'transaction_isolation'; -------------- SHOW SESSION VARIABLES LIKE 'transaction_isolation' -------------- +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.01 sec) ``` ``` Session B> SHOW SESSION VARIABLES LIKE 'transaction_isolation'; -------------- SHOW SESSION VARIABLES LIKE 'transaction_isolation' -------------- +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.01 sec) ``` 6. In Session A, create a table named `test.ilevel`, and insert 2 rows into it. ```sql DROP TABLE IF EXISTS test.ilevel; CREATE TABLE test.ilevel (pk INT PRIMARY KEY, name CHAR(10)); INSERT INTO test.ilevel VALUES (1, 'Moon Light'), (2, 'Sun Light'); ``` ``` ... Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 ``` 7. Stay in Session A, begin a new transaction, and then query table `test.ilevel`. ```sql BEGIN; SELECT * FROM test.ilevel; ``` ``` Session A> BEGIN; -------------- BEGIN -------------- Query OK, 0 rows affected (0.00 sec) +----+------------+ | pk | name | +----+------------+ | 1 | Moon Light | | 2 | Sun Light | +----+------------+ 2 rows in set (0.00 sec) ``` 8. Change to Session B (the other terminal), modify the name from `Moon Light` to `Mars Light` for row number 1. ```sql BEGIN; UPDATE test.ilevel SET name='Mars Light' WHERE pk=1; COMMIT; SELECT * FROM test.ilevel; ``` ``` ... +----+------------+ | pk | name | +----+------------+ | 1 | Mars Light | | 2 | Sun Light | +----+------------+ 2 rows in set (0.01 sec) ``` 9. Switch to Session A, run the following query and observe the result. ```sql SELECT * FROM test.ilevel; ``` ``` ... +----+------------+ | pk | name | +----+------------+ | 1 | Moon Light | | 2 | Sun Light | +----+------------+ 2 rows in set (0.00 sec) ``` 10. Stay in Session A, end the transaction (COMMIT or ROLLBACK), and re-run the same query in step 9 again to view the committed data. ```sql COMMIT; SELECT * FROM test.ilevel; ``` ``` ... +----+------------+ | pk | name | +----+------------+ | 1 | Mars Light | | 2 | Sun Light | +----+------------+ 2 rows in set (0.00 sec) ``` 11. Keep both sessions connected. ### Summary In step 7 and step 9, you experienced a typical situation of `REPEATABLE-READ`. ## Exercise 2: Isolation Level - `READ-COMMITTED` ### Overview + In this exercise, you experience the `READ-COMMITTED` isolation level. ### Duration + This exercise should take you approximately 5 minutes to complete. ### Tasks 1. Create two database sessions and ensure that the system variable `transaction_isolation` is configured to `READ-COMMITTED`. 2. Attempt to read data from an active transaction and a committed transaction from different sessions. ### Solution 1. In BOTH terminals, modify the system variable `transaction_isolation` to `READ-COMMITTED`. ```sql SET SESSION transaction_isolation='READ-COMMITTED'; ``` ``` Session A> SET SESSION transaction_isolation='READ-COMMITTED'; -------------- SET SESSION transaction_isolation='READ-COMMITTED' -------------- Query OK, 0 rows affected (0.01 sec) ``` ``` Session B> SET SESSION transaction_isolation='READ-COMMITTED'; -------------- SET SESSION transaction_isolation='READ-COMMITTED' -------------- Query OK, 0 rows affected (0.01 sec) ``` 2. In Session A, begin a new transaction, and then query table `test.ilevel`. ```sql BEGIN; SELECT * FROM test.ilevel; ``` ``` Session A> BEGIN; -------------- BEGIN -------------- Query OK, 0 rows affected (0.00 sec) Session A> SELECT * FROM test.ilevel; -------------- SELECT * FROM test.ilevel -------------- +----+------------+ | pk | name | +----+------------+ | 1 | Mars Light | | 2 | Sun Light | +----+------------+ 2 rows in set (0.00 sec) ``` 3. Change to Session B (the other terminal), modify the name from `Mars Light` to `Moon Light` for row number 1. ```sql BEGIN; UPDATE test.ilevel SET name='Moon Light' WHERE pk=1; COMMIT; SELECT * FROM test.ilevel; ``` ``` ... +----+------------+ | pk | name | +----+------------+ | 1 | Moon Light | | 2 | Sun Light | +----+------------+ 2 rows in set (0.01 sec) ``` 4. Switch to Session A, run the following query and observe the result. Note that Session A is still in the active transaction started from step 2. Because of the `READ-COMMITTED` isolation level, Session A can read the committed (changed, aka non-repeatable) data by other sessions. ```sql SELECT * FROM test.ilevel; ``` ``` ... +----+------------+ | pk | name | +----+------------+ | 1 | Moon Light | | 2 | Sun Light | +----+------------+ 2 rows in set (0.00 sec) ``` 5. Exit both sessions. ### Summary In step 2 and step 4, you experienced a typical situation of `READ-COMMITTED`. ## End of Exercises