…14:35:29>select * from t; +——+ | i | +——+ | 21 | | 30 |…insert into t values(29); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction root@localhost:test 14:38:03>insert into…insert a value out of [21,30],but it came out my insert still be blocked. The SHOW ENGINE INNODB…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… | 21 | | 25 | | 30 | +——+ transaction2> START TRANSACTION; transaction2> INSERT INTO t VALUES(26); transaction2> COMMIT; transaction1> select * from t where i > 20 …SELECT. They only occur if you do UPDATE or DELETE or SELECT FOR UPDATE. InnoDB provides REPEATABLE READ for read-only SELECT…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… means if you have workload which includes some very long SELECT queries you can be potentially waiting for hours for this… | root | localhost | dumptest | Query | 74 | Waiting for global read lock | insert into C values (“a”,1) | 0 | 0 | 1 | | 10304 | root | localhost… running SELECT queries to let backup to proceed, but resolving server gridlock one way or another. If you’re just using Innodb…
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
March 7, 2012 By 21 Comments
… MySQL RDS, InnoDB engine. Our requirement is to insert data in same table and read from same table. insert into … SAME_TABLE select my_id….85 GB). I wrote separate script to split the same insert in 8 chunks then it took 10min. Question : Can 14min…
Comment: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
March 11, 2012 By 7 Comments
… innodb_autoinc_lock_mode=0 2. restart mysql server now is working perfectly id – is primary key name – is unique varchar insert into tab (name) values (‘Peter’),(‘Oto’),(‘Jan’),(‘Jan’),(‘Jan’),(‘Romco’); select * from tab order by 1…
Comment: InnoDB's gap locks
April 4, 2012 By 14 Comments
Andy, With READ COMMITTED InnoDB doesn’t take additional locks on the gap, improving the … sec) session2> insert into t values(26); Query OK, 1 row affected (0.00 sec) session1> commit; session2> commit; session1> select * from t…
Comment: Predicting how long data load would take
March 26, 2012 By 12 Comments
One trick for really huge, hash-partitioned InnoDB tables is to sort by the partition rule, then by …(partition_key, ), so we found a good ordering is insert into target-tab … select from source_tab … order by mod(target_partition_key…
Comment: InnoDB's gap locks
April 6, 2012 By 14 Comments
… transactions will be REPEATABLE READ until the first write (or SELECT FOR UPDATE) happens, at which point the transaction is implicitly….stackexchange.com/questions/15854/innodb-row-locking-how-to-implement/15864#15864. I haven’t tested it with INSERT/DELETEs, but it should definitely behave the same if you change the SELECT FOR UPDATE into an UPDATE.
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 21 Comments
…SELECT * FROM tbl1 INFO OUTFILE ‘/tmp/tbl1.txt’; LOAD DATA INFILE ‘/tmp/tbl1.txt’ INTO TABLE tbl2; instead of: INSERT INTO tbl2 SELECT * from tbl1; INSERT … INTO…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… table. Now let us populate antest_innodb table which is same but uses Innodb format: mysql> insert into antest_innodb select * from antest; Query OK, 245760… sec) Very interesting result – after loading the data with INSERT in Innodb table we do not get NULL cardinality as with MyISAM…
