Optimistic Transactions and Pessimistic Transactions

The optimistic transaction model commits the transaction directly, and rolls back when there is a conflict. By contrast, the pessimistic transaction model tries to lock the resources that need to be modified before actually committing the transaction, and only starts committing after ensuring that the transaction can be successfully executed.

The optimistic transaction model is suitable for scenarios with low conflict rates, because the direct commit has a high probability of success. But once a transaction conflict occurs, the cost of rollback is relatively high.

The advantage of the pessimistic transaction model is that for scenarios with high conflict rates, the cost of locking ahead is less than the cost of rollback afterwards. Moreover, it can solve the problem that multiple concurrent transactions fail to commit due to conflicts. However, the pessimistic transaction model is not as efficient as the optimistic transaction model in scenarios with low conflict rates.

The pessimistic transaction model is more intuitive and easier to implement on the application side. The optimistic transaction model requires complex application-side retry mechanisms.

The following is an example of a bookshop. It uses an example of buying books to show the pros and cons of optimistic and pessimistic transactions. The process of buying books mainly consists of the following:

  1. Update the stock quantity
  2. Create an order
  3. Make the payment

These operations must either all succeed or all fail. You must ensure that overselling does not happen in the case of concurrent transactions.

Pessimistic transactions

The following code uses two threads to simulate the process that two users buy the same book in a pessimistic transaction mode. There are 10 books left in the bookstore. Bob buys 6 books, and Alice buys 4 books. They complete the orders at nearly the same time. As a result, all books in inventory are sold out.

  • Java
  • Golang
  • Python

Because you use multiple threads to simulate the situation that multiple users insert data simultaneously, you need to use a connection object with safe threads. Here use Java’s popular connection pool HikariCP for demo.

sql.DB in Golang is concurrency-safe, so there is no need to import a third-party package.

To adapt TiDB transactions, write a toolkit util according to the following code:

  1. package util
  2. import (
  3. "context"
  4. "database/sql"
  5. )
  6. type TiDBSqlTx struct {
  7. *sql.Tx
  8. conn *sql.Conn
  9. pessimistic bool
  10. }
  11. func TiDBSqlBegin(db *sql.DB, pessimistic bool) (*TiDBSqlTx, error) {
  12. ctx := context.Background()
  13. conn, err := db.Conn(ctx)
  14. if err != nil {
  15. return nil, err
  16. }
  17. if pessimistic {
  18. _, err = conn.ExecContext(ctx, "set @@tidb_txn_mode=?", "pessimistic")
  19. } else {
  20. _, err = conn.ExecContext(ctx, "set @@tidb_txn_mode=?", "optimistic")
  21. }
  22. if err != nil {
  23. return nil, err
  24. }
  25. tx, err := conn.BeginTx(ctx, nil)
  26. if err != nil {
  27. return nil, err
  28. }
  29. return &TiDBSqlTx{
  30. conn: conn,
  31. Tx: tx,
  32. pessimistic: pessimistic,
  33. }, nil
  34. }
  35. func (tx *TiDBSqlTx) Commit() error {
  36. defer tx.conn.Close()
  37. return tx.Tx.Commit()
  38. }
  39. func (tx *TiDBSqlTx) Rollback() error {
  40. defer tx.conn.Close()
  41. return tx.Tx.Rollback()
  42. }

To ensure thread safety, you can use the mysqlclient driver to open multiple connections that are not shared between threads.

Write a pessimistic transaction example

  • Java
  • Golang
  • Python

Configuration file

If you use Maven to manage the package, in the <dependencies> node in pom.xml, add the following dependencies to import HikariCP, and set the packaging target, and the main class of the JAR package startup. The following is an example of pom.xml.

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  3. xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  4. <modelVersion>4.0.0</modelVersion>
  5. <groupId>com.pingcap</groupId>
  6. <artifactId>plain-java-txn</artifactId>
  7. <version>0.0.1</version>
  8. <name>plain-java-jdbc</name>
  9. <properties>
  10. <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  11. <maven.compiler.source>17</maven.compiler.source>
  12. <maven.compiler.target>17</maven.compiler.target>
  13. </properties>
  14. <dependencies>
  15. <dependency>
  16. <groupId>junit</groupId>
  17. <artifactId>junit</artifactId>
  18. <version>4.13.2</version>
  19. <scope>test</scope>
  20. </dependency>
  21. <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
  22. <dependency>
  23. <groupId>mysql</groupId>
  24. <artifactId>mysql-connector-java</artifactId>
  25. <version>8.0.28</version>
  26. </dependency>
  27. <dependency>
  28. <groupId>com.zaxxer</groupId>
  29. <artifactId>HikariCP</artifactId>
  30. <version>5.0.1</version>
  31. </dependency>
  32. </dependencies>
  33. <build>
  34. <plugins>
  35. <plugin>
  36. <groupId>org.apache.maven.plugins</groupId>
  37. <artifactId>maven-assembly-plugin</artifactId>
  38. <version>3.3.0</version>
  39. <configuration>
  40. <descriptorRefs>
  41. <descriptorRef>jar-with-dependencies</descriptorRef>
  42. </descriptorRefs>
  43. <archive>
  44. <manifest>
  45. <mainClass>com.pingcap.txn.TxnExample</mainClass>
  46. </manifest>
  47. </archive>
  48. </configuration>
  49. <executions>
  50. <execution>
  51. <id>make-assembly</id>
  52. <phase>package</phase>
  53. <goals>
  54. <goal>single</goal>
  55. </goals>
  56. </execution>
  57. </executions>
  58. </plugin>
  59. </plugins>
  60. </build>
  61. </project>

Coding

