… 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 14 Comments
…> START TRANSACTION; transaction1> SELECT * FROM t WHERE i > 20 FOR UPDATE; +——+ | i | +——+ | 21 | | 25 | | 30 | +——+ transaction2> START TRANSACTION; transaction2> INSERT INTO… a SELECT. They only occur if you do UPDATE or DELETE or SELECT FOR UPDATE. InnoDB provides REPEATABLE READ for read-only… = 1. Disables the gap locks except for foreign-key constraint checking or duplicate-key checking. The most important difference between these two options…
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 … values(inet_aton(’192.168.0.1′),1,now()) on duplicate key update hits=hits+1; This example actually shows one more neat… 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…
Post: Duplicate indexes and redundant indexes
August 17, 2006 By 38 Comments
… separate keys inside of storage engine which take space on the disk and in memory and which need to be updated on update/insert delete. Duplicate keys are bad so once you find them get rid of them. Note: Duplicate indexes apply… long values using KEY(A) might be much faster than using KEY(A,B). So unlike in case of duplicate indexes it is…
Post: How many partitions can you have ?
December 5, 2009 By 18 Comments
… which was had data merged into it with INSERT ON DUPLICATE KEY UPDATE statements. The performance was extremely slow. I turned out it… 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… if we remove index on column C – the “UPDATE” part of INSERT ON DUPLICATE KEY UPDATE completes in 22 seconds for 1 partition and…
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… 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…
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…_39323566` GROUP BY 1,2 ORDER BY 1 ASC ON DUPLICATE KEY UPDATE `word`=VALUES(`word`), `md5(word)`=VALUES(`md5(word)`), `count(*)`=`count…_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
… causing upgrade problems in number of other environments. INSERT ON DUPLICATE KEY UPDATE had a unfair share of replication issues in MySQL 5… functionality is used. Happily there are only few INSERT ON DUPLICATE KEY UPDATE query instances, and only one of them into table with… to change the single application not to use INSERT ON DUPLICATE KEY UPDATE in this instance so it was done. So replication was…
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… 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
