Shulou(Shulou.com)05/31 Report--

This article mainly introduces the MySQL concurrency when the classic common deadlock reasons are, have 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.

1. What locks does mysql have?

MySQL has three levels of locks: page level, table level, and row level.

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

Row-level locks: expensive and slow to add locks; deadlocks occur; locking granularity is the smallest, the probability of lock conflicts is the lowest, and the degree of concurrency is the highest.

Page lock: the overhead and locking time are between table lock and row lock; deadlocks occur; lock granularity is between table lock and row lock, and the concurrency is average.

Algorithm:

Next KeyLocks lock, simultaneously locking the record (data), and locking the Gap in front of the record

Gap lock, do not lock the record, only record the previous Gap

Recordlock lock (lock data, not Gap)

So in fact, Next-KeyLocks=Gap lock + Recordlock lock

two。 Under what circumstances will it cause a deadlock

Deadlock: refers to the execution of two or more processes

A phenomenon of mutual waiting caused by competition for resources, without external force, they will not be able to move forward.

At this point, it is said that the system is in a deadlock state or that the system has a deadlock. These processes that are always waiting for each other are called deadlock processes.

Table-level locks do not produce deadlocks. Therefore, the solution to deadlock is mainly aimed at the most commonly used InnoDB.

The key to deadlocks is that two (or more) Session locks are not in the same order.

Then the corresponding key to solve the deadlock problem is to make different session locks in order.

3. Some common deadlock cases

Case 1:

Demand: divide the investment money into several pieces and randomly distribute it to the borrower.

At first, the business program thought like this:

Investors invest, the amount will be randomly divided into several, and then randomly selected from the borrower table, and then through a select for update to update the balance in the borrower table and so on.

The abstract is that a session through the for loop will have several statements like this:

Select * from xxx where id=' random id' for update

Basically, the program deadlocks soon after it is opened.

This can be said to be the most classic deadlock situation.

For example, if two users invest at the same time, the amount of user An is randomly divided into two parts and distributed to the borrower.

B the amount of user is randomly divided into two parts, which are distributed to the borrower.

Deadlocks occur very quickly, of course, because of the different order of locking.

The improvement to this problem is as simple as locking up all assigned borrowers at once.

Select * from xxx where id in (xx,xx,xx) for update

The list value mysql in in is automatically sorted from small to large, and locks are also added from small to large.

For example, (the following session id is the primary key): Session1:mysql > select * from T3 where id in (8pm 9) for update +-+ | id | course | name | ctime | +-+ | 8 | WA | f | 2016-03-02 11:36: 30 | | 9 | JX | f | 0-03-01 11:36:30 | +-+ 2 rows in set (0.04 sec) Session2:select * from T3 where id in (2016) 5) for update Waiting for the lock. In fact, the record of id=10 is not locked at this time, but the record of id=5 has been locked, and the lock is waiting here in id=8. See Session3:mysql > select * from T3 where id=5 for update; lock waiting Session4:mysql > select * from T3 where id=10 for update +-+ | id | course | name | ctime | +-+ | 10 | JB | g | 2016-03-10 11:45: 05 | +-+ 1 row in set (0.00 sec) id=5 cannot be locked in other session But id=10 can be locked.

Case 2:

In development, this kind of judgment is often done: query based on field value (with index), insert if it does not exist, otherwise update.

Taking id as the primary key, there is no id=22 line Session1:select * from T3 where id=22 for update;Empty set (0.00 sec) session2:select * from T3 where id=23 for update;Empty set (0.00 sec) Session1:insert into T3 values. Session2:insert into T3 values (23); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

When there is a row lock (primary key), mysql only has a row lock.

When locking a row that does not exist (even if the condition is a primary key), mysql will lock a range (with a gap lock)

The range of locks is:

(infinitesimal or less than the maximum value of locked id in the table, infinity or greater than the minimum value of locked id in the table)

For example, if the existing id in the table is (11,12)

Then lock it (12, infinity)

If the existing id in the table is (11,30)

Then lock it (115.30)

The solution to this deadlock is:

Insert into T3 (xx,xx) on duplicate key update `xx` = 'XX'

Use mysql-specific syntax to solve this problem. Because the insert statement for the primary key, the inserted row will only have a row lock, whether it exists or not.

Case 3:

Go directly to the situation:

Mysql > select * from T3 where id=9 for update +-+ | id | course | name | ctime | +-+ | 9 | JX | f | 2016-03-01 11:36: 30 | +-+ 1 row in set (0.00 sec) Session2:mysql > select * from T3 where id insert into T3 values (7 'ae','a',now ()) ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

This is similar to the other cases, except that session1 doesn't play the cards according to common sense.

Session2 is waiting for Session1's id=9 to lock, and session2 holds locks from 1 to 8 (note that the range of 9 to 19 is not locked by session2). Finally, session1 has to wait for session2 when inserting a new row, so a deadlock occurs.

This generally does not occur in business requirements, because you lock the id=9, but want to insert the row of id=7, which is a bit jump, of course, there must be a solution, that is to re-manage business requirements and avoid this way of writing.

Thank you for reading this article carefully. I hope the article "what are the common reasons for deadlocks in MySQL concurrency" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.