Re^4: Sequences, last_insert_id, SQLite, and Oracle

[etcshadow]

update: Plus, thinking about it, in the situation you describe the A transaction should lock the sequence table until it is complete, and the B transaction would have to wait until A completed and removed the lock.

Sequences are an oracle construct that are NOT tables. You cannot lock a sequence. This is, in fact, the whole difference between a sequence and a table that you just select from, increment, and then update*. If sequences did behave transactionally, it would lead to lots of deadlock problems and MASSIVE inter-session contention (which is why you'd get so many deadlocks).

* in truth, it would be possible to implement an oracle sequence using a table, but only via another oracle-specific construct called an "autonomous subtransaction". An autonomous subtransaction is a transaction which commits or rolls back independantly of the transaction which invoked it. The point is, you *must* commit the incrementing of the sequence, *even if* you roll back the insert. Otherwise only one database session at a time would be able to have an open transaction inserting into a given table.

[terce]

I'm not talking about sequences (in an Oracle sense) here. If the OP was building code for Oracle only, then sequences (or any other of the Oracle specific solutions mentioned below) would provide a perfect solution. Unfortunately, SQLite doesn't yet support sequences properly, so another soultion is required.

I guess my example was unclear - I should have labelled my table used as a source of unique ID's something other than "sequence".

It also occurs to me I'm forgetting that Oracle implements implicit transactions, which would make this more problematic.

[etcshadow]

Yeah, unfortunately, there's just no getting around using database-specific features for this sort of thing. Every RDBMS handles these specific issues differently.

The best thing, really, is to write yourself an InsertRow function which abstracts this stuff away, but unfortunately it's gonna have to execute different code for different databases. To my knowledge (and I just looked at the code on CPAN to make sure), Class::DBI doesn't even handle this all that well. Oh, well.