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

This article mainly introduces the Mysql lock mechanism in the row lock, table lock, deadlock how to achieve, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor with you to understand.

What is the Mysql lock? What are the types of locks?

Lock definition:

The same resource can only be accessed by one thread at a time

In the database, in addition to the contention of traditional computing resources (such as CPU, Imax O, etc.), data is also a kind of resource shared by many users. How to ensure the consistency and effectiveness of data concurrent access is a problem that must be solved in all databases, and lock conflicts are also an important factor affecting the performance of database concurrent access.

Optimistic lock is mostly used to record the version of the data to reflect the version, which is actually a logo.

For example: update test set a=a-1 where id=100 and a > 0; the corresponding version is a field. It is not necessary to have a field called version. It is required to have this field, and it will be triggered only when this condition is met.

Classification of locks:

Divide (read or write) from the type of data operation

Read lock (shared lock): for the same data, multiple reads can be performed at the same time without affecting each other.

Write lock (exclusive lock): it blocks other write and read locks before the current write operation is completed.

From the granularity method of data operation

Table-level lock: a table-level lock is the largest lock granularity in MySQL, which means to lock the entire table of the current operation (the MyISAM engine defaults to table-level locks, and only table-level locks are supported). For example, to update a piece of data in a 100000 table, before this update commits a transaction, other transactions will be excluded and the granularity is very large.

Row-level lock: a row-level lock is the finest lock granularity in Mysql, indicating that only rows of the current operation are locked (implemented based on index, so once a locking operation does not use an index, the lock will degenerate to a table lock).

Page-level lock: a page-level lock is a lock whose granularity is between row-level lock and table-level lock in MySQL, locking a group of adjacent records at a time.

Distribution from the perspective of concurrency-- in fact, optimistic locks and pessimistic locks are just an idea.

Pessimistic lock: it is conservative (pessimistic) that the data is modified by the outside world (including other transactions of the system, as well as transactions from external systems). Therefore, the data is locked during the whole data processing process.

Optimistic lock: optimistic lock assumes that the data will not cause conflict in general, so when the data is submitted for update, the conflict of the data will be formally detected. If a conflict is found, then the error message is returned and the business is retried.

Other locks:

GAP lock: in conditional queries, for example, where id > 100 InnoDB will lock the index entries of existing data records that meet the conditions; for records whose key values are within the range of conditions but do not exist, it is called "gap". The purpose of gap is to prevent false reading.

Intention lock: intention lock is divided into intention shared lock (IS) and intention exclusive lock (IX). The purpose of intention lock is to indicate that a transaction is locking or is about to lock rows in a table.

Second, the difference between row locks and table locks

Table-level lock is the largest lock granularity in MySQL, which means locking the whole table of the current operation. It is easy to implement. Both MYISAM and INNODB, the most commonly used, support table-level locking.

Features: low overhead, fast locking; no deadlock; large locking granularity, the highest probability of lock conflict and the lowest concurrency.

Row-level locks are the finest-grained locks in Mysql, indicating that locks are only applied to the rows of the current operation. Row-level locks can greatly reduce conflicts in database operations. The locking granularity is the smallest, but the locking overhead is also the largest.

Features: high overhead, slow locking, deadlock; minimum lock granularity, lowest probability of lock conflict and highest concurrency

Use: InnoDB row locks are achieved by locking index items. InnoDB uses row-level locks only when data is retrieved by index conditions, otherwise InnoDB will use table locks

The following update statement, b is the general field is not an index column, then the row-level lock will be changed to a table-level lock.

Update from test set axiom 100 where baggage 100'

Now let's take a practical example to see how innnodb uses row locks.

Data in the current table:

First open two session session windows, and then set the mysql transaction level to the uncommitted level:

Session window:

Session two window:

Where the update of session 2 is in the Running until the timeout ends, or session 1 commits the transaction until the Running ends.

The lock timeout set by the current mysql can be queried through show VARIABLES like "% innodb_lock_wait_timeout%", which defaults to 50 seconds.

You can set the timeout of the lock by set innodb_lock_wait_timeout = 60;.

