…locked. Then, we try to insert another value on the second session: transaction2 > START TRANSACTION; transaction2 > INSERT INTO t VALUES(26); …= 1. Disables the gap locks except for foreign-key constraint checking or duplicate-key checking. The most important difference between these two options…
Comment: INSERT ON DUPLICATE KEY UPDATE and summary counters.
March 23, 2012 By 46 Comments
… how really locking in INSERT … ON DUPLICATE KEY UPDATE works. As I recall: Lock 1) Transaction 1 locks row for INSERT Lock 2) Transaction…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
… this table and so data which was inserted after start of transaction (by ALTER TABLE statement… 1 row affected (0.65 sec) Records: 1 Duplicates: 0 Warnings: 0 SESSION2: root@ubuntu:~/dump# tail…=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@…
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 … key, hits int unsigned not null, last_hit timestamp); insert into ipstat values(inet_aton(’192.168.0.1′),1,now()) on duplicate key… PRIMARY KEY) it would be just incremented and last visit timestamp updated. The benefit of using this feature insted of INSERT + 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 … as update this really will insert NULL value, as col1 is undefined at this stage. INSERT ON DUPLICATE KEY UPDATE is newer feature which… also number of similar cases when you want ether to insert the new row or update stats for existing row. I…
Post: Duplicate indexes and redundant indexes
August 17, 2006 By 38 Comments
… be updated on update/insert delete. Duplicate keys are bad so once you find them get rid of them. Note: Duplicate indexes apply to… will also be able to use longer index. Unlike with duplicate indexes, there are however cases when redundant indexes are helpful… long values using KEY(A) might be much faster than using KEY(A,B). So unlike in case of duplicate indexes it is…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 7 Comments
… and the performance but with a small cost. Queries like INSERT … ON DUPLICATE KEY UPDATE produce gaps on the auto_increment column. To…) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=InnoDB; Insert a value using a LEFT OUTER JOIN: insert into foo(name) select…
Comment: SysBench - benchmark tool
May 21, 2007 By 18 Comments
… certain occasions i receive follwong primary key violation messages… ALERT: failed to execute SQL query: `INSERT INTO sbtest values(5694934,0,’ ‘,’aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy… SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint ‘PK__sbtest__7C8480AE’. Cannot insert duplicate key in object ‘dbo.sbtest’. First i thought…
Post: How many partitions can you have ?
December 5, 2009 By 18 Comments
… got 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 regression became even larger. 1 partition insert…
Comment: INSERT ON DUPLICATE KEY UPDATE and summary counters.
September 4, 2011 By 46 Comments
…=InnoDB INSERT IGNORE INTO test (md5) values (‘A’) 1 row affected INSERT IGNORE INTO test (md5) values (‘A’) Duplicate entry ‘A’ for key ‘md5… row which generated the duplicate key error. This would save us a select statement (either before or after the insert) to determine which… answer (why not just use on duplicate key update?) Well this is because i want “on duplicate key return unique_row_id” Cheers //Steve