Then write the code:

  1. package com.pingcap.txn;
  2. import com.zaxxer.hikari.HikariDataSource;
  3. import java.math.BigDecimal;
  4. import java.sql.*;
  5. import java.util.Arrays;
  6. import java.util.concurrent.*;
  7. public class TxnExample {
  8. public static void main(String[] args) throws SQLException, InterruptedException {
  9. System.out.println(Arrays.toString(args));
  10. int aliceQuantity = 0;
  11. int bobQuantity = 0;
  12. for (String arg: args) {
  13. if (arg.startsWith("ALICE_NUM")) {
  14. aliceQuantity = Integer.parseInt(arg.replace("ALICE_NUM=", ""));
  15. }
  16. if (arg.startsWith("BOB_NUM")) {
  17. bobQuantity = Integer.parseInt(arg.replace("BOB_NUM=", ""));
  18. }
  19. }
  20. HikariDataSource ds = new HikariDataSource();
  21. ds.setJdbcUrl("jdbc:mysql://localhost:4000/bookshop?useServerPrepStmts=true&cachePrepStmts=true");
  22. ds.setUsername("root");
  23. ds.setPassword("");
  24. // prepare data
  25. Connection connection = ds.getConnection();
  26. createBook(connection, 1L, "Designing Data-Intensive Application", "Science & Technology",
  27. Timestamp.valueOf("2018-09-01 00:00:00"), new BigDecimal(100), 10);
  28. createUser(connection, 1L, "Bob", new BigDecimal(10000));
  29. createUser(connection, 2L, "Alice", new BigDecimal(10000));
  30. CountDownLatch countDownLatch = new CountDownLatch(2);
  31. ExecutorService threadPool = Executors.newFixedThreadPool(2);
  32. final int finalBobQuantity = bobQuantity;
  33. threadPool.execute(() -> {
  34. buy(ds, 1, 1000L, 1L, 1L, finalBobQuantity);
  35. countDownLatch.countDown();
  36. });
  37. final int finalAliceQuantity = aliceQuantity;
  38. threadPool.execute(() -> {
  39. buy(ds, 2, 1001L, 1L, 2L, finalAliceQuantity);
  40. countDownLatch.countDown();
  41. });
  42. countDownLatch.await(5, TimeUnit.SECONDS);
  43. }
  44. public static void createUser(Connection connection, Long id, String nickname, BigDecimal balance) throws SQLException {
  45. PreparedStatement insert = connection.prepareStatement(
  46. "INSERT INTO `users` (`id`, `nickname`, `balance`) VALUES (?, ?, ?)");
  47. insert.setLong(1, id);
  48. insert.setString(2, nickname);
  49. insert.setBigDecimal(3, balance);
  50. insert.executeUpdate();
  51. }
  52. public static void createBook(Connection connection, Long id, String title, String type, Timestamp publishedAt, BigDecimal price, Integer stock) throws SQLException {
  53. PreparedStatement insert = connection.prepareStatement(
  54. "INSERT INTO `books` (`id`, `title`, `type`, `published_at`, `price`, `stock`) values (?, ?, ?, ?, ?, ?)");
  55. insert.setLong(1, id);
  56. insert.setString(2, title);
  57. insert.setString(3, type);
  58. insert.setTimestamp(4, publishedAt);
  59. insert.setBigDecimal(5, price);
  60. insert.setInt(6, stock);
  61. insert.executeUpdate();
  62. }
  63. public static void buy (HikariDataSource ds, Integer threadID,
  64. Long orderID, Long bookID, Long userID, Integer quantity) {
  65. String txnComment = "/* txn " + threadID + " */ ";
  66. try (Connection connection = ds.getConnection()) {
  67. try {
  68. connection.setAutoCommit(false);
  69. connection.createStatement().executeUpdate(txnComment + "begin pessimistic");
  70. // waiting for other thread ran the 'begin pessimistic' statement
  71. TimeUnit.SECONDS.sleep(1);
  72. BigDecimal price = null;
  73. // read price of book
  74. PreparedStatement selectBook = connection.prepareStatement(txnComment + "select price from books where id = ? for update");
  75. selectBook.setLong(1, bookID);
  76. ResultSet res = selectBook.executeQuery();
  77. if (!res.next()) {
  78. throw new RuntimeException("book not exist");
  79. } else {
  80. price = res.getBigDecimal("price");
  81. }
  82. // update book
  83. String updateBookSQL = "update `books` set stock = stock - ? where id = ? and stock - ? >= 0";
  84. PreparedStatement updateBook = connection.prepareStatement(txnComment + updateBookSQL);
  85. updateBook.setInt(1, quantity);
  86. updateBook.setLong(2, bookID);
  87. updateBook.setInt(3, quantity);
  88. int affectedRows = updateBook.executeUpdate();
  89. if (affectedRows == 0) {
  90. // stock not enough, rollback
  91. connection.createStatement().executeUpdate(txnComment + "rollback");
  92. return;
  93. }
  94. // insert order
  95. String insertOrderSQL = "insert into `orders` (`id`, `book_id`, `user_id`, `quality`) values (?, ?, ?, ?)";
  96. PreparedStatement insertOrder = connection.prepareStatement(txnComment + insertOrderSQL);
  97. insertOrder.setLong(1, orderID);
  98. insertOrder.setLong(2, bookID);
  99. insertOrder.setLong(3, userID);
  100. insertOrder.setInt(4, quantity);
  101. insertOrder.executeUpdate();
  102. // update user
  103. String updateUserSQL = "update `users` set `balance` = `balance` - ? where id = ?";
  104. PreparedStatement updateUser = connection.prepareStatement(txnComment + updateUserSQL);
  105. updateUser.setBigDecimal(1, price.multiply(new BigDecimal(quantity)));
  106. updateUser.setLong(2, userID);
  107. updateUser.executeUpdate();
  108. connection.createStatement().executeUpdate(txnComment + "commit");
  109. } catch (Exception e) {
  110. connection.createStatement().executeUpdate(txnComment + "rollback");
  111. e.printStackTrace();
  112. }
  113. } catch (SQLException e) {
  114. e.printStackTrace();
  115. }
  116. }
  117. }

