MySQL patches by Codership

innodb autoinc lockmodes 0 and 1 cause hanging

Bug #363134 reported by Seppo Jaakola
2
Affects Status Importance Assigned to Milestone
MySQL patches by Codership
New
Undecided
Unassigned
5.5
Confirmed
Wishlist
Seppo Jaakola
MySQL patches by Codership 5.5.38-25.11

Bug Description

having innodb_autoinc_lockmode set to 0 or 1 (default) can cause hanging slave applier thread.
Checking the stack trace shows that slave thread is waiting for autoinc lock.

To repeat, you can start two node cluster (ardennes and batak in this example) and create two tables:

mysql> create table t1 (i int auto_increment primary key, j int);
mysql> create table t2 ( j int);

Table t2 is needed to generate bulk mode insert on t1 later on. Insert one row in t2:
mysql> insert into t2 values (200);

Then start a batch of autocommit insert against node batak:

$ for ((i=0;i<10000;i+=1)); do mysql -uroot -prootpass -hbatak test -e "insert into test.t1 values (0,0)" ; done

using another terminal start "bulk mode" inserts on t1 in node ardennes:

$ for ((i=0;i<10000;i+=1)); do mysql -uroot -prootpass -hardennes test -e"insert into t1 (j) select * from t2" ; done

both connections will hang pretty soon.

Seppo Jaakola (seppo-jaakola)
Changed in codership-mysql:
assignee: nobody → seppo-jaakola
importance: Undecided → High
milestone: none → 0.6
status: New → In Progress
Revision history for this message
Seppo Jaakola (seppo-jaakola) wrote :

Reason for the deadlock is in table level autoinc lock.
Local bulk mode inserts (autocommit trx) grab autoinc lock and keep it until the end of statement.

Slave applier needs to honor this lock and must wait for it. This is a deadlock, because local autocommit insert cannot proceed past commit phase before slave applier has released TO mutex.

Workaround is to set innodb_autoinc_lock_mode=2. This avoids using autoinc locking altogether.

In the test cluster, hanging does not reproduce with autoinc lock mode 2.

Revision history for this message
Alex Yurchenko (ayurchen) wrote : Re: [Bug 363134] Re: innodb autoinc lockmodes 0 and 1 cause hanging

Why local autocommit inserts keep autoincrement lock until after commit?
Just to be able to rollback the autoinc counter? Do inserts in
transactional mode behave differently? If so, why the difference? Does
innodb_autoinc_lock_mode=2 make autocommit inserts behave like
transactional?

On Fri, 17 Apr 2009 21:48:06 -0000, Seppo Jaakola
<email address hidden> wrote:
> Reason for the deadlock is in table level autoinc lock.
> Local bulk mode inserts (autocommit trx) grab autoinc lock and keep it
> until the end of statement.
>
> Slave applier needs to honor this lock and must wait for it. This is a
> deadlock, because local autocommit insert cannot proceed past commit
> phase before slave applier has released TO mutex.
>
> Workaround is to set innodb_autoinc_lock_mode=2. This avoids using
> autoinc locking altogether.
>
> In the test cluster, hanging does not reproduce with autoinc lock mode
> 2.

Revision history for this message
Seppo Jaakola (seppo-jaakola) wrote :

Heikki has it all explained here: http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

Seppo Jaakola (seppo-jaakola)
Changed in codership-mysql:
importance: High → Medium
milestone: 0.6 → 0.6.1
Seppo Jaakola (seppo-jaakola)
Changed in codership-mysql:
milestone: 0.6.1 → 0.7
Seppo Jaakola (seppo-jaakola)
Changed in codership-mysql:
milestone: 0.7 → 0.8
Seppo Jaakola (seppo-jaakola)
Changed in codership-mysql:
status: In Progress → Confirmed
Alex Yurchenko (ayurchen)
Changed in codership-mysql:
milestone: 0.8 → none
milestone: none → 0.8
milestone: 0.8 → none
Revision history for this message
Seppo Jaakola (seppo-jaakola) wrote :

autoinc lockmode 1 can cause a crash with 2.0 wsrep-5.5 build with master-slave test

The crash should be fixed to begin with.

After that, there are optional strategies to deal with this:
1. Allow only lock mode2, and enhance wsrep option checking to always reject all but value 2 for autoinc lock mode
2. Allow lock modes 0 and/or 1 conditionally in master slave use case
3. Allow lock modes 0 and/or 1 always

Unfortunately it is not possible to distinguish between MS and MM topologies inside the node, so cases 2 & 3 will require some more planning

Alex Yurchenko (ayurchen)
no longer affects: codership-mysql/0.8
Changed in codership-mysql:
milestone: 5.5.33-23.7.6 → none
Alex Yurchenko (ayurchen)
Changed in codership-mysql:
assignee: Seppo Jaakola (seppo-jaakola) → nobody
importance: Wishlist → Undecided
status: Confirmed → New
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.