… mod http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html I have the same problem. 1. You have… is traditional lock mode == > funcionality of auto_increment like before 5.1.22 # =1 is default value – consecutive lock mode # =2 is interleaved lock mode , i don’t investigate what it do innodb_autoinc_lock_mode=0 2. restart…
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
… locks for everything by DDL operations. If you’re having locking issues with Innodb chances are it is row level locks or auto increment table level lock (mostly with MySQL 5.0 and older MySQL versions). MySQL level locks…
Comment: Some little known facts about Innodb Insert Buffer
December 14, 2010 By 12 Comments
Innodb locking problem with unique keys when inserting CREATE TABLE `male_id` ( `entry_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `jy_id` int(10) unsigned NOT NULL, PRIMARY KEY (`entry_id`), UNIQUE KEY `jy_id` (`jy_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1…
Post: Can having information public hurt consulting business ?
April 30, 2008 By 9 Comments
…, something like “Innodb uses row level locks, unless you’re doing insert in table with auto increment which uses table level auto increment locks unless you’re…
Post: Moving from MyISAM to Innodb or XtraDB. Basics
November 22, 2010 By 2 Comments
… spotted by converting tables to Innodb on restored backup. Full text search indexes, GIS, multi-column auto increment keys are great examples. There are also different limits for MyISAM and Innodb – it is possible to…, almost row by row to avoid holding table lock for long time in Innodb you want larger updates to reduce cost of…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… there were bunch of micro optimizations in Innodb in 5.0 series. Also note the “Auto Increment Issue” is finally solved in 5.1 which may be even better. Q28: My impression (from an external user of Innodb… Heikki – it should be possible to implement online reorganize which locks and reorganizes small portions of table online. Q38: Seems that…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
… auto_increment, `text` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB To remove auto_increment, we (1) create table with the same layout but without auto_increment, (2) flush tables with read lock, (3) swap…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… auto_increment_incrementblogpercona.commanual auto_increment_…Innodb_row_lock_current_waitsblogpercona.commanual Innodb_row_lock_timeblogpercona.commanual Innodb_row_lock_time_avgblogpercona.commanual Innodb_row_lock_time_maxblogpercona.commanual Innodb_row_lock_waitsblogpercona.commanual Innodb…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
… situation: CREATE TABLE update_test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(20) NOT NULL, host_id TINYINT UNSIGNED NOT… insert a new row, our auto-increment counter has increased to 3. This is actually expected behavior; InnoDB checks constraints in the order… out to be. 1500 queries per second all trying to lock and update the same row is not going to end…
Post: To UUID or not to UUID ?
March 13, 2007 By 28 Comments
… not really the problem. What is about Secondary Keys ? For Innodb tables UUID have extremely poor impact on your secondary key… parallel insert/updates) has pretty much table level locks when it comes to auto_increment columns, which is however completely separate problem which…