Write a helper.go file that contains the required database operations:

  1. package main
  2. import (
  3. "context"
  4. "database/sql"
  5. "fmt"
  6. "time"
  7. "github.com/go-sql-driver/mysql"
  8. "github.com/pingcap-inc/tidb-example-golang/util"
  9. "github.com/shopspring/decimal"
  10. )
  11. type TxnFunc func(txn *util.TiDBSqlTx) error
  12. const (
  13. ErrWriteConflict = 9007 // Transactions in TiKV encounter write conflicts.
  14. ErrInfoSchemaChanged = 8028 // table schema changes
  15. ErrForUpdateCantRetry = 8002 // "SELECT FOR UPDATE" commit conflict
  16. ErrTxnRetryable = 8022 // The transaction commit fails and has been rolled back
  17. )
  18. const retryTimes = 5
  19. var retryErrorCodeSet = map[uint16]interface{}{
  20. ErrWriteConflict: nil,
  21. ErrInfoSchemaChanged: nil,
  22. ErrForUpdateCantRetry: nil,
  23. ErrTxnRetryable: nil,
  24. }
  25. func runTxn(db *sql.DB, optimistic bool, optimisticRetryTimes int, txnFunc TxnFunc) {
  26. txn, err := util.TiDBSqlBegin(db, !optimistic)
  27. if err != nil {
  28. panic(err)
  29. }
  30. err = txnFunc(txn)
  31. if err != nil {
  32. txn.Rollback()
  33. if mysqlErr, ok := err.(*mysql.MySQLError); ok && optimistic && optimisticRetryTimes != 0 {
  34. if _, retryableError := retryErrorCodeSet[mysqlErr.Number]; retryableError {
  35. fmt.Printf("[runTxn] got a retryable error, rest time: %d\n", optimisticRetryTimes-1)
  36. runTxn(db, optimistic, optimisticRetryTimes-1, txnFunc)
  37. return
  38. }
  39. }
  40. fmt.Printf("[runTxn] got an error, rollback: %+v\n", err)
  41. } else {
  42. err = txn.Commit()
  43. if mysqlErr, ok := err.(*mysql.MySQLError); ok && optimistic && optimisticRetryTimes != 0 {
  44. if _, retryableError := retryErrorCodeSet[mysqlErr.Number]; retryableError {
  45. fmt.Printf("[runTxn] got a retryable error, rest time: %d\n", optimisticRetryTimes-1)
  46. runTxn(db, optimistic, optimisticRetryTimes-1, txnFunc)
  47. return
  48. }
  49. }
  50. if err == nil {
  51. fmt.Println("[runTxn] commit success")
  52. }
  53. }
  54. }
  55. func prepareData(db *sql.DB, optimistic bool) {
  56. runTxn(db, optimistic, retryTimes, func(txn *util.TiDBSqlTx) error {
  57. publishedAt, err := time.Parse("2006-01-02 15:04:05", "2018-09-01 00:00:00")
  58. if err != nil {
  59. return err
  60. }
  61. if err = createBook(txn, 1, "Designing Data-Intensive Application",
  62. "Science & Technology", publishedAt, decimal.NewFromInt(100), 10); err != nil {
  63. return err
  64. }
  65. if err = createUser(txn, 1, "Bob", decimal.NewFromInt(10000)); err != nil {
  66. return err
  67. }
  68. if err = createUser(txn, 2, "Alice", decimal.NewFromInt(10000)); err != nil {
  69. return err
  70. }
  71. return nil
  72. })
  73. }
  74. func buyPessimistic(db *sql.DB, goroutineID, orderID, bookID, userID, amount int) {
  75. txnComment := fmt.Sprintf("/* txn %d */ ", goroutineID)
  76. if goroutineID != 1 {
  77. txnComment = "\t" + txnComment
  78. }
  79. fmt.Printf("\nuser %d try to buy %d books(id: %d)\n", userID, amount, bookID)
  80. runTxn(db, false, retryTimes, func(txn *util.TiDBSqlTx) error {
  81. time.Sleep(time.Second)
  82. // read the price of book
  83. selectBookForUpdate := "select `price` from books where id = ? for update"
  84. bookRows, err := txn.Query(selectBookForUpdate, bookID)
  85. if err != nil {
  86. return err
  87. }
  88. fmt.Println(txnComment + selectBookForUpdate + " successful")
  89. defer bookRows.Close()
  90. price := decimal.NewFromInt(0)
  91. if bookRows.Next() {
  92. err = bookRows.Scan(&price)
  93. if err != nil {
  94. return err
  95. }
  96. } else {
  97. return fmt.Errorf("book ID not exist")
  98. }
  99. bookRows.Close()
  100. // update book
  101. updateStock := "update `books` set stock = stock - ? where id = ? and stock - ? >= 0"
  102. result, err := txn.Exec(updateStock, amount, bookID, amount)
  103. if err != nil {
  104. return err
  105. }
  106. fmt.Println(txnComment + updateStock + " successful")
  107. affected, err := result.RowsAffected()
  108. if err != nil {
  109. return err
  110. }
  111. if affected == 0 {
  112. return fmt.Errorf("stock not enough, rollback")
  113. }
  114. // insert order
  115. insertOrder := "insert into `orders` (`id`, `book_id`, `user_id`, `quality`) values (?, ?, ?, ?)"
  116. if _, err := txn.Exec(insertOrder,
  117. orderID, bookID, userID, amount); err != nil {
  118. return err
  119. }
  120. fmt.Println(txnComment + insertOrder + " successful")
  121. // update user
  122. updateUser := "update `users` set `balance` = `balance` - ? where id = ?"
  123. if _, err := txn.Exec(updateUser,
  124. price.Mul(decimal.NewFromInt(int64(amount))), userID); err != nil {
  125. return err
  126. }
  127. fmt.Println(txnComment + updateUser + " successful")
  128. return nil
  129. })
  130. }
  131. func buyOptimistic(db *sql.DB, goroutineID, orderID, bookID, userID, amount int) {
  132. txnComment := fmt.Sprintf("/* txn %d */ ", goroutineID)
  133. if goroutineID != 1 {
  134. txnComment = "\t" + txnComment
  135. }
  136. fmt.Printf("\nuser %d try to buy %d books(id: %d)\n", userID, amount, bookID)
  137. runTxn(db, true, retryTimes, func(txn *util.TiDBSqlTx) error {
  138. time.Sleep(time.Second)
  139. // read the price and stock of book
  140. selectBookForUpdate := "select `price`, `stock` from books where id = ? for update"
  141. bookRows, err := txn.Query(selectBookForUpdate, bookID)
  142. if err != nil {
  143. return err
  144. }
  145. fmt.Println(txnComment + selectBookForUpdate + " successful")
  146. defer bookRows.Close()
  147. price, stock := decimal.NewFromInt(0), 0
  148. if bookRows.Next() {
  149. err = bookRows.Scan(&price, &stock)
  150. if err != nil {
  151. return err
  152. }
  153. } else {
  154. return fmt.Errorf("book ID not exist")
  155. }
  156. bookRows.Close()
  157. if stock < amount {
  158. return fmt.Errorf("book not enough")
  159. }
  160. // update book
  161. updateStock := "update `books` set stock = stock - ? where id = ? and stock - ? >= 0"
  162. result, err := txn.Exec(updateStock, amount, bookID, amount)
  163. if err != nil {
  164. return err
  165. }
  166. fmt.Println(txnComment + updateStock + " successful")
  167. affected, err := result.RowsAffected()
  168. if err != nil {
  169. return err
  170. }
  171. if affected == 0 {
  172. return fmt.Errorf("stock not enough, rollback")
  173. }
  174. // insert order
  175. insertOrder := "insert into `orders` (`id`, `book_id`, `user_id`, `quality`) values (?, ?, ?, ?)"
  176. if _, err := txn.Exec(insertOrder,
  177. orderID, bookID, userID, amount); err != nil {
  178. return err
  179. }
  180. fmt.Println(txnComment + insertOrder + " successful")
  181. // update user
  182. updateUser := "update `users` set `balance` = `balance` - ? where id = ?"
  183. if _, err := txn.Exec(updateUser,
  184. price.Mul(decimal.NewFromInt(int64(amount))), userID); err != nil {
  185. return err
  186. }
  187. fmt.Println(txnComment + updateUser + " successful")
  188. return nil
  189. })
  190. }
  191. func createBook(txn *util.TiDBSqlTx, id int, title, bookType string,
  192. publishedAt time.Time, price decimal.Decimal, stock int) error {
  193. _, err := txn.ExecContext(context.Background(),
  194. "INSERT INTO `books` (`id`, `title`, `type`, `published_at`, `price`, `stock`) values (?, ?, ?, ?, ?, ?)",
  195. id, title, bookType, publishedAt, price, stock)
  196. return err
  197. }
  198. func createUser(txn *util.TiDBSqlTx, id int, nickname string, balance decimal.Decimal) error {
  199. _, err := txn.ExecContext(context.Background(),
  200. "INSERT INTO `users` (`id`, `nickname`, `balance`) VALUES (?, ?, ?)",
  201. id, nickname, balance)
  202. return err
  203. }

