## Module 6: Concurrency Control - Pessimistic Mode VS Optimistic Mode

In all database that supports concurrent access, there's a risk of conflicts, where multiple transactions may attempt to update the same data simultaneously. While a well-designed data model can minimize the chances of such conflicts, they remain a possibility and must be effectively managed. Databases offer mechanisms to handle concurrent data access, with the two primary approaches being pessimistic and optimistic concurrency control. TiDB supports both approaches.

This module shows both pessimistic lock and optimistic lock behaviors in TiDB.

### Step 1. Read the pessimistic mode demo code

+ On the left, in the **EXPLORER** section of the VS Code, click `DemoJdbcPessimisticLock.java` file.

+ In the following code snippet, two threads are started. Each thread will create a connection to TiDB.

  ```output
  ...
  for (int i = 0; i < 2; i++) {
      connections.add(DriverManager.getConnection(connection_string));
  }
  ...
  ```

+ In the main method, two threads will be initiated and try to update the same record. Both thread 1 and 2 have auto commit disabled.

  ```java
  new Thread(new DemoJdbcPessimisticLock.RowUpdater(0, id, 1, 6000)).start();
  new Thread(new DemoJdbcPessimisticLock.RowUpdater(1, id, 1000, 2000)).start();
  ```

### Step 2. Run the demo code in pessimistic mode

+ Run the demo code in pessimistic mode and observe the output. Note that thread two is not able to commit until thread 1 commits. One possible output is shown.

  ```bash
  rm -f DemoJdbcTxPessimisticLock.class
  javac -cp mysql-connector-j-9.1.0.jar DemoJdbcPessimisticLock.java
  sleep 1
  java -cp :mysql-connector-j-9.1.0.jar DemoJdbcPessimisticLock
  ```

  ```output
  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 = 5188146770730811393
  Connection B session: BEGIN PESSIMISTIC
  Connection B session: UPDATE test_tx_pessimistic SET name = 'Connection B' WHERE id = 5188146770730811393
  Connection A session: Commit
  Connection A session: Checking result

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

  Connection B session: Commit
  Connection B session: Checking result

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

### Step 3. Read the optimistic mode demo code

+ On the left, in the **EXPLORER** section of the VS Code, click `DemoJdbcOptimisticLock.java` file.

+ Similar to the code in pessimistic mode, two threads are started and try to update the same row simultaneously.

  ```java
  new Thread(new DemoJdbcOptimisticLock.RowUpdater(0, id, retryCommit, 1, 6000, 1000)).start();
  new Thread(new DemoJdbcOptimisticLock.RowUpdater(1, id, retryCommit, 1000, 2000, 9000)).start();
  ```

+ In the case of conflicts, a simple retry is implemented.

  ```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();
    }
  }
  ```

### Step 4. Run the demo in optimistic mode

+ You can run the demo code for optimistic lock using the following command. We first run the code with `no-retry`, and then with `retry`. Possible outputs are shown.

  ```bash
  rm -f DemoJdbcOptimisticLock.class
  javac -cp mysql-connector-j-9.1.0.jar DemoJdbcOptimisticLock.java
  sleep 1
  java -cp :mysql-connector-j-9.1.0.jar DemoJdbcOptimisticLock no-retry
  ```

  ```output
  Connection established.
  Connection A session started
  Connection B session started
  Connection A session: BEGIN OPTIMISTIC
  Connection A session: UPDATE test_tx_optimistic SET name = 'Connection A' WHERE id = 4899916394579099649
  Connection B session: BEGIN OPTIMISTIC
  Connection B session: UPDATE test_tx_optimistic SET name = 'Connection B' WHERE id = 4899916394579099649
  Connection B session: Commit
  Connection B session: Checking result

  /* Executing query: select id, name from test_tx_optimistic; */
          Row#, id, name
          1) 4899916394579099649, Connection B

  Connection A session: Commit
  Connection A ErrorCode: 9007
  Connection A SQLState: HY000
  Connection A Error: java.sql.SQLException: Write conflict, txnStartTS=458625318024118280, conflictStartTS=458625318286262286, conflictCommitTS=458625318889193486, key=????, 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
          1) 4899916394579099649, Connection B
  ```

  ```bash
  rm -f DemoJdbcOptimisticLock.class
  javac -cp mysql-connector-j-9.1.0.jar DemoJdbcOptimisticLock.java
  sleep 1
  java -cp :mysql-connector-j-9.1.0.jar DemoJdbcOptimisticLock retry
  ```

  ```output
  Connection established.
  Connection A session started
  Connection B session started
  Connection A session: BEGIN OPTIMISTIC
  Connection A session: UPDATE test_tx_optimistic SET name = 'Connection A' WHERE id = 2017612633061982209
  Connection B session: BEGIN OPTIMISTIC
  Connection B session: UPDATE test_tx_optimistic SET name = 'Connection B' WHERE id = 2017612633061982209
  Connection B session: Commit
  Connection B session: Checking result

  /* Executing query: select id, name from test_tx_optimistic; */
          Row#, id, name
          1) 2017612633061982209, Connection B

  Connection A session: Commit
  Connection A ErrorCode: 9007
  Connection A SQLState: HY000
  Connection A Error: java.sql.SQLException: Write conflict, txnStartTS=458625338366492677, conflictStartTS=458625338628898820, conflictCommitTS=458625339231567877, key=????, 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
          1) 2017612633061982209, Connection A
  ```

### Step 5. Go to the TiDB Cloud Console to start your own journey

+ The hands-on lab ends here. You can now start your TiDB Cloud journey [here](https://tidbcloud.com/clusters). If you had been using your own free-tier cluster for this lab, you'll find it listed on the same page, the cluster name is ${LAB:SERVERLESS_CLUSTER_NAME}.

+ TiDB Cloud provides a web-based SQL Editor, which you can use to explore TiDB Cloud. No extra setup is required on your laptop at all.

![sql_editor.png 85%](https://lab-static.pingcap.com/images/2026/3/1/da0e9e005e2d10932b0c62b497d3c3282fc58567.png)

+ If you don't want to write SQL queries, no problem. `Chat2Query` is a tool for you to write queries in natural languages.
  + First, copy the following command into the SQL editor to select the database you would like to work with, and then click `Run` on the top right corner.

    ```sql
    USE test;
    ```

    ![demo701_chat2query.png 85%](https://lab-static.pingcap.com/images/2026/3/2/cb2f9fbf97c188815f9c14a5cf329b71a966842b.png)

  + Then, press `ctrl` + `i` in Windows laptops, or `command` + `i` in Mac, to try TiDB `Chat2Query`. You can write your query in natural language. AI will generate the queries for you. Have fun.

{% if SERVERLESS_CLUSTER_ENVIRONMENT == 'user_hosted' %}

+ Here are two examples:

  + In the example 1, ask the `Chat2Query` to `show me the number of records in the table online_ddl_test`. Click `Accept`, after a query is generated and reviewed. Finally, click `Run` on the top right corner.

  ![demo701_chat2query_eg1.png 85%](https://lab-static.pingcap.com/images/2026/3/2/d96c3389ad5f2aec461fc9e66995008a14fd7478.png)

  + In the example 2, ask the `Chat2Query` to `add a column 'c' for me in table t1`. Click `Accept`, after a query is generated and reviewed. Finally, click `Run` on the top right corner.

  ![demo701_chat2query_eg2.png 85%](https://lab-static.pingcap.com/images/2026/3/2/779fa0129a442787e40ef0e665ad0d6120d20984.png)

{% endif %}