Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27922

INSERT fails to return an error after transaction abort

Details

    Description

      Under Repeatable Read isolation level, if two transactions concurrently execute and deadlock happend, the transaction that reported deadlock still executes remaining statements by transaction.

      /* init */ CREATE TABLE t(c1 INT PRIMARY KEY, c2 INT UNIQUE);
      /* init */ INSERT INTO t(c1) VALUES (8);
      /* t1 */ BEGIN;
      /* t1 */ UPDATE t SET c1=5, c2=7 WHERE -19;
      /* t2 */ BEGIN;
      /* t2 */ DELETE FROM t WHERE -15; -- blocked
      /* t1 */ UPDATE t SET c1=3 WHERE 0.5;
      /* t2 */ DELETE FROM t WHERE -15; -- deadlock
      /* t2 */ ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
      /* t1 */ COMMIT;
      /* t2 */ INSERT IGNORE INTO t(c1) VALUES (2);
      /* t2 */ SELECT * FROM t FOR UPDATE; -- [(2, NULL), (3, 7)]
      /* t2 */ ROLLBACK;
      /* t2 */ SELECT * FROM t FOR UPDATE; -- [(3, 7)]
      

      Due to the application of autocommit, the ROLLBACK statement should have no effect after deadlock in t2. The last select should return [(2, NULL), (3, 7)].

      Attachments

        Issue Links

          Activity

            I almost reproduced the claimed result with MariaDB 10.7.1. With 10.7.2 or 10.8.1, the DELETE does not report a deadlock; it will fail with a lock wait timeout error instead.

            The spurious deadlock error was removed in MDEV-27025. I confirmed that by testing before and after the 10.6 version of that fix.

            I cleaned up the test and converted it into executable format for mtr. The same result is returned even if the DELETE is aborted by a lock wait timeout.

            --source include/have_innodb.inc
             
            CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT UNIQUE) ENGINE=InnoDB;
            INSERT INTO t1 SET c1=8;
             
            BEGIN;
            UPDATE t1 SET c1=5, c2=7;
             
            connect con2,localhost,root,,;
            SET innodb_lock_wait_timeout=1;
            BEGIN;
            send DELETE FROM t1;
             
            connection default;
            let $wait_condition=
                select count(*) = 1 from information_schema.processlist
                where state = "Updating" and info = "DELETE FROM t1";
            --source include/wait_condition.inc
             
            UPDATE t1 SET c1=3;
             
            connection con2;
            --error ER_LOCK_DEADLOCK,ER_LOCK_WAIT_TIMEOUT
            reap;
            COMMIT;
            disconnect con2;
             
            connection default;
            INSERT INTO t1 SET c1=2;
            SELECT * FROM t1 FOR UPDATE;
            ROLLBACK;
            SELECT * FROM t1 FOR UPDATE;
             
            DROP TABLE t1;
            

            However, instead of the claimed (3,7) value, I am seeing (8,NULL) being reported at the end. Adding IGNORE to the last INSERT statement does not make any difference:

            10.6 bd03c0e51629e1c3969a171137712a6bb854c232

            SELECT * FROM t1 FOR UPDATE;
            c1	c2
            2	NULL
            3	7
            ROLLBACK;by
            SELECT * FROM t1 FOR UPDATE;
            c1	c2
            8	NULL
            DROP TABLE t1;
            

            I am actually getting the same (8,NULL) result also from git checkout mariadb-10.7.1.

            I checked an rr replay trace of the run, for 10.6 before the MDEV-20725 fix. As I expected, the DELETE transaction will be rolled back automatically due to the deadlock:

            10.6 1abc476f0b4fe60cb268fc68cefd9b5a479983ac

            Thread 2 hit Breakpoint 3, trx_t::commit_in_memory (mtr=0x0, this=0x7ff965bb6390) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1231
            1231	  must_flush_log_later= false;
            (rr) bt
            #0  trx_t::commit_in_memory (mtr=0x0, this=0x7ff965bb6390) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1231
            #1  trx_t::commit_low (this=this@entry=0x7ff965bb6390, mtr=0x0) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1448
            #2  0x00005648a88abc20 in trx_t::commit_persist (this=this@entry=0x7ff965bb6390) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1462
            #3  0x00005648a88abc8a in trx_t::commit (this=this@entry=0x7ff965bb6390) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1468
            #4  0x00005648a889c274 in trx_t::rollback_finish (this=this@entry=0x7ff965bb6390) at /mariadb/10.6/storage/innobase/trx/trx0roll.cc:66
            #5  0x00005648a889c8b1 in trx_t::rollback_low (this=this@entry=0x7ff965bb6390, savept=savept@entry=0x0) at /mariadb/10.6/storage/innobase/trx/trx0roll.cc:133
            #6  0x00005648a889a423 in trx_t::rollback (this=this@entry=0x7ff965bb6390, savept=savept@entry=0x0) at /mariadb/10.6/storage/innobase/trx/trx0roll.cc:170
            #7  0x00005648a8805e12 in row_mysql_handle_errors (new_err=new_err@entry=0x7ff954cadb5c, trx=trx@entry=0x7ff965bb6390, thr=thr@entry=0x7ff94402aa68, savept=savept@entry=0x0)
                at /mariadb/10.6/storage/innobase/row/row0mysql.cc:701
            #8  0x00005648a884c6aa in row_search_mvcc (buf=buf@entry=0x7ff944020cc8 "\377", mode=PAGE_CUR_G, prebuilt=<optimized out>, match_mode=<optimized out>, direction=direction@entry=0)
                at /mariadb/10.6/storage/innobase/row/row0sel.cc:5828
            #9  0x00005648a86939ef in ha_innobase::index_read (this=this@entry=0x7ff944029880, buf=0x7ff944020cc8 "\377", key_ptr=key_ptr@entry=0x0, key_len=key_len@entry=0, find_flag=find_flag@entry=HA_READ_AFTER_KEY)
                at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:9015
            #10 0x00005648a8693bb9 in ha_innobase::index_first (this=this@entry=0x7ff944029880, buf=<optimized out>) at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:9376
            #11 0x00005648a8693bdc in ha_innobase::rnd_next (this=0x7ff944029880, buf=<optimized out>) at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:9469
            #12 0x00005648a82c1ae5 in handler::ha_rnd_next (this=0x7ff944029880, buf=0x7ff944020cc8 "\377") at /mariadb/10.6/sql/handler.cc:3396
            #13 0x00005648a84a8db6 in rr_sequential (info=0x7ff954cae700) at /mariadb/10.6/sql/records.cc:519
            #14 0x00005648a84cabbb in READ_RECORD::read_record (this=0x7ff954cae700) at /mariadb/10.6/sql/records.h:81
            #15 mysql_delete (thd=thd@entry=0x7ff940001198, table_list=0x7ff9400137c0, conds=<optimized out>, order_list=order_list@entry=0x7ff940005fe0, limit=18446744073709551615, options=<optimized out>, 
                result=<optimized out>) at /mariadb/10.6/sql/sql_delete.cc:796
            #16 0x00005648a7fddb4e in mysql_execute_command (thd=thd@entry=0x7ff940001198, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /mariadb/10.6/sql/sql_parse.cc:4806
            #17 0x00005648a7fe2ac6 in mysql_parse (thd=thd@entry=0x7ff940001198, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x7ff954caf3c0) at /mariadb/10.6/sql/sql_parse.cc:8029
            #18 0x00005648a7fe51ac in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7ff940001198, packet=packet@entry=0x7ff94000b329 "DELETE FROM t1", packet_length=packet_length@entry=14, 
                blocking=blocking@entry=true) at /mariadb/10.6/sql/sql_parse.cc:1896
            

            At this point, Thread 12 (which was the second one to invoke lock_wait() during the UPDATE) is idle, waiting for the next command to execute. The undo_no of that transaction is 4, that is, it currently has 4 undo log records for row operations. That transaction will be committed (after rolling back all changes) in the explicit ROLLBACK statement:

            10.6 1abc476f0b4fe60cb268fc68cefd9b5a479983ac

            Thread 12 hit Breakpoint 3, trx_t::commit_in_memory (mtr=0x7ff954ff93f0, this=0x7ff965bb51e0) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1231
            1231	  must_flush_log_later= false;
            (rr) bt
            #0  trx_t::commit_in_memory (mtr=0x7ff954ff93f0, this=0x7ff965bb51e0) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1231
            #1  trx_t::commit_low (this=this@entry=0x7ff965bb51e0, mtr=0x7ff954ff93f0) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1448
            #2  0x00005648a88abc20 in trx_t::commit_persist (this=this@entry=0x7ff965bb51e0) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1462
            #3  0x00005648a88abc8a in trx_t::commit (this=this@entry=0x7ff965bb51e0) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1468
            #4  0x00005648a889c274 in trx_t::rollback_finish (this=this@entry=0x7ff965bb51e0) at /mariadb/10.6/storage/innobase/trx/trx0roll.cc:66
            #5  0x00005648a889c8b1 in trx_t::rollback_low (this=this@entry=0x7ff965bb51e0, savept=savept@entry=0x0) at /mariadb/10.6/storage/innobase/trx/trx0roll.cc:133
            #6  0x00005648a889a999 in trx_rollback_for_mysql_low (trx=trx@entry=0x7ff965bb51e0) at /mariadb/10.6/storage/innobase/trx/trx0roll.cc:190
            #7  0x00005648a889ac1e in trx_rollback_for_mysql (trx=trx@entry=0x7ff965bb51e0) at /mariadb/10.6/storage/innobase/trx/trx0roll.cc:221
            #8  0x00005648a867c63f in innobase_rollback (hton=<optimized out>, thd=0x7ff944001ab8, rollback_trx=<optimized out>) at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:4626
            #9  0x00005648a82bfaed in ha_rollback_trans (thd=thd@entry=0x7ff944001ab8, all=all@entry=true) at /mariadb/10.6/sql/handler.cc:2167
            #10 0x00005648a814c624 in trans_rollback (thd=thd@entry=0x7ff944001ab8) at /mariadb/10.6/sql/transaction.cc:372
            #11 0x00005648a7fe0708 in mysql_execute_command (thd=thd@entry=0x7ff944001ab8, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /mariadb/10.6/sql/sql_parse.cc:5656
            #12 0x00005648a7fe2ac6 in mysql_parse (thd=thd@entry=0x7ff944001ab8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x7ff954ffa3c0) at /mariadb/10.6/sql/sql_parse.cc:8029
            #13 0x00005648a7fe51ac in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7ff944001ab8, packet=packet@entry=0x7ff94400bc49 "ROLLBACK", packet_length=packet_length@entry=8, 
                blocking=blocking@entry=true) at /mariadb/10.6/sql/sql_parse.cc:1896
            

            Here is how I invoked the test in the build directory (written to mysql-test/main/MDEV-27922) in the source directory:

            cd mysql-test
            ./mtr main.MDEV-27922
            

            marko Marko Mäkelä added a comment - I almost reproduced the claimed result with MariaDB 10.7.1. With 10.7.2 or 10.8.1, the DELETE does not report a deadlock; it will fail with a lock wait timeout error instead. The spurious deadlock error was removed in MDEV-27025 . I confirmed that by testing before and after the 10.6 version of that fix . I cleaned up the test and converted it into executable format for mtr . The same result is returned even if the DELETE is aborted by a lock wait timeout. --source include/have_innodb.inc   CREATE TABLE t1(c1 INT PRIMARY KEY , c2 INT UNIQUE ) ENGINE=InnoDB; INSERT INTO t1 SET c1=8;   BEGIN ; UPDATE t1 SET c1=5, c2=7;   connect con2,localhost,root,,; SET innodb_lock_wait_timeout=1; BEGIN ; send DELETE FROM t1;   connection default ; let $wait_condition= select count (*) = 1 from information_schema.processlist where state = "Updating" and info = "DELETE FROM t1" ; --source include/wait_condition.inc   UPDATE t1 SET c1=3;   connection con2; --error ER_LOCK_DEADLOCK,ER_LOCK_WAIT_TIMEOUT reap; COMMIT ; disconnect con2;   connection default ; INSERT INTO t1 SET c1=2; SELECT * FROM t1 FOR UPDATE ; ROLLBACK ; SELECT * FROM t1 FOR UPDATE ;   DROP TABLE t1; However, instead of the claimed (3,7) value, I am seeing (8,NULL) being reported at the end. Adding IGNORE to the last INSERT statement does not make any difference: 10.6 bd03c0e51629e1c3969a171137712a6bb854c232 SELECT * FROM t1 FOR UPDATE; c1 c2 2 NULL 3 7 ROLLBACK;by SELECT * FROM t1 FOR UPDATE; c1 c2 8 NULL DROP TABLE t1; I am actually getting the same (8,NULL) result also from git checkout mariadb-10.7.1 . I checked an rr replay trace of the run, for 10.6 before the MDEV-20725 fix. As I expected, the DELETE transaction will be rolled back automatically due to the deadlock: 10.6 1abc476f0b4fe60cb268fc68cefd9b5a479983ac Thread 2 hit Breakpoint 3, trx_t::commit_in_memory (mtr=0x0, this=0x7ff965bb6390) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1231 1231 must_flush_log_later= false; (rr) bt #0 trx_t::commit_in_memory (mtr=0x0, this=0x7ff965bb6390) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1231 #1 trx_t::commit_low (this=this@entry=0x7ff965bb6390, mtr=0x0) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1448 #2 0x00005648a88abc20 in trx_t::commit_persist (this=this@entry=0x7ff965bb6390) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1462 #3 0x00005648a88abc8a in trx_t::commit (this=this@entry=0x7ff965bb6390) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1468 #4 0x00005648a889c274 in trx_t::rollback_finish (this=this@entry=0x7ff965bb6390) at /mariadb/10.6/storage/innobase/trx/trx0roll.cc:66 #5 0x00005648a889c8b1 in trx_t::rollback_low (this=this@entry=0x7ff965bb6390, savept=savept@entry=0x0) at /mariadb/10.6/storage/innobase/trx/trx0roll.cc:133 #6 0x00005648a889a423 in trx_t::rollback (this=this@entry=0x7ff965bb6390, savept=savept@entry=0x0) at /mariadb/10.6/storage/innobase/trx/trx0roll.cc:170 #7 0x00005648a8805e12 in row_mysql_handle_errors (new_err=new_err@entry=0x7ff954cadb5c, trx=trx@entry=0x7ff965bb6390, thr=thr@entry=0x7ff94402aa68, savept=savept@entry=0x0) at /mariadb/10.6/storage/innobase/row/row0mysql.cc:701 #8 0x00005648a884c6aa in row_search_mvcc (buf=buf@entry=0x7ff944020cc8 "\377", mode=PAGE_CUR_G, prebuilt=<optimized out>, match_mode=<optimized out>, direction=direction@entry=0) at /mariadb/10.6/storage/innobase/row/row0sel.cc:5828 #9 0x00005648a86939ef in ha_innobase::index_read (this=this@entry=0x7ff944029880, buf=0x7ff944020cc8 "\377", key_ptr=key_ptr@entry=0x0, key_len=key_len@entry=0, find_flag=find_flag@entry=HA_READ_AFTER_KEY) at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:9015 #10 0x00005648a8693bb9 in ha_innobase::index_first (this=this@entry=0x7ff944029880, buf=<optimized out>) at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:9376 #11 0x00005648a8693bdc in ha_innobase::rnd_next (this=0x7ff944029880, buf=<optimized out>) at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:9469 #12 0x00005648a82c1ae5 in handler::ha_rnd_next (this=0x7ff944029880, buf=0x7ff944020cc8 "\377") at /mariadb/10.6/sql/handler.cc:3396 #13 0x00005648a84a8db6 in rr_sequential (info=0x7ff954cae700) at /mariadb/10.6/sql/records.cc:519 #14 0x00005648a84cabbb in READ_RECORD::read_record (this=0x7ff954cae700) at /mariadb/10.6/sql/records.h:81 #15 mysql_delete (thd=thd@entry=0x7ff940001198, table_list=0x7ff9400137c0, conds=<optimized out>, order_list=order_list@entry=0x7ff940005fe0, limit=18446744073709551615, options=<optimized out>, result=<optimized out>) at /mariadb/10.6/sql/sql_delete.cc:796 #16 0x00005648a7fddb4e in mysql_execute_command (thd=thd@entry=0x7ff940001198, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /mariadb/10.6/sql/sql_parse.cc:4806 #17 0x00005648a7fe2ac6 in mysql_parse (thd=thd@entry=0x7ff940001198, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x7ff954caf3c0) at /mariadb/10.6/sql/sql_parse.cc:8029 #18 0x00005648a7fe51ac in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7ff940001198, packet=packet@entry=0x7ff94000b329 "DELETE FROM t1", packet_length=packet_length@entry=14, blocking=blocking@entry=true) at /mariadb/10.6/sql/sql_parse.cc:1896 At this point, Thread 12 (which was the second one to invoke lock_wait() during the UPDATE ) is idle, waiting for the next command to execute. The undo_no of that transaction is 4, that is, it currently has 4 undo log records for row operations. That transaction will be committed (after rolling back all changes) in the explicit ROLLBACK statement: 10.6 1abc476f0b4fe60cb268fc68cefd9b5a479983ac Thread 12 hit Breakpoint 3, trx_t::commit_in_memory (mtr=0x7ff954ff93f0, this=0x7ff965bb51e0) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1231 1231 must_flush_log_later= false; (rr) bt #0 trx_t::commit_in_memory (mtr=0x7ff954ff93f0, this=0x7ff965bb51e0) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1231 #1 trx_t::commit_low (this=this@entry=0x7ff965bb51e0, mtr=0x7ff954ff93f0) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1448 #2 0x00005648a88abc20 in trx_t::commit_persist (this=this@entry=0x7ff965bb51e0) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1462 #3 0x00005648a88abc8a in trx_t::commit (this=this@entry=0x7ff965bb51e0) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1468 #4 0x00005648a889c274 in trx_t::rollback_finish (this=this@entry=0x7ff965bb51e0) at /mariadb/10.6/storage/innobase/trx/trx0roll.cc:66 #5 0x00005648a889c8b1 in trx_t::rollback_low (this=this@entry=0x7ff965bb51e0, savept=savept@entry=0x0) at /mariadb/10.6/storage/innobase/trx/trx0roll.cc:133 #6 0x00005648a889a999 in trx_rollback_for_mysql_low (trx=trx@entry=0x7ff965bb51e0) at /mariadb/10.6/storage/innobase/trx/trx0roll.cc:190 #7 0x00005648a889ac1e in trx_rollback_for_mysql (trx=trx@entry=0x7ff965bb51e0) at /mariadb/10.6/storage/innobase/trx/trx0roll.cc:221 #8 0x00005648a867c63f in innobase_rollback (hton=<optimized out>, thd=0x7ff944001ab8, rollback_trx=<optimized out>) at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:4626 #9 0x00005648a82bfaed in ha_rollback_trans (thd=thd@entry=0x7ff944001ab8, all=all@entry=true) at /mariadb/10.6/sql/handler.cc:2167 #10 0x00005648a814c624 in trans_rollback (thd=thd@entry=0x7ff944001ab8) at /mariadb/10.6/sql/transaction.cc:372 #11 0x00005648a7fe0708 in mysql_execute_command (thd=thd@entry=0x7ff944001ab8, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /mariadb/10.6/sql/sql_parse.cc:5656 #12 0x00005648a7fe2ac6 in mysql_parse (thd=thd@entry=0x7ff944001ab8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x7ff954ffa3c0) at /mariadb/10.6/sql/sql_parse.cc:8029 #13 0x00005648a7fe51ac in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7ff944001ab8, packet=packet@entry=0x7ff94400bc49 "ROLLBACK", packet_length=packet_length@entry=8, blocking=blocking@entry=true) at /mariadb/10.6/sql/sql_parse.cc:1896 Here is how I invoked the test in the build directory (written to mysql-test/main/ MDEV-27922 ) in the source directory: cd mysql-test ./mtr main.MDEV-27922

            I made a small but important mistake in my conversion of the test to mtr format. All statements starting with COMMIT were being issued from the wrong connection. Here is a revised test:

            --source include/have_innodb.inc
             
            CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT UNIQUE) ENGINE=InnoDB;
            INSERT INTO t1 SET c1=8;
             
            BEGIN;
            UPDATE t1 SET c1=5, c2=7;
             
            connect con2,localhost,root,,;
            SET innodb_lock_wait_timeout=1;
            BEGIN;
            send DELETE FROM t1;
             
            connection default;
            let $wait_condition=
                select count(*) = 1 from information_schema.processlist
                where state = "Updating" and info = "DELETE FROM t1";
            --source include/wait_condition.inc
             
            UPDATE t1 SET c1=3;
             
            connection con2;
            --error ER_LOCK_DEADLOCK,ER_LOCK_WAIT_TIMEOUT
            reap;
             
            connection default;
            COMMIT;
             
            connection con2;
            INSERT INTO t1 SET c1=2;
            SELECT * FROM t1 FOR UPDATE;
            # returns [(2, NULL), (3, 7)]
            ROLLBACK;
            SELECT * FROM t1 FOR UPDATE;
            # returns [(3, 7)]
            disconnect con2;
             
            connection default;
            DROP TABLE t1;
            

            So, what happens after the 2 UPDATE statements was committed? The 4 undo log records that the transaction created must have been for the following:

            1. Delete-mark the record (8,NULL)
            2. Insert the record (5,7)
            3. Delete-mark the record (5,7)
            4. Insert the record (3,7)

            Yes, updating the PRIMARY KEY is expensive, and it should be avoided in applications. So, right after the COMMIT point we expect the table to contain (3,7).

            Then, what happens for the second INSERT? From the InnoDB point of view, the transaction was rolled back, and there is no active transaction. Hence, the "start if not started" logic will kick in and an auto-commit transaction will be created for that INSERT.

            From a user point of view, the error is that the second INSERT statement is being accepted for execution, even though no COMMIT or ROLLBACK had been executed to cancel the effect of BEGIN.

            As far as I can tell, the SQL layer must return an error that the transaction was aborted. If I change the test to use XA transactions:

            @@ -8,7 +8,7 @@
             
             connect con2,localhost,root,,;
             SET innodb_lock_wait_timeout=1;
            -BEGIN;
            +XA START 'xid1';
             send DELETE FROM t1;
             
             connection default;
            @@ -30,7 +30,9 @@
             INSERT INTO t1 SET c1=2;
             SELECT * FROM t1 FOR UPDATE;
             # returns [(2, NULL), (3, 7)]
            -ROLLBACK;
            +--error ER_XA_RBDEADLOCK
            +XA END 'xid1';
            +XA ROLLBACK 'xid1';
             SELECT * FROM t1 FOR UPDATE;
             # returns [(3, 7)]
             disconnect con2;
            

            then a proper error will be reported (although not already for the INSERT) and the correct result will be delivered:

            mariadb-10.7.1

            CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT UNIQUE) ENGINE=InnoDB;
            INSERT INTO t1 SET c1=8;
            BEGIN;
            UPDATE t1 SET c1=5, c2=7;
            connect con2,localhost,root,,;
            SET innodb_lock_wait_timeout=1;
            XA START 'xid1';
            DELETE FROM t1;
            connection default;
            UPDATE t1 SET c1=3;
            connection con2;
            Got one of the listed errors
            connection default;
            COMMIT;
            connection con2;
            INSERT INTO t1 SET c1=2;
            SELECT * FROM t1 FOR UPDATE;
            c1	c2
            2	NULL
            3	7
            XA END 'xid1';
            ERROR XA102: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected
            XA ROLLBACK 'xid1';
            SELECT * FROM t1 FOR UPDATE;
            c1	c2
            3	7
            disconnect con2;
            connection default;
            DROP TABLE t1;
            

            However, I do not fully agree with the following claim of John Jove:

            Due to the application of autocommit, the ROLLBACK statement should have no effect after deadlock in t2.

            This could be an ambiguous area; I am not familiar with the SQL standard. The test case did not send any explicit ROLLBACK or COMMIT after the error was returned.

            Let me revise the test once more, to use COMMIT instead of ROLLBACK. In this case, we should expect either both the DELETE and the INSERT to remain in force, not only the INSERT:

            --source include/have_innodb.inc
             
            CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT UNIQUE) ENGINE=InnoDB;
            INSERT INTO t1 SET c1=8;
             
            BEGIN;
            UPDATE t1 SET c1=5, c2=7;
             
            connect con2,localhost,root,,;
            SET innodb_lock_wait_timeout=1;
            BEGIN;
            send DELETE FROM t1;
             
            connection default;
            let $wait_condition=
                select count(*) = 1 from information_schema.processlist
                where state = "Updating" and info = "DELETE FROM t1";
            --source include/wait_condition.inc
             
            UPDATE t1 SET c1=3;
             
            connection con2;
            --error ER_LOCK_DEADLOCK,ER_LOCK_WAIT_TIMEOUT
            reap;
             
            connection default;
            COMMIT;
             
            connection con2;
            INSERT INTO t1 SET c1=2;
            COMMIT;
            SELECT * FROM t1 FOR UPDATE;
            disconnect con2;
             
            connection default;
            DROP TABLE t1;
            

            With this test, we are in fact seeing the incorrect result (2,NULL),(3,7) instead of a correct result of only one of the rows.

            10.3 5b237e5965c613842a897516eebf0d4e9fe12f98

            connection con2;
            Got one of the listed errors
            connection default;
            COMMIT;
            connection con2;
            INSERT INTO t1 SET c1=2;
            COMMIT;
            SELECT * FROM t1 FOR UPDATE;
            c1	c2
            2	NULL
            3	7
            disconnect con2;
            connection default;
            DROP TABLE t1;
            

            The XA version of this test would return ER_XA_RBDEADLOCK to both XA END and XA COMMIT, and deliver the correct result:

            10.3 5b237e5965c613842a897516eebf0d4e9fe12f98

            connection con2;
            Got one of the listed errors
            connection default;
            COMMIT;
            connection con2;
            INSERT INTO t1 SET c1=2;
            XA END 'xid1';
            ERROR XA102: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected
            XA COMMIT 'xid1';
            ERROR XA102: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected
            SELECT * FROM t1 FOR UPDATE;
            c1	c2
            3	7
            disconnect con2;
            connection default;
            DROP TABLE t1;
            

            marko Marko Mäkelä added a comment - I made a small but important mistake in my conversion of the test to mtr format. All statements starting with COMMIT were being issued from the wrong connection. Here is a revised test: --source include/have_innodb.inc   CREATE TABLE t1(c1 INT PRIMARY KEY , c2 INT UNIQUE ) ENGINE=InnoDB; INSERT INTO t1 SET c1=8;   BEGIN ; UPDATE t1 SET c1=5, c2=7;   connect con2,localhost,root,,; SET innodb_lock_wait_timeout=1; BEGIN ; send DELETE FROM t1;   connection default ; let $wait_condition= select count (*) = 1 from information_schema.processlist where state = "Updating" and info = "DELETE FROM t1" ; --source include/wait_condition.inc   UPDATE t1 SET c1=3;   connection con2; --error ER_LOCK_DEADLOCK,ER_LOCK_WAIT_TIMEOUT reap;   connection default ; COMMIT ;   connection con2; INSERT INTO t1 SET c1=2; SELECT * FROM t1 FOR UPDATE ; # returns [(2, NULL ), (3, 7)] ROLLBACK ; SELECT * FROM t1 FOR UPDATE ; # returns [(3, 7)] disconnect con2;   connection default ; DROP TABLE t1; So, what happens after the 2 UPDATE statements was committed? The 4 undo log records that the transaction created must have been for the following: Delete-mark the record (8,NULL) Insert the record (5,7) Delete-mark the record (5,7) Insert the record (3,7) Yes, updating the PRIMARY KEY is expensive, and it should be avoided in applications. So, right after the COMMIT point we expect the table to contain (3,7). Then, what happens for the second INSERT ? From the InnoDB point of view, the transaction was rolled back, and there is no active transaction. Hence, the "start if not started" logic will kick in and an auto-commit transaction will be created for that INSERT . From a user point of view, the error is that the second INSERT statement is being accepted for execution, even though no COMMIT or ROLLBACK had been executed to cancel the effect of BEGIN . As far as I can tell, the SQL layer must return an error that the transaction was aborted. If I change the test to use XA transactions: @@ -8,7 +8,7 @@ connect con2,localhost,root,,; SET innodb_lock_wait_timeout=1; -BEGIN; +XA START 'xid1'; send DELETE FROM t1; connection default; @@ -30,7 +30,9 @@ INSERT INTO t1 SET c1=2; SELECT * FROM t1 FOR UPDATE; # returns [(2, NULL), (3, 7)] -ROLLBACK; +--error ER_XA_RBDEADLOCK +XA END 'xid1'; +XA ROLLBACK 'xid1'; SELECT * FROM t1 FOR UPDATE; # returns [(3, 7)] disconnect con2; then a proper error will be reported (although not already for the INSERT ) and the correct result will be delivered: mariadb-10.7.1 CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT UNIQUE) ENGINE=InnoDB; INSERT INTO t1 SET c1=8; BEGIN; UPDATE t1 SET c1=5, c2=7; connect con2,localhost,root,,; SET innodb_lock_wait_timeout=1; XA START 'xid1'; DELETE FROM t1; connection default; UPDATE t1 SET c1=3; connection con2; Got one of the listed errors connection default; COMMIT; connection con2; INSERT INTO t1 SET c1=2; SELECT * FROM t1 FOR UPDATE; c1 c2 2 NULL 3 7 XA END 'xid1'; ERROR XA102: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected XA ROLLBACK 'xid1'; SELECT * FROM t1 FOR UPDATE; c1 c2 3 7 disconnect con2; connection default; DROP TABLE t1; However, I do not fully agree with the following claim of John Jove : Due to the application of autocommit, the ROLLBACK statement should have no effect after deadlock in t2. This could be an ambiguous area; I am not familiar with the SQL standard. The test case did not send any explicit ROLLBACK or COMMIT after the error was returned. Let me revise the test once more, to use COMMIT instead of ROLLBACK . In this case, we should expect either both the DELETE and the INSERT to remain in force, not only the INSERT : --source include/have_innodb.inc   CREATE TABLE t1(c1 INT PRIMARY KEY , c2 INT UNIQUE ) ENGINE=InnoDB; INSERT INTO t1 SET c1=8;   BEGIN ; UPDATE t1 SET c1=5, c2=7;   connect con2,localhost,root,,; SET innodb_lock_wait_timeout=1; BEGIN ; send DELETE FROM t1;   connection default ; let $wait_condition= select count (*) = 1 from information_schema.processlist where state = "Updating" and info = "DELETE FROM t1" ; --source include/wait_condition.inc   UPDATE t1 SET c1=3;   connection con2; --error ER_LOCK_DEADLOCK,ER_LOCK_WAIT_TIMEOUT reap;   connection default ; COMMIT ;   connection con2; INSERT INTO t1 SET c1=2; COMMIT ; SELECT * FROM t1 FOR UPDATE ; disconnect con2;   connection default ; DROP TABLE t1; With this test, we are in fact seeing the incorrect result (2,NULL),(3,7) instead of a correct result of only one of the rows. 10.3 5b237e5965c613842a897516eebf0d4e9fe12f98 connection con2; Got one of the listed errors connection default; COMMIT; connection con2; INSERT INTO t1 SET c1=2; COMMIT; SELECT * FROM t1 FOR UPDATE; c1 c2 2 NULL 3 7 disconnect con2; connection default; DROP TABLE t1; The XA version of this test would return ER_XA_RBDEADLOCK to both XA END and XA COMMIT , and deliver the correct result: 10.3 5b237e5965c613842a897516eebf0d4e9fe12f98 connection con2; Got one of the listed errors connection default; COMMIT; connection con2; INSERT INTO t1 SET c1=2; XA END 'xid1'; ERROR XA102: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected XA COMMIT 'xid1'; ERROR XA102: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected SELECT * FROM t1 FOR UPDATE; c1 c2 3 7 disconnect con2; connection default; DROP TABLE t1;

            Here is the XA version of the test that was revised to use COMMIT instead of ROLLBACK:

            @@ -8,7 +8,7 @@
             
             connect con2,localhost,root,,;
             SET innodb_lock_wait_timeout=1;
            -BEGIN;
            +XA START 'xid1';
             send DELETE FROM t1;
             
             connection default;
            @@ -28,7 +28,11 @@
             
             connection con2;
             INSERT INTO t1 SET c1=2;
            -COMMIT;
            +--error 0,ER_XA_RBDEADLOCK
            +XA END 'xid1';
            +--error ER_XAER_RMFAIL,ER_XA_RBDEADLOCK
            +XA COMMIT 'xid1';
            +XA ROLLBACK 'xid1';
             SELECT * FROM t1 FOR UPDATE;
             disconnect con2;
             
            

            Without the XA ROLLBACK, the last SELECT would complain:

            10.8 50fa94ea2b5c26c99aee67f80eb5f68f329ef80c

            mysqltest: At line 36: query 'SELECT * FROM t1 FOR UPDATE' failed: ER_XAER_RMFAIL (1399): XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
            

            Again, this test variant returns the correct result (3,7), but it would be nicer to report an error already for the INSERT that is being submitted to an aborted transaction.

            marko Marko Mäkelä added a comment - Here is the XA version of the test that was revised to use COMMIT instead of ROLLBACK : @@ -8,7 +8,7 @@ connect con2,localhost,root,,; SET innodb_lock_wait_timeout=1; -BEGIN; +XA START 'xid1'; send DELETE FROM t1; connection default; @@ -28,7 +28,11 @@ connection con2; INSERT INTO t1 SET c1=2; -COMMIT; +--error 0,ER_XA_RBDEADLOCK +XA END 'xid1'; +--error ER_XAER_RMFAIL,ER_XA_RBDEADLOCK +XA COMMIT 'xid1'; +XA ROLLBACK 'xid1'; SELECT * FROM t1 FOR UPDATE; disconnect con2; Without the XA ROLLBACK , the last SELECT would complain: 10.8 50fa94ea2b5c26c99aee67f80eb5f68f329ef80c mysqltest: At line 36: query 'SELECT * FROM t1 FOR UPDATE' failed: ER_XAER_RMFAIL (1399): XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state Again, this test variant returns the correct result (3,7), but it would be nicer to report an error already for the INSERT that is being submitted to an aborted transaction.
            John Jove John Jove added a comment -

            Thank you for your reply. I think your explanation is reasonable.

            I reproduced this case in MySQL and its behaviour was different. Please see:

            /* init */ CREATE TABLE t(c1 INT PRIMARY KEY, c2 INT UNIQUE);
            /* init */ INSERT INTO t(c1) VALUES (8);
            /* t1 */ BEGIN;
            /* t1 */ UPDATE t SET c1=5, c2=7 WHERE -19;
            /* t2 */ BEGIN;
            /* t2 */ DELETE FROM t WHERE -15; -- blocked
            /* t1 */ UPDATE t SET c1=3 WHERE 0.5;
            /* t2 */ DELETE FROM t WHERE -15; -- deadlock
            /* t2 */ ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
            /* t1 */ COMMIT;
            /* t2 */ INSERT IGNORE INTO t(c1) VALUES (2);
            /* t2 */ SELECT * FROM t FOR UPDATE; -- [(2, NULL), (3, 7)]
            /* t2 */ ROLLBACK;
            /* t2 */ SELECT * FROM t FOR UPDATE; -- [(2, NULL), (3, 7)]
            

            In MySQL, the DELETE also reported a deadlock. After reporting deadlock, the second transaction was automatically rolled back. So, the INSERT statement autocommitted and the ROLLBACK statement had no effect in second transaction. The last select returned [(2, NULL), (3, 7)], which was different from the result [(3, 7)] in MariaDB.

            John Jove John Jove added a comment - Thank you for your reply. I think your explanation is reasonable. I reproduced this case in MySQL and its behaviour was different. Please see: /* init */ CREATE TABLE t(c1 INT PRIMARY KEY , c2 INT UNIQUE ); /* init */ INSERT INTO t(c1) VALUES (8); /* t1 */ BEGIN ; /* t1 */ UPDATE t SET c1=5, c2=7 WHERE -19; /* t2 */ BEGIN ; /* t2 */ DELETE FROM t WHERE -15; -- blocked /* t1 */ UPDATE t SET c1=3 WHERE 0.5; /* t2 */ DELETE FROM t WHERE -15; -- deadlock /* t2 */ ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction /* t1 */ COMMIT ; /* t2 */ INSERT IGNORE INTO t(c1) VALUES (2); /* t2 */ SELECT * FROM t FOR UPDATE ; -- [(2, NULL), (3, 7)] /* t2 */ ROLLBACK ; /* t2 */ SELECT * FROM t FOR UPDATE ; -- [(2, NULL), (3, 7)] In MySQL, the DELETE also reported a deadlock. After reporting deadlock, the second transaction was automatically rolled back. So, the INSERT statement autocommitted and the ROLLBACK statement had no effect in second transaction. The last select returned [(2, NULL), (3, 7)] , which was different from the result [(3, 7)] in MariaDB.

            This is, apparently, intentional, so not a bug. It was introduced by the commit https://github.com/MariaDB/server/commit/fb5d9a82a783f

            After that commit, an implicit rollback automatically starts a new transaction if autocommit is ON. The commit says that the previous behavior (switching into autocommit mode) although "consistent with behavior of explicit ROLLBACK has broken expectations of users and backward compatibility assumptions."

            Interestingly enough, it's a MySQL commit from 2013, MariaDB was following MySQL code much more closely back then. It looks like MySQL later decided to break these "backward compatibility assumptions" and we didn't.

            serg Sergei Golubchik added a comment - This is, apparently, intentional, so not a bug. It was introduced by the commit https://github.com/MariaDB/server/commit/fb5d9a82a783f After that commit, an implicit rollback automatically starts a new transaction if autocommit is ON. The commit says that the previous behavior (switching into autocommit mode) although "consistent with behavior of explicit ROLLBACK has broken expectations of users and backward compatibility assumptions." Interestingly enough, it's a MySQL commit from 2013, MariaDB was following MySQL code much more closely back then. It looks like MySQL later decided to break these "backward compatibility assumptions" and we didn't.

            People

              sanja Oleksandr Byelkin
              John Jove John Jove
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.