Then write a txn.go with a main function to call helper.go and handle the incoming command line arguments:

  1. package main
  2. import (
  3. "database/sql"
  4. "flag"
  5. "fmt"
  6. "sync"
  7. )
  8. func main() {
  9. optimistic, alice, bob := parseParams()
  10. openDB("mysql", "root:@tcp(127.0.0.1:4000)/bookshop?charset=utf8mb4", func(db *sql.DB) {
  11. prepareData(db, optimistic)
  12. buy(db, optimistic, alice, bob)
  13. })
  14. }
  15. func buy(db *sql.DB, optimistic bool, alice, bob int) {
  16. buyFunc := buyOptimistic
  17. if !optimistic {
  18. buyFunc = buyPessimistic
  19. }
  20. wg := sync.WaitGroup{}
  21. wg.Add(1)
  22. go func() {
  23. defer wg.Done()
  24. buyFunc(db, 1, 1000, 1, 1, bob)
  25. }()
  26. wg.Add(1)
  27. go func() {
  28. defer wg.Done()
  29. buyFunc(db, 2, 1001, 1, 2, alice)
  30. }()
  31. wg.Wait()
  32. }
  33. func openDB(driverName, dataSourceName string, runnable func(db *sql.DB)) {
  34. db, err := sql.Open(driverName, dataSourceName)
  35. if err != nil {
  36. panic(err)
  37. }
  38. defer db.Close()
  39. runnable(db)
  40. }
  41. func parseParams() (optimistic bool, alice, bob int) {
  42. flag.BoolVar(&optimistic, "o", false, "transaction is optimistic")
  43. flag.IntVar(&alice, "a", 4, "Alice bought num")
  44. flag.IntVar(&bob, "b", 6, "Bob bought num")
  45. flag.Parse()
  46. fmt.Println(optimistic, alice, bob)
  47. return optimistic, alice, bob
  48. }