After the first session commit, the update statement of the second session executes successfully. This means that innodb used a lock.

Then how can we be sure that a row lock is used?

Summary: when the session updates the id=125, the data is add lock, so when the id=125 is updated again in session 2, the data is in the locked. This lock is added to the id=125 record. At this time, except for id=125, it can be successful, which proves that this one adds a row lock by default.

III. InnoDB deadlock concept and deadlock cases

Definition: deadlocks occur when two or more transactions hold and request locks on each other and form a circular dependency. Deadlocks also occur when multiple transactions lock the same resource at the same time. In a transactional system, deadlocks do exist and cannot be completely avoided.

Resolution: InnoDB automatically detects transaction deadlocks, rolls back one of the transactions immediately, and returns an error. It chooses the simplest (least costly) transaction to roll back according to some mechanism.

Select for update of deadlock scenario 1:

Generation scenario: both transaction have two select for update,transaction a to lock record 1, then record 2, while transaction b locks record 2 first, then record 1

Write lock: for update, read lock: for my share mode show engine innodb status

Verify the deadlock scenario:

Step 1: update session 1:

Start TRANSACTION;select * from wnn_test where axiom 199 for update

Step 2: update session 2:

Start TRANSACTION;select * from wnn_test where aversion 101 for update

Step 3: update session 1:

Select * from wnn_test where aversion 101 for update

Step 4 update session 2

Select * from wnn_test where axiom 199 for update

By the time you update to steps 3 and 4, a deadlock has already occurred.

Take a look at the log of the execution:

Show engine innodb status; the time of the last lock, the table of the lock, the statement that caused the lock. Where session1 is locked for 14 seconds (ACTIVE 14) and session 2 is locked for 10 seconds (Active 10)

Two update in deadlock scenario 2

Generate scenario: both transaction have two update,transaction a, first update record 1, then update record 2, while transaction b first update record 2, then update record 1

Generate logs:

Note: a closer look at the above two examples shows that when two resources are locked, the third will execute successfully, but the fourth will prompt deadlock. In mysql5.7, the third one will always be in the Running state. This blog uses mysql8.0, where innodb_deadlock_detect can be used to control whether InnoDB performs deadlock detection. When deadlock detection is enabled (the default), InnoDB automatically performs deadlock detection of transactions and rolls back one or more transactions to resolve deadlocks. InnoDB attempts to roll back smaller transactions, the size of which is determined by the number of rows of data it inserts, updates, or deletes.

So should this innodb_deadlock_detect parameter be enabled or not?

For systems with high concurrency, deadlock detection may lead to performance degradation when a large number of threads are waiting for the same lock. At this point, it might be more efficient to disable deadlock detection and instead rely on the parameter innodb_lock_wait_timeout to perform a transaction rollback when a deadlock occurs.

In general, deadlock detection should be enabled, deadlocks should be avoided in the application, and deadlocks should be handled accordingly, such as restarting the transaction.

You can try to turn off the innodb_deadlock_detect option only if you confirm that deadlock detection is affecting system performance and that disabling deadlock detection does not have a negative impact. In addition, if InnoDB deadlock detection is disabled, you need to adjust the value of the parameter innodb_lock_wait_timeout to meet the actual requirements.

Fourth, how to avoid deadlock in the process of program development

The essence of lock is that resources compete with each other and wait for each other, often because the locking order of two (or more) Session is inconsistent.

How to effectively avoid:

In a program, when operating multiple tables, try to access them in the same order (avoid forming a waiting loop)

When dealing with single table data in batches, it is easy to lock the data by sorting the data first (to avoid forming a waiting loop) A thread id:1, 10, 20 sequentially locking B thread id:20,10,1.

If possible, the big business can be reduced to small transactions, and the transaction select for update== > insert== > update= insert into update on duplicate key will not even be opened.

Try to use the index to access data, avoid operations without where conditions, and avoid locking the table. The index is a record row lock and the index is a table lock.

Use equivalence query instead of range query to query data, hit records and avoid the impact of gap locks on concurrency. 1Magne10 where id in 20 equivalent query id > 1 and id

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.