MySQL Bugs: #7527: LOCK TABLE ... READ LOCAL doesn't work with InnoDB

Bug #7527	LOCK TABLE ... READ LOCAL doesn't work with InnoDB
Submitted:	24 Dec 2004 14:01	Modified:	26 Dec 2004 10:38
Reporter:	Sergei Golubchik	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	4.1	OS:
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
LOCK TABLE innodb_table READ LOCAL allows the table to be updated and these updates to be visible by the locker's thread.

How to repeat:
assuming autocommit:

mysql1> LOCK TABLE innodb_table READ LOCAL;
mysql2> INSERT innodb_table VALUES (1);
mysql1> SELECT * FROM innodb_table;

4.0 is probably affected too

Sergei,

MyISAM works the same way with new inserts to the table:

Tested on 4.1.9:

Conn 1:
mysql> create table t(a int) type=myisam;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> lock table t read local;
Query OK, 0 rows affected (0.00 sec)

mysql>

Conn 2:
mysql> insert into t values (10);
Query OK, 1 row affected (0.01 sec)

mysql>

MyISAM does block updates to old rows of the table.

http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html
"
The difference between READ LOCAL and READ is that READ LOCAL allows non-conflicting INSERT statements (concurrent inserts) to execute while the lock is held. However, this can't be used if you are going to manipulate the database files outside MySQL while you hold the lock. 
"

Internally, LOCK TABLES ... READ does:

Breakpoint 1, ha_innobase::store_lock(THD*, st_thr_lock_data**, thr_lock_type)
    (this=0x8b4de80, thd=0x8b3f0f8, to=0x8b40e60, lock_type=TL_READ_NO_INSERT)
    at ha_innodb.cc:5278

while LOCK TABLES ... READ LOCAL does:

Breakpoint 1, ha_innobase::store_lock(THD*, st_thr_lock_data**, thr_lock_type)
    (this=0x8b4de80, thd=0x8b3f0f8, to=0x8b40e60, lock_type=TL_READ)
    at ha_innodb.cc:5278

InnoDB could change TL_READ to TL_READ_NO_INSERT in ::store_lock(), to make READ LOCAL equivalent to READ.

But this is a behavioral change. One could claim that for InnoDB, no update or insert is a 'conflicting' operation. For MyISAM, READ LOCAL is a way to do a 'consistent read' of the table.

At least, the manual has to be updated. Using READ LOCAL is deprecated for InnoDB. mysqldump --opt does use READ LOCAL, though.

Regards,

Heikki

You missed the point :)

The bug is not that INSERT is not blocked - but that it is visible to the thread holdnig the lock. Notice "mysql1> SELECT * FROM innodb_table" in my test case. If you do it in MyISAM you won't see inserted rows.

Sergei,

I think it does not pay to force an InnoDB 'consistent read' at LOCK TABLES ... READ LOCAL. There are much better ways to start a consistent read in InnoDB. In mysqldump, people should use the --single-transaction option, not --opt.

The question is does any application break if we make LOCK TABLES ... READ LOCAL a synonym of LOCK TABLES ... READ for InnoDB tables. In mysqldump --opt, it would mean that we lock the table, while it now does not really lock InnoDB tables. People might bump into difficulties in dumping their tables.

ha_innodb.cc:

       } else {
                if (trx->isolation_level != TRX_ISO_SERIALIZABLE
                    && thd->lex->sql_command == SQLCOM_SELECT
                    && thd->lex->lock_option == TL_READ) {

                        /* For other than temporary tables, we obtain
                        no lock for consistent read (plain SELECT). */

                        prebuilt->select_lock_type = LOCK_NONE;

We cannot get fully consistent mysqldump backups with LOCK TABLES ... READ either, because mysqldump probably runs in the AUTOCOMMIT=1 mode, and commits during the backup run can change what the SELECTs (that are performed as consistent reads) see.

I would favor keeping the READ LOCAL as an essentially no-op for InnoDB, and documenting it in the manual as deprecated.

Note that the whole old LOCK TABLES becomes deprecated for InnoDB tables in 5.0.3, where we have the regular, industry-standard table locking:

LOCK TABLES ... WHERE ENGINE=InnoDB;

or whatever syntax we decide.

Regards,

Heikki

Hi!

I have now updated the manual. READ LOCAL essentially does nothing for InnoDB type tables, and is deprecated.

In mysqldump, --single-transaction --quick is recommended for InnoDB type tables.

I am closing this bug report now. Feel free to reopen it, if more fixes are needed.

Regards,

Heikki