The Golang example already includes optimistic transactions.

  1. import time
  2. import MySQLdb
  3. import os
  4. import datetime
  5. from threading import Thread
  6. REPEATABLE_ERROR_CODE_SET = {
  7. 9007, # Transactions in TiKV encounter write conflicts.
  8. 8028, # table schema changes
  9. 8002, # "SELECT FOR UPDATE" commit conflict
  10. 8022 # The transaction commit fails and has been rolled back
  11. }
  12. def create_connection():
  13. return MySQLdb.connect(
  14. host="127.0.0.1",
  15. port=4000,
  16. user="root",
  17. password="",
  18. database="bookshop",
  19. autocommit=False
  20. )
  21. def prepare_data() -> None:
  22. connection = create_connection()
  23. with connection:
  24. with connection.cursor() as cursor:
  25. cursor.execute("INSERT INTO `books` (`id`, `title`, `type`, `published_at`, `price`, `stock`) "
  26. "values (%s, %s, %s, %s, %s, %s)",
  27. (1, "Designing Data-Intensive Application", "Science & Technology",
  28. datetime.datetime(2018, 9, 1), 100, 10))
  29. cursor.executemany("INSERT INTO `users` (`id`, `nickname`, `balance`) VALUES (%s, %s, %s)",
  30. [(1, "Bob", 10000), (2, "ALICE", 10000)])
  31. connection.commit()
  32. def buy_optimistic(thread_id: int, order_id: int, book_id: int, user_id: int, amount: int,
  33. optimistic_retry_times: int = 5) -> None:
  34. connection = create_connection()
  35. txn_log_header = f"/* txn {thread_id} */"
  36. if thread_id != 1:
  37. txn_log_header = "\t" + txn_log_header
  38. with connection:
  39. with connection.cursor() as cursor:
  40. cursor.execute("BEGIN OPTIMISTIC")
  41. print(f'{txn_log_header} BEGIN OPTIMISTIC')
  42. time.sleep(1)
  43. try:
  44. # read the price of book
  45. select_book_for_update = "SELECT `price`, `stock` FROM books WHERE id = %s FOR UPDATE"
  46. cursor.execute(select_book_for_update, (book_id,))
  47. book = cursor.fetchone()
  48. if book is None:
  49. raise Exception("book_id not exist")
  50. price, stock = book
  51. print(f'{txn_log_header} {select_book_for_update} successful')
  52. if stock < amount:
  53. raise Exception("book not enough, rollback")
  54. # update book
  55. update_stock = "update `books` set stock = stock - %s where id = %s and stock - %s >= 0"
  56. rows_affected = cursor.execute(update_stock, (amount, book_id, amount))
  57. print(f'{txn_log_header} {update_stock} successful')
  58. if rows_affected == 0:
  59. raise Exception("stock not enough, rollback")
  60. # insert order
  61. insert_order = "insert into `orders` (`id`, `book_id`, `user_id`, `quality`) values (%s, %s, %s, %s)"
  62. cursor.execute(insert_order, (order_id, book_id, user_id, amount))
  63. print(f'{txn_log_header} {insert_order} successful')
  64. # update user
  65. update_user = "update `users` set `balance` = `balance` - %s where id = %s"
  66. cursor.execute(update_user, (amount * price, user_id))
  67. print(f'{txn_log_header} {update_user} successful')
  68. except Exception as err:
  69. connection.rollback()
  70. print(f'something went wrong: {err}')
  71. else:
  72. # important here! you need deal the Exception from the TiDB
  73. try:
  74. connection.commit()
  75. except MySQLdb.MySQLError as db_err:
  76. code, desc = db_err.args
  77. if code in REPEATABLE_ERROR_CODE_SET and optimistic_retry_times > 0:
  78. print(f'retry, rest {optimistic_retry_times - 1} times, for {code} {desc}')
  79. buy_optimistic(thread_id, order_id, book_id, user_id, amount, optimistic_retry_times - 1)
  80. def buy_pessimistic(thread_id: int, order_id: int, book_id: int, user_id: int, amount: int) -> None:
  81. connection = create_connection()
  82. txn_log_header = f"/* txn {thread_id} */"
  83. if thread_id != 1:
  84. txn_log_header = "\t" + txn_log_header
  85. with connection:
  86. with connection.cursor() as cursor:
  87. cursor.execute("BEGIN PESSIMISTIC")
  88. print(f'{txn_log_header} BEGIN PESSIMISTIC')
  89. time.sleep(1)
  90. try:
  91. # read the price of book
  92. select_book_for_update = "SELECT `price` FROM books WHERE id = %s FOR UPDATE"
  93. cursor.execute(select_book_for_update, (book_id,))
  94. book = cursor.fetchone()
  95. if book is None:
  96. raise Exception("book_id not exist")
  97. price = book[0]
  98. print(f'{txn_log_header} {select_book_for_update} successful')
  99. # update book
  100. update_stock = "update `books` set stock = stock - %s where id = %s and stock - %s >= 0"
  101. rows_affected = cursor.execute(update_stock, (amount, book_id, amount))
  102. print(f'{txn_log_header} {update_stock} successful')
  103. if rows_affected == 0:
  104. raise Exception("stock not enough, rollback")
  105. # insert order
  106. insert_order = "insert into `orders` (`id`, `book_id`, `user_id`, `quality`) values (%s, %s, %s, %s)"
  107. cursor.execute(insert_order, (order_id, book_id, user_id, amount))
  108. print(f'{txn_log_header} {insert_order} successful')
  109. # update user
  110. update_user = "update `users` set `balance` = `balance` - %s where id = %s"
  111. cursor.execute(update_user, (amount * price, user_id))
  112. print(f'{txn_log_header} {update_user} successful')
  113. except Exception as err:
  114. connection.rollback()
  115. print(f'something went wrong: {err}')
  116. else:
  117. connection.commit()
  118. optimistic = os.environ.get('OPTIMISTIC')
  119. alice = os.environ.get('ALICE')
  120. bob = os.environ.get('BOB')
  121. if not (optimistic and alice and bob):
  122. raise Exception("please use \"OPTIMISTIC=<is_optimistic> ALICE=<alice_num> "
  123. "BOB=<bob_num> python3 txn_example.py\" to start this script")
  124. prepare_data()
  125. if bool(optimistic) is True:
  126. buy_func = buy_optimistic
  127. else:
  128. buy_func = buy_pessimistic
  129. bob_thread = Thread(target=buy_func, kwargs={
  130. "thread_id": 1, "order_id": 1000, "book_id": 1, "user_id": 1, "amount": int(bob)})
  131. alice_thread = Thread(target=buy_func, kwargs={
  132. "thread_id": 2, "order_id": 1001, "book_id": 1, "user_id": 2, "amount": int(alice)})
  133. bob_thread.start()
  134. alice_thread.start()
  135. bob_thread.join(timeout=10)
  136. alice_thread.join(timeout=10)

The Python example already includes optimistic transactions.

An example that does not involve overselling

Run the sample program:

  • Java
  • Golang
  • Python
  1. mvn clean package
  2. java -jar target/plain-java-txn-0.0.1-jar-with-dependencies.jar ALICE_NUM=4 BOB_NUM=6
  1. go build -o bin/txn
  2. ./bin/txn -a 4 -b 6
  1. OPTIMISTIC=False ALICE=4 BOB=6 python3 txn_example.py

SQL logs:

  1. /* txn 1 */ BEGIN PESSIMISTIC
  2. /* txn 2 */ BEGIN PESSIMISTIC
  3. /* txn 2 */ SELECT * FROM `books` WHERE `id` = 1 FOR UPDATE
  4. /* txn 2 */ UPDATE `books` SET `stock` = `stock` - 4 WHERE `id` = 1 AND `stock` - 4 >= 0
  5. /* txn 2 */ INSERT INTO `orders` (`id`, `book_id`, `user_id`, `quality`) VALUES (1001, 1, 1, 4)
  6. /* txn 2 */ UPDATE `users` SET `balance` = `balance` - 400.0 WHERE `id` = 2
  7. /* txn 2 */ COMMIT
  8. /* txn 1 */ SELECT * FROM `books` WHERE `id` = 1 FOR UPDATE
  9. /* txn 1 */ UPDATE `books` SET `stock` = `stock` - 6 WHERE `id` = 1 AND `stock` - 6 >= 0
  10. /* txn 1 */ INSERT INTO `orders` (`id`, `book_id`, `user_id`, `quality`) VALUES (1000, 1, 1, 6)
  11. /* txn 1 */ UPDATE `users` SET `balance` = `balance` - 600.0 WHERE `id` = 1
  12. /* txn 1 */ COMMIT

Finally, check that the order is created, the user balance is deducted, and the book inventory is deducted as expected.

  1. mysql> SELECT * FROM `books`;
  2. +----+--------------------------------------+----------------------+---------------------+-------+--------+
  3. | id | title | type | published_at | stock | price |
  4. +----+--------------------------------------+----------------------+---------------------+-------+--------+
  5. | 1 | Designing Data-Intensive Application | Science & Technology | 2018-09-01 00:00:00 | 0 | 100.00 |
  6. +----+--------------------------------------+----------------------+---------------------+-------+--------+
  7. 1 row in set (0.00 sec)
  8. mysql> SELECT * FROM orders;
  9. +------+---------+---------+---------+---------------------+
  10. | id | book_id | user_id | quality | ordered_at |
  11. +------+---------+---------+---------+---------------------+
  12. | 1000 | 1 | 1 | 6 | 2022-04-19 10:58:12 |
  13. | 1001 | 1 | 1 | 4 | 2022-04-19 10:58:11 |
  14. +------+---------+---------+---------+---------------------+
  15. 2 rows in set (0.01 sec)
  16. mysql> SELECT * FROM users;
  17. +----+---------+----------+
  18. | id | balance | nickname |
  19. +----+---------+----------+
  20. | 1 | 9400.00 | Bob |
  21. | 2 | 9600.00 | Alice |
  22. +----+---------+----------+
  23. 2 rows in set (0.00 sec)

