It wasn't a problem before MDEV-7660. With MDEV-7660 LOCK TABLES now starts transaction and this feature is generally broken, because transaction is rolled back on disconnect.
Attachments
Issue Links
is caused by
MDEV-7660MySQL WL#6671 "Improve scalability by not using thr_lock.c locks for InnoDB tables"
For backward compatibility we will implement a special handling for transactions implicitly opened by LOCK TABLES. These transactions will be committed on disconnect, not rolled back,
Sergei Golubchik
added a comment - For backward compatibility we will implement a special handling for transactions implicitly opened by LOCK TABLES. These transactions will be committed on disconnect, not rolled back,
Sergey Vojtovich
added a comment - Problem #1 (solvable, but confusing):
We have to consider autocommit setting that was before LOCK TABLES when deciding whether to commit or rollback transaction.
Disconnect must commit transaction:
SET @@autocommit=1;
LOCK TABLE t1 WRITE;
Disconnect must rollback transaction:
SET @@autocommit=0;
LOCK TABLE t1 WRITE;
The latter example is from http://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html
Before MDEV-7660 SELECT didn't see "1", now it sees.
BUT if t1 is write locked, COMMIT doesn't break lock and thus "1" is not visible.
Sergey Vojtovich
added a comment - - edited Problem #2 (not completely related):
COMMIT now breaks lock:
CREATE TABLE t1(a INT ) ENGINE=InnoDB;
SET @@autocommit=0;
LOCK TABLE t1 READ ;
connect (con1, localhost, root);
send INSERT INTO t1 VALUES (1);
connection default ;
COMMIT ;
--sleep 1
SELECT * FROM t1;
UNLOCK TABLES;
connection con1;
reap;
disconnect con1;
connection default ;
DROP TABLE t1;
Before MDEV-7660 SELECT didn't see "1", now it sees.
BUT if t1 is write locked, COMMIT doesn't break lock and thus "1" is not visible.
Problem #2 extension (it might be unrelated to the disconnect issue, but it's definitely related to the root cause)
ROLLBACK now breaks lock;
statements which cause implicit commit can now break lock, e.g. ANALYZE TABLE !
Problem #3
This does not work anymore:
--source include/have_log_bin.inc
SET SESSION BINLOG_FORMAT=ROW;
CREATETABLE t11 (song VARCHAR(255));
LOCK TABLES t11 WRITE;
INSERTINTO t11 VALUES('Several Species');
SET SESSION BINLOG_FORMAT=STATEMENT;
INSERTINTO t11 VALUES('Careful');
UNLOCK TABLES;
At line 7: query 'SET SESSION BINLOG_FORMAT=STATEMENT' failed: 1679: Cannot modify @@session.binlog_format inside a transaction
I'm sure there are many more surprises.
serg, again, given all the above, I beg to reconsider. Yes, I suppose every single issue can be solved by some cumbersome workaround, or declared as non-essential, but the sheer amount of incompatibilities that we introduce just does not make sense. Even if it were the only way to implement the desired optimization, it would still be very questionable, but to my understanding, svoj had an alternative solution which wasn't supposed to cause all of this, but it was discarded. Maybe it could be revived?
Elena Stepanova
added a comment - - edited Problem #2 extension (it might be unrelated to the disconnect issue, but it's definitely related to the root cause)
ROLLBACK now breaks lock;
statements which cause implicit commit can now break lock, e.g. ANALYZE TABLE !
Problem #3
This does not work anymore:
--source include/have_log_bin.inc
SET SESSION BINLOG_FORMAT=ROW;
CREATE TABLE t11 (song VARCHAR (255));
LOCK TABLES t11 WRITE;
INSERT INTO t11 VALUES ( 'Several Species' );
SET SESSION BINLOG_FORMAT=STATEMENT;
INSERT INTO t11 VALUES ( 'Careful' );
UNLOCK TABLES;
At line 7: query 'SET SESSION BINLOG_FORMAT=STATEMENT' failed: 1679: Cannot modify @@session.binlog_format inside a transaction
I'm sure there are many more surprises.
serg , again, given all the above, I beg to reconsider. Yes, I suppose every single issue can be solved by some cumbersome workaround, or declared as non-essential, but the sheer amount of incompatibilities that we introduce just does not make sense. Even if it were the only way to implement the desired optimization, it would still be very questionable, but to my understanding, svoj had an alternative solution which wasn't supposed to cause all of this, but it was discarded. Maybe it could be revived?
Bug
Blocker
MDEV-7660 MySQL WL#6671 "Improve scalability by not using thr_lock.c locks for InnoDB tables"
For backward compatibility we will implement a special handling for transactions implicitly opened by LOCK TABLES. These transactions will be committed on disconnect, not rolled back,