… 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… queue) Lock 3) Transaction 1 tries to locks row for UPDATE (goes into queue) It is reasonable not to release Lock…’t it use Lock 1 instead of Lock 3 for UPDATE operation? Or maybe I am wrong at all?
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
…> 20 FOR UPDATE; +——+ | i | +——+ | 21 | | 25 | | 30 | +——+ transaction2> START TRANSACTION; transaction2> INSERT INTO t… result, regardless other session modifications on that table. This makes … locks except for foreign-key constraint checking or duplicate-key checking. The most important…
Post: INSERT ON DUPLICATE KEY UPDATE and summary counters.
May 29, 2006 By 46 Comments
INSERT … ON DUPLICATE KEY UPDATE is very powerful but often forgotten MySQL feature. It was … primary key, hits int unsigned not null, last_hit timestamp); insert into ipstat values(inet_aton(’192.168.0.1′),1,now()) on duplicate key update…
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 …);. Instead 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 which…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 7 Comments
… 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…) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=InnoDB; Insert a value using a LEFT OUTER JOIN: insert into foo(name) select…
Post: How many partitions can you have ?
December 5, 2009 By 18 Comments
… data inserted to them per insert statement. As I tested the UPDATE path on INSERT OF DUPLICATE KEY UPDATE (adding ON DUPLICATE KEY UPDATE set c=c+1 to my bulk inserts) the… case with just insert. The difference grows even larger if we remove index on column C – the “UPDATE” part of INSERT ON DUPLICATE KEY UPDATE completes in 22…
Post: The story of one MySQL Upgrade
October 8, 2010 By 8 Comments
… also seen causing upgrade problems in number of other environments. INSERT ON DUPLICATE KEY UPDATE had a unfair share of replication issues in MySQL 5… frequently this functionality is used. Happily there are only few INSERT ON DUPLICATE KEY UPDATE query instances, and only one of them into table with…
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 from… we use is INSERT, and it does not use this function so it did not have a bug. INSERT ON DUPLICATE KEY UPDATE, REPLACE, UPDATE all should…
Comment: INSERT ON DUPLICATE KEY UPDATE and summary counters.
July 15, 2008 By 46 Comments
… == 0) mysql_query(“INSERT INTO table VALUES (’1′, ’1′)”); 3) Using INSERT .. ON duplicate KEY UPDATE .. mysql_query(“INSERT INTO table VALUES (’1′, ’1′) ON duplicate KEY UPDATE user_hits = user_hits+1 “); So.. which one is recommended? I repeat.. my update…
Comment: INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO
October 26, 2011 By 15 Comments
… fields that I need in the log). The trigger uses INSERT ON DUPLICATE KEY UPDATE to achieve that. The problem is that I find many… if INSERT … UPDATE might lock for read access (or lock only the index, or something simmilar) while checking for key value existence, as inserts in myisam are concurrent with reads, and then try to lock for write access when performing the update…