An example that prevents overselling

The task in this example is more challenging. Suppose there are 10 books left in stock. Bob buys 7 books, Alice buys 4 books, and they place orders almost at the same time. What will happen? You can reuse the code from the previous example to solve this challenge, and change Bob’s purchase quantity from 6 to 7.

Run the sample program:

  • Java
  • Golang
  • Python
  1. mvn clean package
  2. java -jar target/plain-java-txn-0.0.1-jar-with-dependencies.jar ALICE_NUM=4 BOB_NUM=7
  1. go build -o bin/txn
  2. ./bin/txn -a 4 -b 7
  1. OPTIMISTIC=False ALICE=4 BOB=7 python3 txn_example.py
  1. /* txn 1 */ BEGIN PESSIMISTIC
  2. /* txn 2 */ BEGIN PESSIMISTIC
  3. /* txn 2 */ SELECT * FROM `books` WHERE `id` = 1 FOR UPDATE
  4. /* txn 2 */ UPDATE `books` SET `stock` = `stock` - 4 WHERE `id` = 1 AND `stock` - 4 >= 0
  5. /* txn 2 */ INSERT INTO `orders` (`id`, `book_id`, `user_id`, `quality`) values (1001, 1, 1, 4)
  6. /* txn 2 */ UPDATE `users` SET `balance` = `balance` - 400.0 WHERE `id` = 2
  7. /* txn 2 */ COMMIT
  8. /* txn 1 */ SELECT * FROM `books` WHERE `id` = 1 FOR UPDATE
  9. /* txn 1 */ UPDATE `books` SET `stock` = `stock` - 7 WHERE `id` = 1 AND `stock` - 7 >= 0
  10. /* txn 1 */ ROLLBACK

Since txn 2 preemptively gets the lock resource and updates the stock, the return value of affected_rows in txn 1 is 0, and it enters the rollback process.

Let’s check the order creation, user balance deduction, and book inventory deduction. Alice successfully ordered 4 books, Bob failed to order 7 books, and the remaining 6 books are in stock as expected.

  1. mysql> SELECT * FROM books;
  2. +----+--------------------------------------+----------------------+---------------------+-------+--------+
  3. | id | title | type | published_at | stock | price |
  4. +----+--------------------------------------+----------------------+---------------------+-------+--------+
  5. | 1 | Designing Data-Intensive Application | Science & Technology | 2018-09-01 00:00:00 | 6 | 100.00 |
  6. +----+--------------------------------------+----------------------+---------------------+-------+--------+
  7. 1 row in set (0.00 sec)
  8. mysql> SELECT * FROM orders;
  9. +------+---------+---------+---------+---------------------+
  10. | id | book_id | user_id | quality | ordered_at |
  11. +------+---------+---------+---------+---------------------+
  12. | 1001 | 1 | 1 | 4 | 2022-04-19 11:03:03 |
  13. +------+---------+---------+---------+---------------------+
  14. 1 row in set (0.00 sec)
  15. mysql> SELECT * FROM users;
  16. +----+----------+----------+
  17. | id | balance | nickname |
  18. +----+----------+----------+
  19. | 1 | 10000.00 | Bob |
  20. | 2 | 9600.00 | Alice |
  21. +----+----------+----------+
  22. 2 rows in set (0.01 sec)

Optimistic transactions

The following code uses two threads to simulate the process that two users buy the same book in an optimistic transaction, just like the pessimistic transaction example. There are 10 books left in inventory. Bob buys 6 and Alice buys 4. They complete the order at about the same time. In the end, no books are left in inventory.

Write an optimistic transaction example

  • Java
  • Golang
  • Python

