# Optimistic Transaction Lock ## Overview + In this exercise, you will learn how to work with optimistic transaction lock. + You first create a table and establish two connections. Both connections try to update the exact same record in the table. Unlike pessimistic transaction lock, the optimistic transaction lock checks if the transaction can commit at the end. If conflict occurs, the application code can decide whether to retry or raise an error. ### 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. + You have JDK 8 or later installed. ### Duration + This exercise should take you approximately 15 minutes to complete. ### Tasks 1. Check the Java source code of the demo program. 2. Run the demo script to create a conflict caused by two transactions, execute the script, and observe the results. ### Solution 1. Under `tidb-course-201-lab/scripts` folder, in Java source code `DemoJdbcTxOptimisticLock.java`, you should pay attention to following lines (there is no need to go through all the source code). The following codes in the `main()` method establish two connections. ```java for (int i = 0; i < 2; i++) { connections.add(DriverManager.getConnection( "jdbc:mysql://" + tidbHost + ":" + port + "/test?" + securityOption, dbUsername, dbPassword)); } ``` The first connection creates a table called `test_tx_optimistic`, with two columns `id` and `name`. The column `id` is the primary key, with `AUTO_RANDOM` attribute. The key is then returned, so that later on both connections know which record to update. ```java ... Statement s = c.createStatement(); ... s.executeUpdate("CREATE TABLE test_tx_optimistic (id BIGINT PRIMARY KEY AUTO_RANDOM, name CHAR(20))"); s.executeUpdate("INSERT INTO test_tx_optimistic (name) VALUES ('INIT') ", Statement.RETURN_GENERATED_KEYS); ``` `ATUOCOMMIT` is disabled. ```java for (Connection c : connections) { c.setAutoCommit(false); } ``` The next section demonstrates the primary goal, which involves both connections utilizing the `RowUpdater` class to initiate an optimistic transaction and attempting to update the `name` column on the same row. ```java s.executeUpdate("BEGIN OPTIMISTIC"); System.out.println(connectionTags[this.connectionNo] + " session: " + "UPDATE test_tx_optimistic SET name = '" + connectionTags[this.connectionNo] + "' WHERE id = " + rowid); s.executeUpdate("UPDATE test_tx_optimistic SET name = '" + connectionTags[this.connectionNo] + "' WHERE id = " + rowid); try { Thread.sleep(this.waitBefore1stCommit); } catch (InterruptedException e) { e.printStackTrace(); } System.out.println(connectionTags[this.connectionNo] + " session: " + "Commit"); c.commit(); ``` When a conflict arises due to row modification, the following code block illustrates how the application side's code can attempt to retry the transaction. ```java if (this.retryCommit) { try { Thread.sleep(this.waitBefore2ndCommit); } catch (InterruptedException ie) { ie.printStackTrace(); } try { System.out.println(connectionTags[this.connectionNo] + " session: " + "Commit"); Statement s = c.createStatement(); s.executeUpdate("UPDATE test_tx_optimistic SET name = '" + connectionTags[this.connectionNo] + "' WHERE id = " + rowid); c.commit(); } catch (SQLException e1) { e1.printStackTrace(); } } ``` 2. Run the script `09-demo-jdbc-tx-optimistic-01-show.sh` with the option `local no-retry` and observe the results. An error with code `9007` is expected and no transaction retry will be attempted. Only one connection can successfully complete its transaction. ``` ./09-demo-jdbc-tx-optimistic-01-show.sh local no-retry ``` ``` $ ./09-demo-jdbc-tx-optimistic-01-show.sh local no-retry TiDB endpoint: 127.0.0.1 TiDB username: root Default TiDB server port: 4000 Security options: Connection established. Connection B session started Connection A session started Connection A session: BEGIN OPTIMISTIC Connection A session: UPDATE test_tx_optimistic SET name = 'Connection A' WHERE id = 3170534137668829185 Connection B session: BEGIN OPTIMISTIC Connection B session: UPDATE test_tx_optimistic SET name = 'Connection B' WHERE id = 3170534137668829185 Connection B session: Commit Connection B session: Checking result /* Executing query: select id, name from test_tx_optimistic; */ Row#, id, name 1) 3170534137668829185, Connection B Connection A session: Commit Connection A ErrorCode: 9007 Connection A SQLState: HY000 Connection A Error: java.sql.SQLException: Write conflict, txnStartTS=442128584348794883, conflictStartTS=442128584610938881, conflictCommitTS=442128585135226882, key={tableID=92, tableName=test.test_tx_optimistic, handle=3170534137668829185}, originalKey=74800000000000005c5f72ac00000000000001, primary={tableID=92, tableName=test.test_tx_optimistic, handle=3170534137668829185}, originalPrimaryKey=74800000000000005c5f72ac00000000000001, reason=Optimistic [try again later] < Session in Connection A RAISED THE EXCEPTION !!! > Connection A session: Checking result /* Executing query: select id, name from test_tx_optimistic; */ Row#, id, name 2) 3170534137668829185, Connection B ``` 3. Run the script `09-demo-jdbc-tx-optimistic-01-show.sh` with the option `local retry` and observe the results. Both transactions are expected to be successful as there will be a retry executed this time. ``` ./09-demo-jdbc-tx-optimistic-01-show.sh local retry ``` ``` $ ./09-demo-jdbc-tx-optimistic-01-show.sh local retry TiDB endpoint: 127.0.0.1 TiDB username: root Default TiDB server port: 4000 Security options: Connection established. Connection B session started Connection A session started Connection A session: BEGIN OPTIMISTIC Connection A session: UPDATE test_tx_optimistic SET name = 'Connection A' WHERE id = 1441151880758558721 Connection B session: BEGIN OPTIMISTIC Connection B session: UPDATE test_tx_optimistic SET name = 'Connection B' WHERE id = 1441151880758558721 Connection B session: Commit Connection B session: Checking result /* Executing query: select id, name from test_tx_optimistic; */ Row#, id, name 1) 1441151880758558721, Connection B Connection A session: Commit Connection A ErrorCode: 9007 Connection A SQLState: HY000 Connection A Error: java.sql.SQLException: Write conflict, txnStartTS=442128746578182147, conflictStartTS=442128746840326145, conflictCommitTS=442128747364876290, key={tableID=95, tableName=test.test_tx_optimistic, handle=1441151880758558721}, originalKey=74800000000000005f5f729400000000000001, primary={tableID=95, tableName=test.test_tx_optimistic, handle=1441151880758558721}, originalPrimaryKey=74800000000000005f5f729400000000000001, reason=Optimistic [try again later] < Session in Connection A RAISED THE EXCEPTION !!! > Connection A session: Commit Connection A session: Checking result /* Executing query: select id, name from test_tx_optimistic; */ Row#, id, name 2) 1441151880758558721, Connection A ``` ## End of Exercises