Produce a deadlock problem recently, is very difficult, hope your bosses to help analyze the deadlock causes
Note:
ID primary key on the
Status index, the initial value for 1, 2, 3, 4 failure, 5 expired
Priority index, the present value of 1
UniqueNumber unique index, 32-bit md5 value
1. The default RR InnoDB repeatable read level
2. Two examples of four the same thread, thread execute the following order
(1)
Select
The Content, Priority, UniqueNumber
The from t
The where (Status=1 or Status=4)
The order by Priority
Asc limit 0100
For update
(2)
The update t set Status=2
Where UniqueNumber='32-bit md5'
(3)
If the overdue update Status=5
If the update failure Status=4
If successful, update the Status=3

One (1) (2) in the same transaction, because (1) (2) in the same way with @ transaction annotations
(1) (2) the multithreading serial, followed by multi-thread parallel

See the mysql deadlock log found
(1) and (2) a deadlock occurs, as shown in figure, true not understand


Consider the causes and lock gap, but in the real world cannot emersion
For years, help me emersion the, thank you very much

CodePudding user response:

CodePudding user response:

Statements (1), you give the first 100 lines of status=1 or 4 plus the write lock, and at the end of the transaction will unlock the lock,
If you use FOR UPDATE with a storage engine that USES page or row locks, rows examined by the query are write - locked until the end of the current transaction.
Statement (2), what would you like to put the UniqueNumber='32-bit md5' status column set to 2,
Because there is UniqueNumber='32-bit md5 and status=1 or4 line has been statement (1) to the write lock, so the statement (2) when they want to change the status of a deadlock,

I guess your misunderstanding may be that statement (1) select the Content, Priority, UniqueNumber didn't choose the status, why is locked,
This is because the Innoob support row locks, lock all the entire line, can not only lock a row of a few columns,

CodePudding user response:

refer to the second floor lpq666 response:

statement (1), before you give the status=1 or 4 100 lines with the write lock, and at the end of the transaction will unlock the lock,
If you use FOR UPDATE with a storage engine that USES page or row locks, rows examined by the query are write - locked until the end of the current transaction.
Statement (2), what would you like to put the UniqueNumber='32-bit md5' status column set to 2,
Because there is UniqueNumber='32-bit md5 and status=1 or4 line has been statement (1) to the write lock, so the statement (2) when they want to change the status of a deadlock,

I guess your misunderstanding may be that statement (1) select the Content, Priority, UniqueNumber didn't choose the status, why is locked,
This is because the Innoob support row locks, lock all the entire line, can not only lock a row of a few columns,

bosses: hello, I tried this kind of situation, this kind of situation the mysql does not produce a deadlock, is waiting for a timeout, I know InooDB default row-level locks
You can see I blink, because points not enough so I won't post, there was a deadlock log images, which statement for next - key lock (2) access to insert intent locks, really don't understand multithreaded situations this can happen, thank you very much!