Coding

  1. package com.pingcap.txn.optimistic;
  2. import com.zaxxer.hikari.HikariDataSource;
  3. import java.math.BigDecimal;
  4. import java.sql.*;
  5. import java.util.Arrays;
  6. import java.util.concurrent.*;
  7. public class TxnExample {
  8. public static void main(String[] args) throws SQLException, InterruptedException {
  9. System.out.println(Arrays.toString(args));
  10. int aliceQuantity = 0;
  11. int bobQuantity = 0;
  12. for (String arg: args) {
  13. if (arg.startsWith("ALICE_NUM")) {
  14. aliceQuantity = Integer.parseInt(arg.replace("ALICE_NUM=", ""));
  15. }
  16. if (arg.startsWith("BOB_NUM")) {
  17. bobQuantity = Integer.parseInt(arg.replace("BOB_NUM=", ""));
  18. }
  19. }
  20. HikariDataSource ds = new HikariDataSource();
  21. ds.setJdbcUrl("jdbc:mysql://localhost:4000/bookshop?useServerPrepStmts=true&cachePrepStmts=true");
  22. ds.setUsername("root");
  23. ds.setPassword("");
  24. // prepare data
  25. Connection connection = ds.getConnection();
  26. createBook(connection, 1L, "Designing Data-Intensive Application", "Science & Technology",
  27. Timestamp.valueOf("2018-09-01 00:00:00"), new BigDecimal(100), 10);
  28. createUser(connection, 1L, "Bob", new BigDecimal(10000));
  29. createUser(connection, 2L, "Alice", new BigDecimal(10000));
  30. CountDownLatch countDownLatch = new CountDownLatch(2);
  31. ExecutorService threadPool = Executors.newFixedThreadPool(2);
  32. final int finalBobQuantity = bobQuantity;
  33. threadPool.execute(() -> {
  34. buy(ds, 1, 1000L, 1L, 1L, finalBobQuantity, 5);
  35. countDownLatch.countDown();
  36. });
  37. final int finalAliceQuantity = aliceQuantity;
  38. threadPool.execute(() -> {
  39. buy(ds, 2, 1001L, 1L, 2L, finalAliceQuantity, 5);
  40. countDownLatch.countDown();
  41. });
  42. countDownLatch.await(5, TimeUnit.SECONDS);
  43. }
  44. public static void createUser(Connection connection, Long id, String nickname, BigDecimal balance) throws SQLException {
  45. PreparedStatement insert = connection.prepareStatement(
  46. "INSERT INTO `users` (`id`, `nickname`, `balance`) VALUES (?, ?, ?)");
  47. insert.setLong(1, id);
  48. insert.setString(2, nickname);
  49. insert.setBigDecimal(3, balance);
  50. insert.executeUpdate();
  51. }
  52. public static void createBook(Connection connection, Long id, String title, String type, Timestamp publishedAt, BigDecimal price, Integer stock) throws SQLException {
  53. PreparedStatement insert = connection.prepareStatement(
  54. "INSERT INTO `books` (`id`, `title`, `type`, `published_at`, `price`, `stock`) values (?, ?, ?, ?, ?, ?)");
  55. insert.setLong(1, id);
  56. insert.setString(2, title);
  57. insert.setString(3, type);
  58. insert.setTimestamp(4, publishedAt);
  59. insert.setBigDecimal(5, price);
  60. insert.setInt(6, stock);
  61. insert.executeUpdate();
  62. }
  63. public static void buy (HikariDataSource ds, Integer threadID, Long orderID, Long bookID,
  64. Long userID, Integer quantity, Integer retryTimes) {
  65. String txnComment = "/* txn " + threadID + " */ ";
  66. try (Connection connection = ds.getConnection()) {
  67. try {
  68. connection.setAutoCommit(false);
  69. connection.createStatement().executeUpdate(txnComment + "begin optimistic");
  70. // waiting for other thread ran the 'begin optimistic' statement
  71. TimeUnit.SECONDS.sleep(1);
  72. BigDecimal price = null;
  73. // read price of book
  74. PreparedStatement selectBook = connection.prepareStatement(txnComment + "SELECT * FROM books where id = ? for update");
  75. selectBook.setLong(1, bookID);
  76. ResultSet res = selectBook.executeQuery();
  77. if (!res.next()) {
  78. throw new RuntimeException("book not exist");
  79. } else {
  80. price = res.getBigDecimal("price");
  81. int stock = res.getInt("stock");
  82. if (stock < quantity) {
  83. throw new RuntimeException("book not enough");
  84. }
  85. }
  86. // update book
  87. String updateBookSQL = "update `books` set stock = stock - ? where id = ? and stock - ? >= 0";
  88. PreparedStatement updateBook = connection.prepareStatement(txnComment + updateBookSQL);
  89. updateBook.setInt(1, quantity);
  90. updateBook.setLong(2, bookID);
  91. updateBook.setInt(3, quantity);
  92. updateBook.executeUpdate();
  93. // insert order
  94. String insertOrderSQL = "insert into `orders` (`id`, `book_id`, `user_id`, `quality`) values (?, ?, ?, ?)";
  95. PreparedStatement insertOrder = connection.prepareStatement(txnComment + insertOrderSQL);
  96. insertOrder.setLong(1, orderID);
  97. insertOrder.setLong(2, bookID);
  98. insertOrder.setLong(3, userID);
  99. insertOrder.setInt(4, quantity);
  100. insertOrder.executeUpdate();
  101. // update user
  102. String updateUserSQL = "update `users` set `balance` = `balance` - ? where id = ?";
  103. PreparedStatement updateUser = connection.prepareStatement(txnComment + updateUserSQL);
  104. updateUser.setBigDecimal(1, price.multiply(new BigDecimal(quantity)));
  105. updateUser.setLong(2, userID);
  106. updateUser.executeUpdate();
  107. connection.createStatement().executeUpdate(txnComment + "commit");
  108. } catch (Exception e) {
  109. connection.createStatement().executeUpdate(txnComment + "rollback");
  110. System.out.println("error occurred: " + e.getMessage());
  111. if (e instanceof SQLException sqlException) {
  112. switch (sqlException.getErrorCode()) {
  113. // You can get all error codes at https://docs.pingcap.com/tidb/stable/error-codes
  114. case 9007: // Transactions in TiKV encounter write conflicts.
  115. case 8028: // table schema changes
  116. case 8002: // "SELECT FOR UPDATE" commit conflict
  117. case 8022: // The transaction commit fails and has been rolled back
  118. if (retryTimes != 0) {
  119. System.out.println("rest " + retryTimes + " times. retry for " + e.getMessage());
  120. buy(ds, threadID, orderID, bookID, userID, quantity, retryTimes - 1);
  121. }
  122. }
  123. }
  124. }
  125. } catch (SQLException e) {
  126. e.printStackTrace();
  127. }
  128. }
  129. }

Configuration changes

Change the startup class in pom.xml:

  1. <mainClass>com.pingcap.txn.TxnExample</mainClass>

Change it to the following to point to the optimistic transaction example.

  1. <mainClass>com.pingcap.txn.optimistic.TxnExample</mainClass>

The Golang example in the Write a pessimistic transaction example section already supports optimistic transactions and can be used directly without changes.

The Python example in the Write a pessimistic transaction example section already supports optimistic transactions and can be used directly without changes.

An example that does not involve overselling

Run the sample program:

  • Java
  • Golang
  • Python
  1. mvn clean package
  2. java -jar target/plain-java-txn-0.0.1-jar-with-dependencies.jar ALICE_NUM=4 BOB_NUM=6
  1. go build -o bin/txn
  2. ./bin/txn -a 4 -b 6 -o true
  1. OPTIMISTIC=True ALICE=4 BOB=6 python3 txn_example.py

SQL statement execution process:

  1. /* txn 2 */ BEGIN OPTIMISTIC
  2. /* txn 1 */ BEGIN OPTIMISTIC
  3. /* txn 2 */ SELECT * FROM `books` WHERE `id` = 1 FOR UPDATE
  4. /* txn 2 */ UPDATE `books` SET `stock` = `stock` - 4 WHERE `id` = 1 AND `stock` - 4 >= 0
  5. /* txn 2 */ INSERT INTO `orders` (`id`, `book_id`, `user_id`, `quality`) VALUES (1001, 1, 1, 4)
  6. /* txn 2 */ UPDATE `users` SET `balance` = `balance` - 400.0 WHERE `id` = 2
  7. /* txn 2 */ COMMIT
  8. /* txn 1 */ SELECT * FROM `books` WHERE `id` = 1 for UPDATE
  9. /* txn 1 */ UPDATE `books` SET `stock` = `stock` - 6 WHERE `id` = 1 AND `stock` - 6 >= 0
  10. /* txn 1 */ INSERT INTO `orders` (`id`, `book_id`, `user_id`, `quality`) VALUES (1000, 1, 1, 6)
  11. /* txn 1 */ UPDATE `users` SET `balance` = `balance` - 600.0 WHERE `id` = 1
  12. retry 1 times for 9007 Write conflict, txnStartTS=432618733006225412, conflictStartTS=432618733006225411, conflictCommitTS=432618733006225414, key={tableID=126, handle=1} primary={tableID=114, indexID=1, indexValues={1, 1000, }} [try again later]
  13. /* txn 1 */ BEGIN OPTIMISTIC
  14. /* txn 1 */ SELECT * FROM `books` WHERE `id` = 1 FOR UPDATE
  15. /* txn 1 */ UPDATE `books` SET `stock` = `stock` - 6 WHERE `id` = 1 AND `stock` - 6 >= 0
  16. /* txn 1 */ INSERT INTO `orders` (`id`, `book_id`, `user_id`, `quality`) VALUES (1000, 1, 1, 6)
  17. /* txn 1 */ UPDATE `users` SET `balance` = `balance` - 600.0 WHERE `id` = 1
  18. /* txn 1 */ COMMIT

