… affected (0.00 sec) root@localhost:test 14:35:29>select * from t; +——+ | i | +——+ | 21 | | 30 | | 26 | +——+ 3 rows in set… 1100101952 inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1216, 1 row lock(s) MySQL thread id 31929, query id 484616 localhost root update insert into t values(33) ——- TRX…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… | | 25 | | 30 | +——+ transaction2> START TRANSACTION; transaction2> INSERT INTO t VALUES(26); transaction2> COMMIT; transaction1> select * from t where i > 20 FOR UPDATE…/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the default isolation level so it…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… SELECT queries you can be potentially waiting for hours for this statement to complete. Here is example how it can look: mysql… | root | localhost | dumptest | Query | 74 | Waiting for global read lock | insert into C values (“a”,1) | 0 | 0 | 1 | | 10304 | root… WITH READ LOCK and fail backup or kill long running SELECT queries to let backup to proceed, but resolving server gridlock…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…is stored in this table and so data which was inserted after start of transaction (by ALTER TABLE statement) will …set (0.00 sec) mysql> select count(*) from A; +———-+ | count(*) | +———-+ | 2359296 | +———-+ 1 row in set (1.73 sec) mysql> select * from C; +——+ | t | +——+ | …
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
March 7, 2012 By 21 Comments
… AWS MySQL RDS, InnoDB engine. Our requirement is to insert data in same table and read from same table. insert into … SAME_TABLE select….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
… 5.1.22 change autoincrement mod http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling…._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…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… MySQL manual, especially the option …delayed_insert_limitblogpercona.commanual Delayed_insert_threadsblogpercona.commanual delayed_insert_…Select_full_joinblogpercona.commanual Select_full_range_joinblogpercona.commanual Select_rangeblogpercona.commanual Select_range_checkblogpercona.commanual Select…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… orphans. mysql> INSERT INTO Foo (A,B) VALUES (111,2222), (333,444); mysql> INSERT INTO Bar (ID,X,Y) VALUES (21,333,444); mysql> SET FOREIGN_KEY_CHECKS=0; mysql> INSERT INTO Bar (ID,X,Y) VALUES (42,555,666); — THIS IS AN ORPHAN mysql> SELECT Bar…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 21 Comments
… MySQL Server) as otherwise the script will fail second time. If you need result to be even closer to one of INSERT … SELECT… 224576 MySQL thread id 1794751, query id 6994931 localhost root Sending data insert into test select * from sample ——– As you can see INSERT… SELECT has…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 7 Comments
… and assigns the new value to the column. Prior to MySQL 5.1.22 InnoDB used a method to access that… KEY `uniqname` (`name`) ) ENGINE=InnoDB; Insert a value using a LEFT OUTER JOIN: insert into foo(name) select 1 from mutex left outer… will see, the INSERT is ignored and no rows are inserted. The same behaviour as INSERT IGNORE: insert into foo(name) select 1 from mutex…
