# Pessimistic Transaction Lock

### Overview
+ In this exercise, you will learn how to work with pessimistic transaction locks.
+ You first create a table and establish two connections. Both connections try to update the exact same record in the table at the same time. You can observe that, only after the first transaction commits, the second transaction is able to proceed.

### 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 10 minutes to complete.

### Tasks
1. Check the Java source code of the demo program.
2. Run the demo script to create a conflict situation (two transactions modify the same row at the same time), execute the script, and observe the results.

### Solution
1. Under the `tidb-course-201-lab/scripts` folder, in Java source code `DemoJdbcTxPessimisticLock.java`, pay attention to the 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_pessimistic`, with two columns `id` and `name`. The column `id` is the primary key, with `AUTO_RANDOM` attribute. The key is then fetched, so that later on both connections know which record to update.

      ```java
      ...
      Statement s = c.createStatement();
      ...
      s.executeUpdate("CREATE TABLE test_tx_pessimistic (id BIGINT PRIMARY KEY AUTO_RANDOM, name CHAR(20))");
      s.executeUpdate("INSERT INTO test_tx_pessimistic (name) VALUES ('INIT') ", Statement.RETURN_GENERATED_KEYS);
      ```

      `AUTOCOMMIT` is disabled. 
      ```
      for (Connection c : connections) {
          c.setAutoCommit(false);
      }
      ```

      The following section illustrates the main objective. Both connections use the `RowUpdater` class, initialize a pessimistic transaction, and try to update the `name` column on the same row.
      ```java
      ...
      Statement s = c.createStatement();
      ...
      System.out.println(connectionTags[this.connectionNo] + " session: " + "BEGIN PESSIMISTIC");
      s.executeUpdate("BEGIN PESSIMISTIC");
      System.out.println(connectionTags[this.connectionNo] + " session: "
              + "UPDATE test_tx_pessimistic SET name = '" + connectionTags[this.connectionNo]
              + "' WHERE id = " + rowid);
      s.executeUpdate("UPDATE test_tx_pessimistic SET name = '" + connectionTags[this.connectionNo]
              + "' WHERE id = " + rowid);
      ```

2. Execute the demonstration script and observe the outcomes. One connection (B in the example output) can only commit after the other connection (A in the example output) has committed, and neither connection should encounter any errors during the process.
   
   ```
   $ ./09-demo-jdbc-tx-pessimistic-01-show.sh local
   ```
   ```
   $ ./09-demo-jdbc-tx-pessimistic-01-show.sh local
   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 PESSIMISTIC
   Connection A session: UPDATE test_tx_pessimistic SET name = 'Connection A' WHERE id = 8070450532247928833
   Connection B session: BEGIN PESSIMISTIC
   Connection B session: UPDATE test_tx_pessimistic SET name = 'Connection B' WHERE id = 8070450532247928833
   Connection A session: Commit
   Connection A session: Checking result

   /* Executing query: select id, name from test_tx_pessimistic; */
     Row#, id, name
     1) 8070450532247928833, Connection A

   Connection B session: Commit
   Connection B session: Checking result

   /* Executing query: select id, name from test_tx_pessimistic; */
     Row#, id, name
     2) 8070450532247928833, Connection B
   ```

## End of Exercises