## Module 6: Concurrency Control - Pessimistic Mode Vs. Optimistic Mode

In any 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 **EXPLORER** of the VS Code, under folder `mysql_connector_python_demo`, click `lock_pessimistic.py` file. Let's go through it.

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

  ```output
  ...
  t1 = threading.Thread(target=thread1_task)
  t2 = threading.Thread(target=thread2_task)
  t1.start()
  t2.start()
  ...
  ```

+ In the following code, `threading.Event` is used to coordinate the actions of thread 1 and thread 2, so that the conflict is guaranteed to happen. Both thread 1 and 2 have auto commit disabled.

  ```output
  def thread1_task():
    conn1 = get_connection(autocommit=False)
    cur1 = conn1.cursor(dictionary=True)
    cur1.execute("BEGIN PESSIMISTIC;")
    print("[Thread1] BEGIN PESSIMISTIC", flush=True)

    # notify thread2 to begin transaction
    thread1_begin.set()

    # update and hold lock
    cur1.execute("UPDATE users SET name = 'conn1' WHERE id = 1;")
    print("[Thread1] UPDATED row → name='conn1'", flush=True)

    # notify thread2 to begin transaction
    thread1_updated.set()

    # wait thread2 to attempt update (blocked)
    thread2_ready.wait()

    cur1.execute("COMMIT;")
    print("[Thread1] COMMITTED", flush=True)
    ...
  ```

  ```output
  def thread2_task():
    conn2 = get_connection(autocommit=False)
    cur2 = conn2.cursor(dictionary=True)

    # wait thread1 to complete BEGIN
    thread1_begin.wait()

    cur2.execute("BEGIN PESSIMISTIC;")
    print("[Thread2] BEGIN PESSIMISTIC", flush=True)

    # wait thread1 to update and hold lock
    thread1_updated.wait()

    # notify thread1 that I will attempt UPDATE
    thread2_ready.set()
    print("[Thread2] attempting UPDATE (will block)...", flush=True)

    # this line will be blocked until thread1 COMMIT release row lock
    cur2.execute("UPDATE users SET name = 'conn2' WHERE id = 1;")
    print("[Thread2] UPDATED row → name='conn2'", flush=True)

    cur2.execute("COMMIT;")
    print("[Thread2] COMMITTED", flush=True)
    ...
  ```

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

+ On the left **EXPLORER** of the VS Code, under folder `mysql_connector_python_demo`, click `lock_pessimistic.py` file. Let's go through it.

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

  ```bash
  poetry run python mysql_connector_python_demo/lock_pessimistic.py
  ```

  ```output
  Create database lab_demo_405_3450001
  [Thread1] BEGIN PESSIMISTIC
  [Thread2] BEGIN PESSIMISTIC
  [Thread1] UPDATED row → name='conn1'
  [Thread2] attempting UPDATE (will block)...
  [Thread1] COMMITTED
  [Thread2] UPDATED row → name='conn2'
  [Thread2] COMMITTED
  Update Result: name is changed to conn2
  ```

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

+ On the left **EXPLORER** of the VS Code, under folder `mysql_connector_python_demo`, click `lock_optimistic.py` file. Let's go through it.

+ The demo code for optimistic lock is similar to the one for pessimistic lock, except that `try` and `except` logic is implemented for both threads with optimistic lock. Also, `threading.Event` is not used in the demo code for optimistic lock.

