… about http://bugs.mysql.com/bug.php?id=52020 Can you please explain how really locking in INSERT … ON DUPLICATE KEY UPDATE works. As I… to locks row for UPDATE (goes into queue) It is reasonable not to release Lock 1, but why MySQL tries to create Lock 3? Why can’t it use Lock 1 instead of Lock 3 for UPDATE operation…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… if you do UPDATE or DELETE or SELECT FOR UPDATE. InnoDB provides… result, regardless other session modifications on that table. This makes … locks except for foreign-key constraint checking or duplicate-key checking. The most important…innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as …
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 introduced in MySQL 4.1 but I still constantly see people….0.1′),1,now()) on duplicate key update hits=hits+1; This example actually shows one more neat feature of MySQL – inet_aton and inet…
Post: INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO
January 18, 2007 By 15 Comments
… update this really will insert NULL value, as col1 is undefined at this stage. INSERT ON DUPLICATE KEY UPDATE is newer feature which came in MySQL 4.1 by advice one of MySQL big users. The question…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 7 Comments
Are you using InnoDB tables on MySQL version 5.1.22 or newer? If so, you probably … and assigns the new value to the column. Prior to MySQL 5.1.22 InnoDB used a method to access that… 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: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 6 Comments
…) Records: 3088896 Duplicates: 0 Warnings: 0 Here is the serial version as run by the native database interface (MySQL): mysql> select word, md5…_tmp_39323566` GROUP BY 1,2 ORDER BY 1 ASC ON DUPLICATE KEY UPDATE `word`=VALUES(`word`), `md5(word)`=VALUES(`md5(word)`), `count(*)`=`count…
Post: The story of one MySQL Upgrade
October 8, 2010 By 8 Comments
… in number of other environments. INSERT ON DUPLICATE KEY UPDATE had a unfair share of replication issues in MySQL 5.0. There are number of… this functionality is used. Happily there are only few INSERT ON DUPLICATE KEY UPDATE query instances, and only one of them into table with… enough to change the single application not to use INSERT ON DUPLICATE KEY UPDATE in this instance so it was done. So replication was…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… of compute resource which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law applies to the… 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: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
…. There are probably similar problems in INSERT IGNORE and ON DUPLICATE KEY UPDATE cases. Now In MySQL 5.1+ it is also possible to fix… not-indexed UPDATE which has to lock all rows in Innodb right now) I however did not get exact plans on this… on systems with 1-2 SATA drives so they are far from optimal for server grade IO subsystems. These are for MySQL…
Post: How many partitions can you have ?
December 5, 2009 By 18 Comments
… dealing with large MySQL data warehouse had the table which was had data merged into it with INSERT ON DUPLICATE KEY UPDATE statements. The performance… 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…
