…i > 20 FOR UPDATE; +——+ | i | +——+ | 21 | | 25 | | 30 | +——+ transaction2> START TRANSACTION; transaction2> INSERT INTO t VALUES… only occur if you do UPDATE or DELETE or SELECT FOR UPDATE. InnoDB provides REPEATABLE …en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the …
Comment: INSERT ON DUPLICATE KEY UPDATE and summary counters.
March 23, 2012 By 46 Comments
… http://bugs.mysql.com/bug.php?id=52020 Can you please explain how really locking in INSERT … ON DUPLICATE KEY UPDATE works. As I recall: Lock 1) Transaction 1 locks row for INSERT Lock 2) Transaction… why MySQL tries to create Lock 3? Why can’t it use Lock 1 instead of Lock 3 for UPDATE operation? Or maybe…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… MySQL manual, especially the option …variables, or look for content in the Percona documentation or…delayed_insert_limitblogpercona.commanual Delayed_insert_threadsblogpercona.commanual delayed_insert_…commanual unique_checksblogpercona.commanual updatable_views_with_limitblogpercona….
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 11 Comments
… MySQL 5.1 or aren’t using RBR, then it is possible to set up a dedicated MySQL slave which has log_slave_updates… > ) > engine=innodb; Query OK, 0 rows affected (0.00 sec) mysql> insert into test.demo values (1,1); Query OK, 1 row… to `test`.`demo` will automatically be captured. Insert data in one transaction (two rows): mysql> insert into test.demo values (NULL,2),(NULL…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… due to statement level MySQL Replication which among other requirements, needs all auto-increment values in multiple value insert to be sequential… code simple and same for all insert cases – in fact if the insert is single value insert or the number of values in the…. There are probably similar problems in INSERT IGNORE and ON DUPLICATE KEY UPDATE cases. Now In MySQL 5.1+ it is also possible…
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… Applications it could be counting number of visits per page or IP address, number of times particular keyword was searched etc… have its value automatically updated to current timestamp on insert and update. We actually could have omitted now() in insert clause but this would…
Post: Bug#12704861
November 20, 2011 By 1 Comment
…a very serious problem that is fixed in MySQL 5.1.60. I’ll repeat here …transaction allocated (nonfree=TRUE) before storing the BLOBs, or freed (nonfree=FALSE) before committing the mini-transaction… When columns are being made off-page in insert-by-update, invoke btr_mark_freed_leaves(nonfree=TRUE)…
Post: Data mart or data warehouse?
July 15, 2010 By 13 Comments
…. These types of warehouses are almost always “insert only”. Data is very likely never updated or deleted in these databases and they are… a dimension – A “slowly changing dimension” or SCD. The fact table is usually only inserted to, but older data may be purged… much easier than it may be on MySQL, but it is definitely possibly to use MySQL as long as the right tools…
Post: Implementing efficient counters with MySQL
July 1, 2007 By 16 Comments
… efficiently pushing few updates back to MySQL server. If you rather use existing solutions you can use memcache + another mysql instance (or simply the… “shadow table” trick – use two tables insert into one and process and truncate another. MySQL offers atomic RENAME TABLE call which can…
Post: INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO
January 18, 2007 By 15 Comments
… 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… maintaining counters in MySQL. There are also number of similar cases when you want ether to insert the new row or update stats for…
