I want to say something about mysql for Update

Mysql for update: select…. When the corresponding SQL is executed for UPDATE, the database locks the corresponding row. The corresponding lock is pessimistic and readable. However, it has not been applied to practical work and study.

A few days ago, it was not Double Eleven. The company system needed to process a large number of order data. However, in such a high-concurrency scenario, some orders had problems in the delivery status. It is clear that some orders have been shipped, how the order is still in the delivery, after checking the conclusion, are caused by the disaster.

Now that we have found the root of the problem, how can we solve it? As we all know, the general strategy is to lock, of course, there are many kinds of locks, so I won’t bother here. Synchronized and Redis distributed locks (setNx) were initially considered, but a senior at the company said they were too heavy and recommended mysql’s for Update.

Later, I questioned his solution based on specific business scenarios. For update, if both transactions execute select… For update, should not be successful, but how to lock? Later he said that both transactions execute select at the same time… For update, only one transaction succeeds, and the other one waits until the row lock is acquired.

After all, senior is senior, also dare not refute much. In fact, the heart is still a little doubt, so with doubt carried out the following experiment, to completely break their own heart of doubt.

Preparation:

DROP TABLE IF EXISTS account;

CREATE TABLE account (

Id bigint(19) NOT NULL AUTO_INCREMENT COMMENT ‘iD ‘,

Name varchar(128) CHARACTER SET utf8MB4 COLLATE UTf8 NOT NULL COMMENT ‘username ‘,

Money decimal(10, 2) NOT NULL COMMENT ‘account balance ‘,

flag varchar(255) CHARACTER SET utf8 COLLATE utf8 NOT NULL DEFAULT ‘123’,

PRIMARY KEY (id) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

INSERT INTO account VALUES (1, ‘A’, 800.00, ’12’);

Mysql client: start transaction;

select * from account where id=1 for update;

Mysql Client 2:

start transaction;

select * from account where id=1; SQL > select * from client where id = 1;

select * from account where id=1 for update; At this point, client 2 is always blocked.

Client 1: commit;

Client 2: You can see the query results.