Recently I had to extract data from a number of large tables (MySql 5.0 InnoDB tables). Given the size of tables and number of joins, I used TEMP tables and stored procedure. When pushing data into temp tables I ran into locks on underlying SELECT tables. These queries were being run on a slave copy of MySQL replication. Lock on the select table were causing replication UPDATE/INSERT queries to time out and crash replication.
It turned out "INSERT INTO A_TEMP_TABLE select X, Y, Z from LARGE_TABLE1, LARGE_TABLE1 WHERE....." was locking large tables (InnoDB tables), even after specifying appropriate TRANSACTION ISOLATION LEVEL of READ COMMITTED.
MySQL documentation says the tables won't be locked, but they do get locked on 5.0.
"INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive index record without a gap lock on each row inserted into T. If innodb_locks_unsafe_for_binlog is enabled or the transaction isolation level is READ COMMITTED, InnoDB does the search on S as a consistent read (no locks)"
An ugly workaround was to use "SELECT INTO OUTFILE". Once you write to OUTFILE, you need to import it back in DB using LOAD DATE INFILE. It turns out you can not use LOAD DATA INFILE inside a stored procedure, so I had to fall back to Perl script to do the job.
Suggestions to MySQL.
1) Provide ability to ISSUE "INSERT INTO A_TEMP_TABLE select X, Y, Z from LARGE_TABLE1, LARGE_TABLE1 WHERE....." without locking tables when an apporpriate transaction level is specified. This probably is already fixed in 5.1.
2) Allow DATA LOAD INFILE from stored procedures.
Tuesday, June 30, 2009
MySQL InnoDB lock
Recently I had to extract data from a number of large tables (MySql 5.0 InnoDB tables). Given the size of tables and number of joins, I used TEMP tables and stored procedure. When pushing data into temp tables I ran into locks on underlying SELECT tables. These queries were being run on a slave copy of MySQL replication. Lock on the select table were causing replication UPDATE/INSERT queries to time out and crash replication.
It turned out "INSERT INTO A_TEMP_TABLE select X, Y, Z from LARGE_TABLE1, LARGE_TABLE1 WHERE....." was locking large tables (InnoDB tables), even after specifying appropriate TRANSACTION ISOLATION LEVEL of READ COMMITTED.
MySQL documentation says the tables won't be locked, but they do get locked on 5.0.
"INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive index record without a gap lock on each row inserted into T. If innodb_locks_unsafe_for_binlog is enabled or the transaction isolation level is READ COMMITTED, InnoDB does the search on S as a consistent read (no locks)"
An ugly workaround was to use "SELECT INTO OUTFILE". Once you write to OUTFILE, you need to import it back in DB using LOAD DATE INFILE. It turns out you can not use LOAD DATA INFILE inside a stored procedure, so I had to fall back to Perl script to do the job.
Suggestions to MySQL.
1) Provide ability to ISSUE "INSERT INTO A_TEMP_TABLE select X, Y, Z from LARGE_TABLE1, LARGE_TABLE1 WHERE....." without locking tables when an apporpriate transaction level is specified. This probably is already fixed in 5.1.
2) Allow DATA LOAD INFILE from stored procedures.
It turned out "INSERT INTO A_TEMP_TABLE select X, Y, Z from LARGE_TABLE1, LARGE_TABLE1 WHERE....." was locking large tables (InnoDB tables), even after specifying appropriate TRANSACTION ISOLATION LEVEL of READ COMMITTED.
MySQL documentation says the tables won't be locked, but they do get locked on 5.0.
"INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive index record without a gap lock on each row inserted into T. If innodb_locks_unsafe_for_binlog is enabled or the transaction isolation level is READ COMMITTED, InnoDB does the search on S as a consistent read (no locks)"
An ugly workaround was to use "SELECT INTO OUTFILE". Once you write to OUTFILE, you need to import it back in DB using LOAD DATE INFILE. It turns out you can not use LOAD DATA INFILE inside a stored procedure, so I had to fall back to Perl script to do the job.
Suggestions to MySQL.
1) Provide ability to ISSUE "INSERT INTO A_TEMP_TABLE select X, Y, Z from LARGE_TABLE1, LARGE_TABLE1 WHERE....." without locking tables when an apporpriate transaction level is specified. This probably is already fixed in 5.1.
2) Allow DATA LOAD INFILE from stored procedures.
Subscribe to:
Posts (Atom)