In the optimistic transaction mode, because the intermediate state is not necessarily correct, it is not possible to judge whether a statement is successfully executed through affected_rows as in the pessimistic transaction mode. You need to regard the transaction as a whole, and judge whether the current transaction has a write conflict by checking whether the final COMMIT statement returns an exception.

As you can see from the above SQL log, because two transactions are executed concurrently and the same record is modified, a 9007 Write conflict exception is thrown after txn 1 COMMIT. For write conflicts in the optimistic transaction mode, you can safely retry on the application side. After one retry, the data is committed successfully. The final execution result is as expected:

  1. mysql> SELECT * FROM books;
  2. +----+--------------------------------------+----------------------+---------------------+-------+--------+
  3. | id | title | type | published_at | stock | price |
  4. +----+--------------------------------------+----------------------+---------------------+-------+--------+
  5. | 1 | Designing Data-Intensive Application | Science & Technology | 2018-09-01 00:00:00 | 0 | 100.00 |
  6. +----+--------------------------------------+----------------------+---------------------+-------+--------+
  7. 1 row in set (0.01 sec)
  8. mysql> SELECT * FROM orders;
  9. +------+---------+---------+---------+---------------------+
  10. | id | book_id | user_id | quality | ordered_at |
  11. +------+---------+---------+---------+---------------------+
  12. | 1000 | 1 | 1 | 6 | 2022-04-19 03:18:19 |
  13. | 1001 | 1 | 1 | 4 | 2022-04-19 03:18:17 |
  14. +------+---------+---------+---------+---------------------+
  15. 2 rows in set (0.01 sec)
  16. mysql> SELECT * FROM users;
  17. +----+---------+----------+
  18. | id | balance | nickname |
  19. +----+---------+----------+
  20. | 1 | 9400.00 | Bob |
  21. | 2 | 9600.00 | Alice |
  22. +----+---------+----------+
  23. 2 rows in set (0.00 sec)

An example that prevents overselling

This section describes an optimistic transaction example that prevents overselling. Suppose there are 10 books left in inventory. Bob buys 7 books,and Alice buys 4 books. They place orders almost at the same time. What will happen? You can reuse the code from the optimistic transaction example to address this requirement. Change Bob’s purchases from 6 to 7.

Run the sample program:

  • Java
  • Golang
  • Python
  1. mvn clean package
  2. java -jar target/plain-java-txn-0.0.1-jar-with-dependencies.jar ALICE_NUM=4 BOB_NUM=7
  1. go build -o bin/txn
  2. ./bin/txn -a 4 -b 7 -o true
  1. OPTIMISTIC=True ALICE=4 BOB=7 python3 txn_example.py
  1. /* txn 1 */ BEGIN OPTIMISTIC
  2. /* txn 2 */ BEGIN OPTIMISTIC
  3. /* txn 2 */ SELECT * FROM `books` WHERE `id` = 1 FOR UPDATE
  4. /* txn 2 */ UPDATE `books` SET `stock` = `stock` - 4 WHERE `id` = 1 AND `stock` - 4 >= 0
  5. /* txn 2 */ INSERT INTO `orders` (`id`, `book_id`, `user_id`, `quality`) VALUES (1001, 1, 1, 4)
  6. /* txn 2 */ UPDATE `users` SET `balance` = `balance` - 400.0 WHERE `id` = 2
  7. /* txn 2 */ COMMIT
  8. /* txn 1 */ SELECT * FROM `books` WHERE `id` = 1 FOR UPDATE
  9. /* txn 1 */ UPDATE `books` SET `stock` = `stock` - 7 WHERE `id` = 1 AND `stock` - 7 >= 0
  10. /* txn 1 */ INSERT INTO `orders` (`id`, `book_id`, `user_id`, `quality`) VALUES (1000, 1, 1, 7)
  11. /* txn 1 */ UPDATE `users` SET `balance` = `balance` - 700.0 WHERE `id` = 1
  12. retry 1 times for 9007 Write conflict, txnStartTS=432619094333980675, conflictStartTS=432619094333980676, conflictCommitTS=432619094333980678, key={tableID=126, handle=1} primary={tableID=114, indexID=1, indexValues={1, 1000, }} [try again later]
  13. /* txn 1 */ BEGIN OPTIMISTIC
  14. /* txn 1 */ SELECT * FROM `books` WHERE `id` = 1 FOR UPDATE
  15. Fail -> out of stock
  16. /* txn 1 */ ROLLBACK

You can see from the above SQL log that txn 1 is retried on the application side due to a write conflict in the first execution. By comparing the latest snapshots, you can find that the stock is running out. The application side throws out of stock, and ends abnormally.

  1. mysql> SELECT * FROM books;
  2. +----+--------------------------------------+----------------------+---------------------+-------+--------+
  3. | id | title | type | published_at | stock | price |
  4. +----+--------------------------------------+----------------------+---------------------+-------+--------+
  5. | 1 | Designing Data-Intensive Application | Science & Technology | 2018-09-01 00:00:00 | 6 | 100.00 |
  6. +----+--------------------------------------+----------------------+---------------------+-------+--------+
  7. 1 row in set (0.00 sec)
  8. mysql> SELECT * FROM orders;
  9. +------+---------+---------+---------+---------------------+
  10. | id | book_id | user_id | quality | ordered_at |
  11. +------+---------+---------+---------+---------------------+
  12. | 1001 | 1 | 1 | 4 | 2022-04-19 03:41:16 |
  13. +------+---------+---------+---------+---------------------+
  14. 1 row in set (0.00 sec)
  15. mysql> SELECT * FROM users;
  16. +----+----------+----------+
  17. | id | balance | nickname |
  18. +----+----------+----------+
  19. | 1 | 10000.00 | Bob |
  20. | 2 | 9600.00 | Alice |
  21. +----+----------+----------+
  22. 2 rows in set (0.00 sec)