Bug #21293 Deadlock detection prefers to kill long running FOR UPDATE queries
Submitted: 26 Jul 2006 10:19 Modified: 20 Jun 2010 17:21
Reporter: Domas Mituzas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:4.1,5.0-bk,5.1-bk OS:Any
Assigned to: Vasil Dimov CPU Architecture:Any
Triage: D5 (Feature request)

[26 Jul 2006 10:19] Domas Mituzas
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?
[26 Jul 2006 15: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 15: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 14: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 14: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 14:42] Heikki Tuuri
Domas,

ok, I have now raised this to S2.

--Heikki
[16 Apr 2007 11:51] Heikki Tuuri
This is a feature request.
[12 Jul 2007 17:57] Timothy Smith
Queued to 5.1-maint team tree(s)
[19 Jul 2007 15:48] Bugs System
Pushed into 5.1.21-beta
[30 Jul 2007 16: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.
[5 May 2010 15:13] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 2:21] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 5:49] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:18] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:46] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 23:29] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 11:49] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:27] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:14] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)