Shulou(Shulou.com)06/02 Report--

This article mainly shows you "MySQL pessimistic lock and optimistic lock how to achieve", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "MySQL pessimistic lock and optimistic lock how to achieve" this article.

Preface

Pessimistic lock and optimistic lock are two ideas used to solve concurrency problems, which have their own implementation on different platforms. For example, in Java, synchronized can be thought of as the implementation of pessimistic locks (lax, there is a lock upgrade process, upgrading to heavyweight locks), and the Atomic*** atomic class can be regarded as the implementation of optimistic locks.

Pessimistic lock

It has strong exclusive and exclusive characteristics, and the data is locked in the whole processing process, which is generally realized by the mutex of the system. It is blocked when other threads want to acquire the lock until the thread holding the lock releases the lock.

Optimistic lock

Be optimistic about data modification and access, assuming that there will be no conflicts. Data conflicts will be detected only when the data is submitted for update, and if there is no conflict, the update will be submitted smoothly, otherwise it will fail quickly and an error will be returned to the user to choose what to do next. Generally speaking, the user will continue to try again after failure until the update is submitted successfully.

MySQL itself supports the locking mechanism. For example, we have a requirement of "check before writing". We want the whole process to be an atomic operation and cannot be interrupted, which can be achieved by adding an "exclusive lock" to the data rows of the query. As long as the current transaction does not release the lock, MySQL blocks other transactions to acquire the exclusive lock until the current transaction releases the lock. This exclusive lock at the bottom of MySQL is called a pessimistic lock.

MySQL itself does not provide optimistic locking, which needs to be implemented by developers themselves. The common practice is to add a version column to the table to mark the version of the data row. When we need to update the data, we must compare the version version. Version unanimously indicates that the data has not been modified by other transactions during this period, otherwise it means that the data has been modified by other transactions and needs to spin retry.

Actual combat

Suppose the database has two tables: the goods table and the order table.

After the user places an order, two actions need to be performed:

The list of goods minus inventory.

The order table creates a record.

Initial data: items with an ID of 1 have 100th inventory, and the order table data is empty.

The client starts 10 threads to issue orders concurrently, and what is the performance in the scenarios of no lock, pessimistic lock and optimistic lock.

The following is the sql statement to create the table:

-- CREATE TABLE `goods` (`id` bigint (20) NOT NULL AUTO_INCREMENT, `goods_ name` varchar (50) NOT NULL, `price` decimal (10PRIMARY KEY 2) NOT NULL, `stock`int (11) DEFAULT '0percent, `version`int (10) unsigned NOT NULL DEFAULT' 0percent, PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8-- order form CREATE TABLE `torder` (`id` bigint (20) NOT NULL AUTO_INCREMENT, `goods_ id` bigint (20) NOT NULL, `order_ time` datetime NOT NULL) PRIMARY KEY (`id`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf81, no lock

Don't do anything.

/ / place an order private boolean order () {Goods goods = goodsMapper.selectById (1L); boolean success = false; if (goods.getStock () > 0) {goods.setStock (goods.getStock ()-1); / / update inventory goodsMapper.updateById (goods); / / create order orderMapper.save (goods.getId ()); success = true;} return success;}

Console output result:

2. Pessimistic lock

Add FOR UPDATE when querying items and exclusive locks on data rows, so that other threads will be blocked when querying again until the current thread's transaction commits and releases the lock. The concurrency performance is not high in this way.

Sql statement

@ Select ("SELECT * FROM goods WHERE id = # {id} FOR UPDATE") Goods selectForUpdate (Long id)

Console output result:

Note: FOR UPDATE must be valid in a transaction, query and update must be in the same transaction!

3. Optimistic lock

The implementation idea is to check the version number each time it is updated. If the version number indicates that the data has not been changed by other threads during the period, the current thread can submit the update normally, otherwise, the data has been changed by other threads. The current thread needs to spin and retry until the business succeeds.

Update the data at the same time the version number must increase itself!

@ Update ("UPDATE goods SET stock = # {stock}, version = version+1 WHERE id = # {id} AND version = # {version}") int updateByVersion (Long id, Integer stock, Integer version)

Business code

Boolean order () {Goods goods = goodsMapper.selectById (1L); boolean success = false; if (goods.getStock () > 0) {goods.setStock (goods.getStock ()-1); / / update inventory with version number int result = goodsMapper.updateByVersion (goods.getId (), goods.getStock (), goods.getVersion ()); if (result)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.