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 8 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: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
… … ODKU on this table, we see the following: (root@localhost) [test]> insert into update_test (username,host_id) values (‘foo’,1) on duplicate key update last… sec) (root@localhost) [test]> INSERT INTO update_test (username, host_id) VALUES (‘foo’, 7) ON DUPLICATE KEY UPDATE host_id=7, last_modified=NOW(); Query… to be. 1500 queries per second all trying to lock and update the same row is not going to end well; deadlocks…
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…
Comment: INSERT ON DUPLICATE KEY UPDATE and summary counters.
March 23, 2012 By 48 Comments
… 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: 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…
Post: Announcing Percona Server 5.1.65-14.0
September 4, 2012 By Leave a Comment
… bug #66301. Concurrent INSERT … ON DUPLICATE KEY UPDATE statements on a table with an AUTO_INCREMENT column could result in spurious duplicate key errors (and, as a… due to some rows being updated rather than inserted) with the default value of innodb_autoinc_lock_mode=1. Bug fixed #1035225…
Post: Announcing Percona Server 5.5.27-28.0
August 23, 2012 By 5 Comments
… bug #66301. Concurrent INSERT … ON DUPLICATE KEY UPDATE statements on a table with an AUTO_INCREMENT column could result in spurious duplicate key errors (and, as a… due to some rows being updated rather than inserted) with the default value of innodb_autoinc_lock_mode=1. Bug fixed #1035225…
