Hi Frederic, Update then select on a single row table is transaction safe. If two users start a transaction and issue update queries the first query to execute will set a lock on that row. The second query will then block on the update waiting to obtain the same lock. In innodb row locks are not released until a transaction commits. This means that the transaction with the successful update can then issue another select to fetch the new row id while the first transaction is still waiting on the row lock. When the first transaction commits the row lock will be freed allowing the section transaction to obtain the lock, update, and select the next number in the sequence.
I'm curious why you are using a sequence table to generate unique ids for another table. Why not just change the other table to have an auto_increment primary key and a secondary unique key to replace the current primary key? Innodb uses a special mechanism to allocate auto_increment ids that is much faster than a sequence table... -Eric On 12/16/06, Frederic Wenzel <[EMAIL PROTECTED]> wrote:
Hi, I am using a sequence table as suggested in http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#id2887015 in order to generate new, unique IDs for a table where I have a primary key spanning multiple columns and still need a unique numeric part without being able to use an auto-increment table. I am using "UPDATE translations_seq SET id=LAST_INSERT_ID(id+1)" and then I fetch my newest ID with "select id from translations_seq". While this method is described in the manual as "multi-user safe" I was wondering if this was also transaction safe? When two users start a transaction at the same time (and don't commit it yet) will they get different IDs? Because the UPDATE statement will not take place yet until COMMITing it, I am unsure if it will actually hand out different IDs for both of the transactions. Can anyone enlighten me? Thanks Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- Eric Bergen [EMAIL PROTECTED] http://www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]