Small knowledge, big challenge! This paper is participating in theEssentials for programmers”Creative activities

This article also participated in the “Digitalstar Project” to win a creative gift package and creative incentive money

1. Understanding of locks

Scenario: A system with low access volume that has not been changed for a long time suddenly has a problem due to the increase in access. After log review and code investigation, the reasons are found as follows

  • Table A Primary key: ID, status: status
  • Table B Primary key: ID, version: version, Cumulative total: total

Table A

Table B

/** * This reference program is provided by @jiuzhang.com * Copyright is reserved. Please indicate the source for Forwarding */ -- xiaoming -- start transaction; -... -- update A set status = 2 where id= 1 and status =1; update B set total = total + XXX1 where id = 1 and version = 1; commit; -- Xiao Hong -- Start transaction; -... -- update A set status = 2 where id= 2 and status =1; update B set total = total + XXX2 where id = 1 and version = 1; commit; -- Zhang SAN -- start transaction; ... update A set status = 2 where id= 3 and status =1; update B set total = total + XXX3 where id = 1 and version = 1; commit;Copy the code

The update operation of Table A is the completed update of user order status, while the update operation of Table B is the statistics of business.

It is clear that when concurrency occurs (multiple transactions operating on the same row as table B) the update to table B will be controlled by optimistic lock Version and the code will determine that the update failed and throw an exception causing the user’s update to table A to be rolled back.

The problem is not that optimistic locks are used, but that two unrelated businesses are in one transaction, and statistics and order status updates should be separated from each other.

The above problem leads to the delay of the message consumption of partial order payment completion. It is repeated for many times before being consumed, and the interval is long. Why is the order still not successful when the user thinks that the payment has been deducted?

Here is a summary of MySQL InnoDB locks and application scenarios:

/**
 * This reference program is provided by @jiuzhang.com
 * Copyright is reserved. Please indicate the source for forwarding
 */
​
show variables like '%isolation%';
Copy the code

The transaction isolation level of the databases we use is basically read-committed, and all locks are summarized at this level

/**
 * This reference program is provided by @jiuzhang.com
 * Copyright is reserved. Please indicate the source for forwarding
 */
​
select version(); 
Copy the code

Find InnoDB lock on MySql website according to database version number

MySql 官网

Lock the interpretation of the

Lock: Mechanism by which a computer coordinates concurrent access to a resource by multiple processes or threads.

The importance of locks

In databases, in addition to traditional computing resources (CPU, RAM, I\O, etc.), data is also a resource that can be shared by multiple users.

Therefore, how to ensure the consistency and validity of concurrent data access is a problem that all databases must solve. Lock conflicts are also an important factor affecting concurrent access performance of databases, so locks are especially important for databases.

The disadvantage of the lock

Locking consumes resources. Various operations, including obtaining a lock, checking whether the lock has been released, and releasing the lock, will increase the system overhead.

SHOW VARIABLES like '%isolation%';Copy the code

22. Lock type

1. Type of InnoDB lock increment lock

Auto-inc Locks; Table – level lock)

Query the automatic lock mode of the current database:

/**
 * This reference program is provided by @jiuzhang.com
 * Copyright is reserved. Please indicate the source for forwarding
 */
​
show variables like '%innodb_autoinc_lock_mode%';
Copy the code

  • 0:traditonalEvery timeinsertTable locks are created whenever a statement is executed
  • 1:consecutive simple insertA batch of locks will be acquired, a batch of insert autoincrement sequences will be guaranteed, and locks will be released before inserts. In this mode you will find that when youinsertWhen multiple data is rolled back, the DB does not insert any data, but the ID is increased, which is the default level of the database
  • 2:interleavedDo not lock the table, real-time insert, the highest concurrency, but based on the master and slave replication is not secure, interested can query the advantages and disadvantages of RBR and SBR

The simple INSERT indicates that the number of inserts can be determined by analyzing the insert statement. Insert, insert, insert… Valies (), values (), values (); Corresponding other inserts are Bulk inserts, mixed-mode inserts, etc. The official words are as follows

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

A self-increment lock is a special table-level lock that inserts columns of the AUTO_INCREMENT type into transactions. In the simplest case, if one transaction is inserting records into a table, all other transactions must wait for inserts so that the first row inserted by the transaction is a consecutive primary key value.

case

If there is no auto-add lock, the id is auto-add in the following tableAUTO_INCREMENT = 4

Insert A record zhang Sanfeng, the increment sequence assigned to the id 4, the transaction is not committed

Insert record zhang Cuishan, autoincrement sequence assigned to id 5, transaction not committed

Insert (id = 6); insert (id = 6)

Since no other uncommitted transactions will be queried under the isolation level of read committed transactions, transaction A’s query id>3 will return 4: Zhang Sanfeng, 6: Yin Liting. There will be a transaction in which the data incremented by id is not continuous

In this case, auto-increment locks help solve this problem

  • innodb_autoinc_lock_mode = 0