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

This is true, although you're assuming that the inserts occur within a transaction - and this isn't necessarily the case.

It was the best fix I could come up with for a system which had to include support for Foxpro 2 (don't ask!) - which had no auto-ids and no transactional support.

I must confess I assumed you weren't working on a multi-user environment, as looking at the SQLite docs I see it doesn't support multi-user updates/inserts on a single database file.

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.
  • Comment on Re^3: Sequences, last_insert_id, SQLite, and Oracle

Replies are listed 'Best First'.
Re^4: Sequences, last_insert_id, SQLite, and Oracle
by etcshadow (Priest) on Jun 15, 2005 at 01:04 UTC
    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

    [download]
[reply]
[d/l]
      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.
[reply]
        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

        [download]
[reply]
[d/l]
Re^4: Sequences, last_insert_id, SQLite, and Oracle
by gaal (Parson) on Jun 14, 2005 at 13:54 UTC
    Preversely, if the database has no transactions then your technique is indeed safe. Likewise if you don't use the sequence inside a transaction -- but if you do have them and want to use them, odds are you'd like to be able to use the sequence with them :-)

    I'm using SQLite for development and will possibly move to Oracle for production. SQLite is fine for applications that are not heavily multiuser: locking certainly exists, it's just not very finely-grained. (There are of course other limitations, like how putting the database on NFS is a bad idea, but for my purposes they don't present a problem.)

[reply]
      Sure. I guess it's just a question of how "platform-agnostic" you need/want to be. Or, to put it another way, you're limited by the lowest common denominator of SQL support amongst your chosen platforms.

      I'm sure you've got good reasons to develop on one platform and then go into production on another (and, of course, I've got no knowledge of the specifics of your project), but, unless your end-users need the application to be based on several different platforms, I would have thought you'll be spared a lot of headaches by picking one platform and sticking to it.

      If the cost of using Oracle from the outset is a limiting factor, perhaps one of the open-source db platforms would provide what you need - I'm thinking particularly of Postgres as I have most experience with it.
[reply]
        SQLite is much easier to just start working with. I can prototype all I want and it's my own private db. I can run it on my laptop. For Oracle I need to have to ration my usage of tablespace, servers, client installation: too much bureaucracy. The deployment environment does have access to Oracle I can piggyback on if I must. Installing other databases may be tricky, but SQLite is so hassle-free to turn out to be fine.

        The database is not a huge part of this project, anyway. I was hoping not to have many issues with it (I still do).

[reply]

In Section Seekers of Perl Wisdom