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]