+ We take thread 1 as an example.

  ```output
  def thread1_task():
    ...
    cur1.execute("BEGIN OPTIMISTIC;")
    print("[Thread1] BEGIN OPTIMISTIC", flush=True)

    # update
    cur1.execute("UPDATE users SET name = 'conn1' WHERE id = 1;")
    print("[Thread1] UPDATED row → name='conn1'", flush=True)
    ...

    # thread1 try to commit.
    # error(s) will be raised if there is a conflict
    try:
        cur1.execute("UPDATE users SET name = 'conn1' WHERE id = 1;")
        print("[Thread1] UPDATED row → name='conn1'", flush=True)
        print("[Thread1] Try to commit", flush=True)

        cur1.execute("COMMIT;")
        print("[Thread1] Commit Successful", flush=True)

    except mysql.connector.Error as err:
        print("[Thread1] Conflict occurs: {}".format(err), flush=True)
        print("[Thread1] Retry", flush=True)

        try:
            cur1.execute("BEGIN OPTIMISTIC;")
            print("[Thread1 retry] BEGIN OPTIMISTIC", flush=True)

            cur1.execute("UPDATE users SET name = 'conn1' WHERE id = 1;")
            print("[Thread1 retry] UPDATED row → name='conn1'", flush=True)

            cur1.execute("COMMIT;")
            print("[Thread1 retry] Commit successful", flush=True)

        except mysql.connector.Error as err:
            print("[Thread1 retry] Conflict occurs: {}".format(err), flush=True)
  ```

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

+ You can run the demo code for optimistic lock using the following command. Since we do not use `threading.Event` to control the execution sequence, there are many possible outputs. We list three typical outputs here. In the first sample output, `Thread 2` has a conflict and then retried. In the second sample output, `Thread 1` has a conflict. In the third sample output, there is no conflict.

+ However, no transaction lock waits will occur in this scenario. If a conflict arises, the resolution is left to your discretion. In our demo, a simple retry will be executed.

  ```bash
  poetry run python mysql_connector_python_demo/lock_optimistic.py
  ```

  ```output
  [Thread1] BEGIN OPTIMISTIC
  [Thread1] UPDATED row → name='conn1'
  [Thread1] UPDATED row → name='conn1'
  [Thread1] Try to commit
  [Thread2] BEGIN OPTIMISTIC
  [Thread1] Commit Successful
  [Thread2] UPDATED row → name='conn2'
  [Thread2] Try to commit
  [Thread2] Conflict occurs: 9007 (HY000): Write conflict, txnStartTS=458156067333603352, conflictStartTS=458156067333603338, conflictCommitTS=458156067333603359, key=????, reason=Optimistic [try again later]
  [Thread2] Retry
  [Thread2 retry] BEGIN OPTIMISTIC
  [Thread2 retry] UPDATED row → name='conn2'
  [Thread2 retry] Commit successful
  Update Result: name is changed to conn2
  ```

  ```output
  [Thread2] BEGIN OPTIMISTIC
  [Thread2] UPDATED row → name='conn2'
  [Thread2] Try to commit
  [Thread1] BEGIN OPTIMISTIC
  [Thread2] Commit Successful
  [Thread1] UPDATED row → name='conn1'
  [Thread1] UPDATED row → name='conn1'
  [Thread1] Try to commit
  [Thread1] Conflict occurs: 9007 (HY000): Write conflict, txnStartTS=458156066350563402, conflictStartTS=458156066350563397, conflictCommitTS=458156066350563406, key=????, reason=Optimistic [try again later]
  [Thread1] Retry
  [Thread1 retry] BEGIN OPTIMISTIC
  [Thread1 retry] UPDATED row → name='conn1'
  [Thread1 retry] Commit successful
  Update Result: name is changed to conn1
  ```

  ```output
  [Thread2] BEGIN OPTIMISTIC
  [Thread2] UPDATED row → name='conn2'
  [Thread2] Try to commit
  [Thread2] Commit Successful
  [Thread1] BEGIN OPTIMISTIC
  [Thread1] UPDATED row → name='conn1'
  [Thread1] UPDATED row → name='conn1'
  [Thread1] Try to commit
  [Thread1] Commit Successful
  Update Result: name is changed to conn1
  ```

### 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 ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}
    ```

    ![chat2query.png 85%](https://lab-static.pingcap.com/images/2026/3/2/92fb25fbfdbbf592a6e6b2abd0b095584a06d477.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.

  ![chat2query_eg1.png 85%](https://lab-static.pingcap.com/images/2026/3/2/0e3e5d953884b1f8afc24cf4ed4e4d8d91f4f7af.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.

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

{% endif %}