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

This article mainly introduces "case analysis of mysql deadlock problem". In daily operation, I believe that many people have doubts about the case analysis of mysql deadlock problem. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "case analysis of mysql deadlock problem". Next, please follow the editor to study!

I. description of the problem:

The non-index field of a table is updated, and the table is locked in a concurrent environment, resulting in deadlock!

2. Brief analysis:

Two transactions, one transaction, is performing an index field update sql, and the other transaction happens to do a primary key conditional update, as well as a non-index update. Causes two transactions to cross-wait for the lock to be released, so the deadlock problem occurs.

Third, data simulation:

Create a table:

Create table user (

Id int (10) NOT NULL AUTO_INCREMENT

User_id varchar (20)

Id_card_no varchar (50)

Name varchar (30)

Password varchar (30)

Sex char (1)

Birthday date

PRIMARY KEY (`id`)

INDEX index_name (user_id (20))

);

Insert two pieces of data

Insert into user (user_id,id_card_no,name,password,sex,birthday) values (1993-05-27')

Insert into user (user_id,id_card_no,name,password,sex,birthday) values ('000002', '002', '123','53'))

IV. Recurrence of problems

Step 1: start a transaction 1, and first perform a primary key update

Begin

Update user set name = "test transaction by zz" where id = 1

Step 2: open another transaction 2 and execute another primary key update statement

Begin

Update user set name = "test transaction by zz" where id = 2

Step 3: transaction 1 performs a non-indexed field update and does not commit the transaction

Update user set name = "test transaction by zz name" where user_id = '000001'

Step 4: transaction 2 is performing a non-indexed field update, do not commit the transaction

Update user set name = "test transaction by zz name" where user_id = '000002'

Running result: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction.

4. the results of the analysis:

1. Two transactions, cross waiting for each other to release the lock, there will be a deadlock.

two。 As in the above operation, transaction 1 waits for transaction 2 to release row locks while performing sql2, and transaction 2 may also update non-indexed fields at this time, and need to wait for transaction 1 to release table locks, which leads to deadlocks.

3. When the primary key is updated, only the index is locked, which is the concept of row-level lock; when the index field is updated or deleted, the corresponding primary key record is found according to the index field, and then locked; non-primary key, the update and deletion of non-index fields will lock the whole table by default, which is the concept of table-level lock.

Fifth, optimization and suggestions:

1. Avoid updating and deleting non-indexed fields.

two。 If there is such an update, it is recommended to make an out-of-transaction query and then update it according to the index field, so that the table will not be locked.

3. Index the corresponding fields, but there should not be too many indexes in a table, which will affect the performance of the database. It is best to have less than four. Queries that exceed 100ms need to be optimized. However, indexing is not the best solution. When you update the index, you will find the corresponding primary key of the index first, and then lock it. There is also a little performance problem. But to meet most of the business needs.

4. The system needs an abnormal situation to have a retry mechanism, for database such as deadlock problems, generally appear in the concurrency, but also when there will be system gaps, at this time there is a retry, it is slightly better. However, for businesses with high timeliness, this problem must not occur.

At this point, the study of "case analysis of mysql deadlock problem" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.