Frank, "my experience MySQL returns the the rows in the order that you inserted them"
This is true, if, and only if you have never deleted a record from the table. Like most SQL servers, MySQL leaves deleted records' space in the physical table "unoccupied", but still available. When you insert a record, it first checks if the new record can not be inserted into an already allocated space (previously occupied by a valid record). If so, it will insert it there, else it will be appended to the table. This will explain the order in which your records are listed. Record 5 either got inserted into an open space, OR it was inserted while the other client thread/transaction inserted the other 4 records. If you optimize your table, then only is it truly purged from space previously occupied by deleted records. To answer your question though, the previous situation has nothing to do with your autoinc values, which will always be incremented - guaranteed. This behaviour can be changed though if you actually specify a value for an AUTOINC column during the insert, and thereby not allow MySQL to do or follow it's normal course in incrementing the autoinc. Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents http://www.scibit.com/products/mysqlx http://www.scibit.com/products/mascon > > -----Original Message----- > From: "Frank Sonntag" <[EMAIL PROTECTED]> > To: "mysql@lists.mysql.com" <mysql@lists.mysql.com> > CC: > Subject: are autoincrement values not always increasing in innodb? > Sent: Mon, 27 Dec 2004 00:45:37 GMT > Received: Mon, 27 Dec 2004 00:49:46 GMT > Read: Tue, 28 Dec 2004 10:38:11 GMT > Hi, > > does InnoDB guarantee that the values of an autoincrement column do always > increase? > What happened to me is that a select * from my_table returns something like > > id | ... > > 10 > 11 > 5 > 12 > 13 > > where id is defined as int(10) unsigned NOT NULL auto_increment > and is the primary key of the table. > The inserts corresponding to ids (10, 11, 12, 13) are done inside one > transaction, the insert that generates id = 5, in another (concurrent) one. > > Cheers > Frank > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
