Recently discovered in watching row lock locks you mentioned clearance is transaction between incompatible , because is not mutually exclusive but also easily lead to a deadlock,
But my own test, old feel the mutex sometimes not mutually exclusive, here are a I think the mutex example :
Table structure and data:

 # transaction to perform A 
The BEGIN. 
SELECT the id FROM test_lock WHERE id & gt; 2 and id & lt;=3 FOR UPDATE. 
- the ROLLBACK; # uncommitted 

 # transactions executed after B 
The BEGIN. 
SELECT the id FROM test_lock WHERE id & gt; 2 and id & lt;=3 FOR UPDATE. Block B # affairs here 
ROLLBACK; 

For table test_lock transaction with A key in the lock (the next key lock) lock (1, 5] interval, transaction B should also lock with A range of ,
if the clearance is not mutually exclusive lock, why transaction B in implementing blocking happens?
Feel sure what went wrong on cognition, oneself a person want to really don't understand, hope leaders help point out mistakes!

CodePudding user response:

Here again are not mutually exclusive example:

 
# affairs A 
The BEGIN. 
SELECT the id FROM test_lock WHERE id=3 FOR UPDATE. 

 
# transaction B 
The BEGIN. 
SELECT the id FROM test_lock WHERE id=3 FOR UPDATE. # does not block 
ROLLBACK;