After the mysql deadlock, do you wait for a timeout, or simply report a wrong rollback, or behave differently under different circumstances?

I read some articles saying that it is necessary to set a reasonable deadlock timeout for deadlock problems. However, some say that InnoDB will automatically detect transaction deadlocks and roll back immediately with an error.

I tried it and found that "InnoDB will automatically detect transaction deadlocks and immediately roll back with an error." It is more consistent with the experimental results.

so after the mysql deadlock, do you wait for a timeout, or simply report a wrong rollback, or behave differently in different situations?

if "InnoDB will automatically detect transaction deadlocks and roll back immediately with an error." Is the correct situation, what is the concept of "deadlock wait timeout" mentioned in many articles?

Jun.07,2022

deadlock timeout should refer to lock waiting time. The parameter is innodb_lock_wait_timeout

.

these two processing methods correspond to different situations

deadlock detection : it is enabled by default. When adding a lock, InnoDB will detect whether it will cause a deadlock, and if so, roll back the transaction with the lowest cost

.

Lock wait timeout : one is to deal with the deadlock that cannot be detected, and the other is to avoid waiting for a normal lock (non-deadlock, possibly a large transaction) for too long


I don't think deadlock is a problem that can be detected 100%, so the deadlock that can be checked must be reported wrong immediately. Isn't it silly to wait for a timeout?

if it is a deadlock that cannot be detected, it is necessary to set a timeout mechanism.


< H1 > query global wait transaction lock timeout < / H1 >

SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

< H1 > set the global wait transaction lock timeout < / H1 >

SET GLOBAL innodb_lock_wait_timeout=100;

< H1 > query the current session waiting for transaction lock timeout < / H1 >

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1bf0061-31447.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1bf0061-31447.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?