To cut to the chase, let’s talk about my actual business problems:

There is A “bet” feature in the project, where the odds are calculated based on the total amount of money wagered by team A and Team B. So when a user bets on one side, the odds on both sides change accordingly.

As reflected in the database (simplified version), A betting person will change several fields in the database table: team A odds, team A bet amount, team B odds, team B bet amount and so on.

Using the @Transactional annotation will cause updates to be lost if you use the default Transactional mode. Missing updates: Updates from one transaction overwrite updates from other transactions. For example, if A reads the bet amount of 1000 and calculates it, B also reads the bet amount of 1000. A writes the calculated 1200 into the database. Finally, B writes the calculated 1100 into the database. In the end, the bet amount was only 1100, which was lost and updated). If there really is a high concurrency situation, with tens or hundreds of people betting every second, this problem has to be addressed.

The default transaction cannot be resolved, so of course a solution must be sought. Either optimistic or pessimistic locking can be used.

A pessimistic lock solution

Important: A row lock (also known as write lock or X lock) is added to read data every time. The lock is released only after the transaction is finished.

Note: Mysql uses the InonDB engine, by default, add, delete, and modify the row lock. Read without row lock.

  • Create a select statement with a “for update” at the end of it:
Note :InnoDB only uses row-level locks when retrieving data by index criteria. Otherwise, InnoDB will use table locks, i.e. InnoDB rows are indexed!
SELECT * FROM table_name WHERE xxx FOR UPDATE;
Update the data after the second logical processing
UPDATE xxx...
Copy the code
@Query(value = "SELECT * FROM guessing_handicap WHERE handicap_id = ? 1 FOR UPDATE", nativeQuery = true)
GuessingHandicap getBet(Integer id);
Copy the code

The implementation is very simple, the understanding of the concept is written later.

Optimistic locking solutions

Optimistic locking is typically implemented using a version number mechanism or CAS algorithm

  • Version number mechanism: A version number field is added to the data table, indicating the number of times the data is modified. When the data is modified, the version value increases by 1. When data is read, the system also reads the version value. When submitting an update, the system updates the version value only when the version value that was just read is the same as the version value in the current database. Otherwise, the update operation is retried until the update succeeds.
int count = 0; // Count the number of repetitions, tentatively 10while(count < 10) { count++; / / read the data first, save the version number GuessingHandicap handicap. = guessingHandicapDao getBet (id); Integer version = handicap.getVersion(); // Process the data //... / / write processed results back to the database Integer rows = guessingHandicapDao. UpdateHandicap (...). ;if (rows == 0) {
        continue;
    }
    // ...
}
throw new ValidationException("Bet failed.");
Copy the code
  • CAS algorithm: Compare and swap is a well-known lock-free algorithm.

The concept of CAS is a little complicated, for example, a simple way to implement it: it is still a betting table. When I read the data, I read the bet amount and odds of the record, and save the data temporarily. Update XXX set odds = new where odds = original

- The CAS algorithm also has disadvantages, the most obvious and easily understood being that it can lead to ABA problems. - If A variable V is A value when it is first read and is still A value when it is ready to assign, can we say that its value has not been modified by other threads? Obviously not, because during that time its value could be changed to something else and then back to A, and the CAS operation would assume that it had never been changed. This problem is known as CAS manipulation"ABA"The problem.Copy the code

Therefore, the version number mechanism is recommended for optimistic locking. Just add a field, easy and easy.

Two types of lock usage scenarios

From the introduction of the two kinds of lock, we know that the two kinds of lock have their own advantages and disadvantages, can not be considered better than the other kind, for example, optimistic lock is suitable for the situation of less write (multi-read scenario), that is, conflict is really rare, this can save the lock overhead, increase the overall throughput of the system. However, in the case of overwrite, conflicts often arise, which can cause the upper application to be repeatedly retry, thus reducing performance. Pessimistic locking is suitable for overwrite scenarios.

Remember the conclusion: Optimistic locking works with less write (read more); Pessimistic locking is suitable for multi-write scenarios.

