| Bug #21293 | Deadlock detection prefers to kill long running FOR UPDATE queries | ||
|---|---|---|---|
| Submitted: | 26 Jul 2006 12:19 | Modified: | 30 Jul 2007 18:23 |
| Reporter: | Domas Mituzas | ||
| Status: | Closed | ||
| Category: | Server: InnoDB | Severity: | S4 (Feature request) |
| Version: | 4.1,5.0-bk,5.1-bk | OS: | Any |
| Assigned to: | Vasil Dimov | Target Version: | |
| Triage: | D5 (Feature request) | ||
[26 Jul 2006 17:21]
Heikki Tuuri
Domas,
InnoDB tries to save the transaction that has updated, deleted, or inserted the biggest
number of rows. That unfortunately means that pure SELECT ... FOR UPDATE transactions will
end up as the victim.
To adequately solve this, we should introduce a settable priority for a transaction.
In the meantime, a clumsy workaround is to run some big dummy insert + delete in your
transaction first, and only after that proceed to do the actual work. The dummy operation
will raise the priority of your transaction if a deadlock is encountered.
Regards,
Heikki
lock0lock.c:
if (ut_dulint_cmp(wait_lock->trx->undo_no,
start->undo_no) >= 0) {
/* Our recursion starting point
transaction is 'smaller', let us
choose 'start' as the victim and roll
back it */
return(LOCK_VICTIM_IS_START);
}
[26 Jul 2006 17:25]
Domas Mituzas
One of serious problems with this is that in case of INSERT INTO ... SELECT or REPLACE INTO ... SELECT if the target table is MyISAM, InnoDB interrupts the SELECT query, and partially-executed statement is written into binary log with error code attached.
[4 Jan 2007 15:35]
Heikki Tuuri
Domas, I think Guilhem implemented a retry to replication in a deadlock situation. That was about 2 years ago. Regards, Heikki
[4 Jan 2007 15:40]
Domas Mituzas
The problem is not a retry, but InnoDB interrupting MyISAM operation in the middle, because of lack of rows edited. There were discussions about using ha::extra() to fetch such information somehow. It could make sense to check if a query has edited non-transactional tables before killing it. It is a bug, as it causes data inconsistencies in replication.
[4 Jan 2007 15:42]
Heikki Tuuri
Domas, ok, I have now raised this to S2. --Heikki
[16 Apr 2007 13:51]
Heikki Tuuri
This is a feature request.
[12 Jul 2007 19:57]
Timothy Smith
Queued to 5.1-maint team tree(s)
[19 Jul 2007 17:48]
Bugs System
Pushed into 5.1.21-beta
[30 Jul 2007 18:23]
Paul DuBois
Noted in 5.1.21 changelog. When determining which transaction to kill after deadlock has been detected, InnoDB now adds the number of locks to a transaction's weight, and avoids killing transactions that mave modified non-transactional tables. This should reduce the likelihood of killing long-running transactions containing SELECT ... FOR UPDATE or INSERT/REPLACE INTO ... SELECT statements, and of causing partial updates if the target is a MyISAM table.
Description: Long SELECT .. FOR UPDATE or INSERT/REPLACE ... SELECT queries, which establish locks on data reads, may be killed by deadlock detector, if there're any other transactions, which edit source data. In concurrent environments this may make INSERT ... SELECT unusable, as locks not obtained atomically may have other transaction locks interrupted. How to repeat: # # Test of Locking # --disable_warnings drop table if exists t1,t2; --enable_warnings create table t1 ( a int primary key ) engine=InnoDB; create table t2 ( a int ) engine=MyISAM; insert into t1 values (1),(2),(3); #send insert into t2 select * from t1 where a > sleep(1); send select * from t1 where a > sleep(1) for update; --connect (othertransaction,,,,) begin; delete from t1 where a = 3; --sleep 2 send delete from t1 where a = 1; connection default; reap; Suggested fix: roll back the other transactions instead the jumbo ones?