|
in reply to Re: Sequences, last_insert_id, SQLite, and Oracle
in thread Sequences, last_insert_id, SQLite, and Oracle
This isn't safe for any kind of multi-user use. Say user A is in the middle of a transaction and asks for a sequence. The global id is incremented, but since A has not completed the transaction, he does not commit. Now B comes along and asks for a sequence. He sees the previous value of the global id, which is then incremented and given to him -- the same value A had got!
This can only be safe if the sequence-pulling is atomic, for example if it is done on separate database handles than the ones A and B use for the rest of their applications. |
Re^3: Sequences, last_insert_id, SQLite, and Oracle
by terce (Friar) on Jun 14, 2005 at 13:42 UTC
|
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. | [reply] |
|
|
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
| [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] |
|
|
|
|
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] |
|
|
|
|
