Re: Simulating sequences

[Rick James]

> I have the code in a single transaction - with just the two sql statements (the update and the select last_update_id).

No. Leave the UPDATE as a stand-alone transaction. Last_insert_id is local to the connection, so there is no need to put the SELECT inside a BEGIN...COMMIT.

I recommend not even putting the UPDATE inside any other transactions -- get the sequence number before BEGINning the 'real' transaction.

> So, back to the original question: Is the update statement SUPPOSED to lock the row for its full execution (read/increment/update) or is that not thread safe?

It is thread safe. No extra locking is necessary.

> My transaction level on the connection is READ_COMMITTED.

Why? The default tx_isolation may be less problematical.

> Last_insert_id() has two known peculiarities---it reports IDs per connection (whereas your sequence must be general),

That's what's expected; what is the problem, Peter?

> and on multi-row inserts it reports the first id generated, not the last.

The UPDATE is clearly a single-row.

Another thing to try: Make your sequence table MyISAM.

I find that over 99% of SEQUENCE cases can be rewritten as AUTO_INCREMENTs. This leads to more efficient code, less hassle, etc. However, it does require rethinking some of the design, especially if you need the SEQUENCE before you do an INSERT to get the AUTO_INCREMENT.

Please provide a small test case that demonstrates the problem.