… really locking in INSERT … ON DUPLICATE KEY UPDATE works. As I recall: Lock 1) Transaction 1 locks row for INSERT Lock 2) Transaction 2 tries to locks row for UPDATE (goes into queue) Lock…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
…lock and the gaps between them with a shared gap lock. This lock doesn’t only affect to SELECT … FOR UPDATE…locks to prevent that from happening. 2- innodb_locks_unsafe_for_binlog = 1. Disables the gap locks except for foreign-key constraint checking or duplicate-key…
Post: INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO
January 18, 2007 By 15 Comments
Jonathan Haddad writes about REPLACE INTO and INSERT ON DUPLICATE KEY UPDATE. Really, Why MySQL has both of these, especially both are … only had ISAM tables with table locks and no transactions support. Of course you could use LOCK TABLES but it is not… of acting as update this really will insert NULL value, as col1 is undefined at this stage. INSERT ON DUPLICATE KEY UPDATE is newer feature…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 7 Comments
… counter values called “Traditional”. This one uses a special table lock called AUTO-INC that remains until the end of the… lock on AUTO-INC. We have recovered the concurrency and the performance but with a small cost. Queries like INSERT … ON DUPLICATE KEY UPDATE produce gaps on…
Post: Innodb performance gotcha w Larger queries.
August 28, 2009 By 7 Comments
… was replacing single value UPDATE with multiple value REPLACE (though I also saw the same problem with INSERT ON DUPLICATE KEY UPDATE) As I went… the SQL for each time. if (trx->isolation_level select_lock_type != LOCK_NONE && trx->mysql_query_str) { /* Scan the MySQL query… this function so it did not have a bug. INSERT ON DUPLICATE KEY UPDATE, REPLACE, UPDATE all should be affected, though I have not tested…
Comment: INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO
October 26, 2011 By 15 Comments
… log). The trigger uses INSERT ON DUPLICATE KEY UPDATE to achieve that. The problem is that I find many locked queries in the db as… wondering if INSERT … UPDATE might lock for read access (or lock only the index, or something simmilar) while checking for key value existence, as… REPLACE solve the issue? Would it work right where INSERT … UPDATE locks?
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… used. There are probably similar problems in INSERT IGNORE and ON DUPLICATE KEY UPDATE cases. Now In MySQL 5.1+ it is also possible… community version. Relaxing Locks Row level replication allows to get relaxed locks in many other cases. Gap or next-key locks which limit concurrency…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… is no external locking or synchronization required. If a query fails on a storage node, then it can be retried on the same… a UNIQUE CHECK * on the base table: `origin_airport_id` * storage node result set merge optimization enabled: ON DUPLICATE KEY UPDATE `origin_airport_id`=VALUES…
Comment: How many partitions can you have ?
December 7, 2009 By 18 Comments
Have you seen http://bugs.mysql.com/bug.php?id=37252 ? This implies it is the locking that causes the slowdown (even for InnoDB/Falcon). Can you try your test with locking the table first, doing the INSERT ON DUPLICATE KEY UPDATE statements, and then unlock?
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…| | init | 0.000008 | | Opening tables | 0.000118 | | System lock | 0.000007 | | setup | 0.000027 | | creating table | 0….table without updating indexes took 18 seconds, and about the same time was spent on … ADD KEY (c), ADD KEY(c); Query OK, 0 rows affected (36.42 sec) Records: 0 Duplicates: …