Three, what is pessimistic lock and optimistic lock (concept understanding)

The optimism lock corresponds to people who are optimistic in their lives thinking about things going good, and the pessimism lock corresponds to people who are pessimistic in their lives thinking about things going bad. Both kinds of people have their own advantages and disadvantages. One kind of people is better than the other.

Pessimistic locking

Always assume the worst, every time to fetch the data that people will change, so every time when take data will be locked, so people want to take this data will be blocked until it got locked (Shared resources to only one thread at a time using, other threads blocked, after use to transfer resources to other threads). Traditional relational database inside used a lot of this locking mechanism, such as row lock, table lock, read lock, write lock, etc., are in the operation before the first lock. Exclusive locks such as synchronized and ReentrantLock in Java are implementations of the pessimistic locking idea.

Optimistic locking

Always assume the best case, every time I go to get the data, I think others will not modify it, so I will not lock it, but when updating, I will judge whether others have updated the data during this period, which can be achieved by using the version number mechanism and CAS algorithm. Optimistic locks are suitable for multi-read applications to improve throughput. Optimistic locks are provided by databases similar to write_condition. In Java. Java util. Concurrent. Atomic package this atomic variable classes is to use the optimistic locking a way of implementation of CAS.

Some confounding concepts are added

  • From the granularity of locks, we can divide database locks into two categories: table locks and row locks,

  • Table locks are divided into table read locks and table write locks.

  • Row lock is divided into shared lock and exclusive lock, and shared lock, exclusive lock and other nicknames, in fact, just different names

    • Shared lock — read lock –S lock
    • Exclusive lock — write lock –X lock

  • InnoDB also has two types of Intention Locks for internal use, both of which are table Locks:

    • Intended shared lock (IS) : a transaction that intends to assign a shared lock to a row must acquire an IS lock on that table before assigning a shared lock to a row.
    • Intentional exclusive lock (IX) : a transaction that intends to lock a row exclusively must acquire an IX lock on the table before it can lock a row exclusively.
    • Carefully comb through again, the concept is still quite clear, and the intent lock is database implicit help us to do, do not need us to care!

Understanding of read-only transactions

There are various opinions on the Internet: “Read-only transactions” is not a mandatory option, it is just a “suggested”, prompt database driver and database system, the transaction does not contain changes the operation of the data, so the JDBC driver and the database can according to this kind of situation to some specific optimization of the transaction, say not to arrange the corresponding database lock, To reduce the stress of transactions on the database, after all, transactions also consume database resources. Therefore, “read-only transactions” is only a recommended configuration for performance optimization, not mandatory.

@Transactional(readOnly = true)
Copy the code

Notes for read-only transactions:

  • Cannot add, modify, or delete a read-only transaction. Otherwise, Cannot execute Statement in a READ ONLY transaction.
  • In a read-only transaction, only the contents before the execution point in time can be read.
  • Read-only transactions are used as a code for ORM framework optimizations, such as lock abandonment or flush never.
  • Read-only transactions also have the disadvantage of dynamically generating proxy classes and increasing overhead.

Summary of application scenarios:

  1. If a single query statement is executed at a time, there is no need to enable transaction support. The database supports read consistency during SQL execution by default.
  2. If an execute multiple queries, such as query, statistics report query, under this scenario, multiple query SQL must ensure that the overall read consistency, otherwise, after the SQL query of the preceding article, the article after the SQL query, data changes by other users, then the total time of statistics query will be read data inconsistent state, at this time, Transaction support should be enabled.
  3. If you want to make your code elegant, you can follow the previous two steps to add read-only transactions, or annotate all of them if that’s too much trouble.

Longpeng Reed Technology Java development engineer

Reed Technology – Guangzhou professional software outsourcing service company

Provide professional services such as wechat mini program, APP application research and development, UI design, etc., focusing on Internet product consulting, brand design, technology research and development, etc.

Visit www.talkmoney.cn to learn more

Universal manual | early diary Lite | | bump wallpaper goods