Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options

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

by etcshadow (Priest)
on Jun 15, 2005 at 01:04 UTC ( #466745=note: print w/replies, xml ) Need Help??

in reply to Re^3: Sequences, last_insert_id, SQLite, and Oracle
in thread Sequences, last_insert_id, SQLite, and Oracle

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.

------------ :Wq Not an editor command: Wq

Replies are listed 'Best First'.
Re^5: Sequences, last_insert_id, SQLite, and Oracle
by terce (Friar) on Jun 15, 2005 at 08:27 UTC
    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.
      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.

      ------------ :Wq Not an editor command: Wq

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://466745]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (5)
As of 2020-09-18 20:36 GMT
Find Nodes?
    Voting Booth?
    If at first I donít succeed, I Ö

    Results (113 votes). Check out past polls.