MySQL Bugs: #21293: Deadlock detection prefers to kill long running FOR UPDATE queries

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

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?

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);
                                }

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.

Domas,

I think Guilhem implemented a retry to replication in a deadlock situation. That was about 2 years ago.

Regards,

Heikki

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.

Domas,

ok, I have now raised this to S2.

--Heikki

This is a feature request.

Queued to 5.1-maint team tree(s)

Pushed into 5.1.21-beta

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.

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

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)

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

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)

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)

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)