[MDEV-13713] Unexpected deadlock upon concurrent insert into sequence Created: 2017-09-02  Updated: 2018-04-21  Resolved: 2018-04-21

Status: Closed
Project: MariaDB Server
Component/s: Sequences, Storage Engine - InnoDB
Affects Version/s: 10.3
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Michael Widenius
Resolution: Not a Bug Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-13016 Testing SEQUENCE object phase 2 Closed

 Description   

I am not 100% sure it's a bug, but behavior is strange, so better to check if it's intended this way.

In the test case below,

  • 1st connection (default) starts a transaction;
  • it selects NEXTVAL from an InnoDB sequence;
  • 2nd connection attempts to insert into the sequence and starts waiting;
  • 1st connection, in turn, also attempts to insert into the sequence;
  • 1st connection receives ER_LOCK_DEADLOCK;
  • 2nd connection keeps waiting (till lock_wait_timeout is exceeded, unless 1st connection releases the sequence earlier).

Both before and after ER_LOCK_DEADLOCK processlist says that the 2nd connection is waiting for table metadata lock.

Two things seem strange to me.
First, that the deadlock occurs at all, it would appear that 1st connection should be in full possession of the sequence, if the other one is waiting for the lock.
Second, if deadlock is valid, why the 2nd transaction does not recognize it and keeps waiting.

--source include/have_innodb.inc
 
 
 
create sequence s engine=InnoDB;
 
 
 
begin;
 
select nextval(s);
 
 
 
--connect (con1,localhost,root,,)
 
 
 
--echo # INSERT starts waiting for a metadata lock
 
--send 
  insert into s values (1,1,100000000000000,1,1,1000,0,0);
 
 
 
--connection default
 
 
 
--let $show_statement= SHOW PROCESSLIST
 
--let $field= State
 
--let $condition= = 'Waiting for table metadata lock'
 
 
 
--source include/wait_show_condition.inc
 
 
 
--error ER_LOCK_DEADLOCK
 
insert into s values (1,1,100000000000001,1,1,1000,0,0);
 
 
 
# Cleanup
 
commit;
 
--connection con1
 
--reap
 
--disconnect con1
 
--connection default
 
DROP SEQUENCE s;



 Comments   
Comment by Elena Stepanova [ 2017-09-02 ]

marko, if you have an opinion on the subject (e.g. whether the behavior is expected or not), please do share.

Comment by Marko Mäkelä [ 2017-09-04 ]

As far as InnoDB is concerned, sequences are a special kind of a table, with the following properties:

  • No InnoDB transactions (no record or table locking)
  • No undo logging (no rollback, MVCC, records stored off-page, secondary indexes)
  • Atomicity, durability, isolation and consistency are solely provided by redo-log mini-transactions (short-term InnoDB page locks for individual row operations)

So, the locking question should not involve InnoDB at all. Note that there were bugs that caused InnoDB to acquire locks earlier: MDEV-13015, MDEV-13497.

Comment by Michael Widenius [ 2018-04-21 ]

I don't think this is a bug.
For sequences, INSERT should be treated as an ALTER SEQUENCE statement as it creates a new sequence object and this can't be done until all other users of the sequence has ended.

The first statement can continue using the sequence with NEXT_VAL() until commit, but not do ALTER SEQUENCE or INSERT (which is the same thing). Trying to do an INSERT will cause a deadlock because of this.

Comment by Michael Widenius [ 2018-04-21 ]

Not a bug

Generated at Thu Feb 08 08:07:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.