…=52020 Can you please explain how really locking in INSERT … ON DUPLICATE KEY UPDATE works. As I recall: Lock 1) Transaction 1 locks row… INSERT Lock 2) Transaction 2 tries to locks row for UPDATE (goes into queue) Lock 3) Transaction 1 tries to locks row for UPDATE (goes into queue) It is reasonable not to release Lock…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
…only occur if you do UPDATE or DELETE or SELECT FOR UPDATE. InnoDB provides REPEATABLE …the same result, regardless other session modifications on that table. This makes reads consistent …the gap locks except for foreign-key constraint checking or duplicate-key checking. The most important difference …
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 … 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… PRIMARY KEY) it would be just incremented and last visit timestamp updated. The benefit of using this feature insted of INSERT + UPDATE could…
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 … with REPLACE was – many people tried to use it like update accessing previous column value, for example doing something like REPLACE… 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
… 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… NULL AUTO_INCREMENT, `name` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET…
Post: How many partitions can you have ?
December 5, 2009 By 18 Comments
… 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… grows even larger if we remove index on column C – the “UPDATE” part of INSERT ON DUPLICATE KEY UPDATE completes in 22 seconds for 1 partition…
Comment: INSERT ON DUPLICATE KEY UPDATE and summary counters.
August 4, 2008 By 46 Comments
… I’m checking whether LAN(my unique key) is duplicate or not,if it duplicate then update the record else insert that LAN No…_name from viewromappingfinal on duplicate key update LAN=values(LAN) it’s working fine if i won’t use romappingfinalid(table primary key). Suggest me how to use the INSERT ON UPDATE KEY using a table primary key but checking the condition on another primary key(LAN).
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 6 Comments
… the document size significantly to over 3 million “words” by duplicating the entire set multiple times. mysql> load data infile ‘/tmp…_tmp_39323566` GROUP BY 1,2 ORDER BY 1 ASC ON DUPLICATE KEY UPDATE `word`=VALUES(`word`), `md5(word)`=VALUES(`md5(word)`), `count(*)`=`count…_tmp_27656998` 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
… seen causing upgrade problems in number of other environments. INSERT ON DUPLICATE KEY UPDATE had a unfair share of replication issues in MySQL 5… this functionality is used. Happily there are only few INSERT ON DUPLICATE KEY UPDATE query instances, and only one of them into table with…
Comment: INSERT ON DUPLICATE KEY UPDATE and summary counters.
September 4, 2011 By 46 Comments
… affected INSERT IGNORE INTO test (md5) values (‘A’) Duplicate entry ‘A’ for key ‘md5′ I think an incredibly useful feature here would… the innodb row id of the row which generated the duplicate key error. This would save us a select statement (either before… column. Someone will answer (why not just use on duplicate key update?) Well this is because i want “on duplicate key return unique_row_id” Cheers //Steve
