…insert_selectblogpercona.commanual Com_install_pluginblogpercona.commanual Com_killblogpercona.commanual Com_loadblogpercona.commanual Com_lock…
Post: SELECT LOCK IN SHARE MODE and FOR UPDATE
August 6, 2006 By 51 Comments
… different from normal SELECT statements. Here is simple example: SESSION1: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into tst…: mysql> commit; Query OK, 0 rows affected (0.01 sec) SESSION2: mysql> select * from tst; Empty set (0.00 sec) mysql> select * from tst lock in…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…,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… and detect orphans early so you can correct them. 1Innodb locking and Foreign Keys 2Instrumentation and the cost of Foreign Keys
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
…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 a lot of lock structs, which means it has locked …
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
…| | 25 | | 30 | +——+ transaction2> START TRANSACTION; transaction2> INSERT INTO t VALUES(26); transaction2> COMMIT; transaction1> select * from t where i > 20 FOR UPDATE…source of information: http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as …
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
… MySQL 5.1.22 InnoDB used a method to access that counter values called “Traditional”. This one uses a special table lock… long running queries like INSERT INTO table1 … SELECT … FROM table2. In version 5.1.22 and later the lock algorithm for the auto… 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…
Post: Concurrent inserts on MyISAM and the binary log
May 14, 2008 By 7 Comments
… log can result in a different order of execution. The MySQL manual actually says this, but not in the clearest way… SELECT gets a lock on the table like this: Locked – read Low priority read lock But on INSERT…SELECT, you’ll see this: Read lock without concurrent inserts That read lock…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… the Meta Data Locking behavior prior to MySQL 5.5.3 Metadata Locking behavior prior to MySQL 5.5.3 Prior to MySQL 5.5… table with the new structure is created and then INSERT INTO new_table SELECT * FROM old_table is done and then RENAME… table metadata lock | select * from test_2 order by id | | 8 | msandbox | localhost | test | Query | 180 | Waiting for table metadata lock | select * from test…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
…(`i`) node2 mysql> insert into autoinc (j) values (‘node2′ ); Query OK, 1 row affected (0.00 sec) node2 mysql> insert into autoinc …happened: node1 mysql> commit; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction node1 mysql> select * from autoinc; +—+——-+ |…
Post: Percona XtraDB Cluster: Failure Scenarios with only 2 nodes
July 25, 2012 By 6 Comments
…mysql> insert into percona values (0,’percona2′,’daniel’); Query OK, 1 row affected (0.02 sec) percona2 mysql> select * from percona; +—-+—————+——–+ | id | inserted…
