sub insertRecord {
  my $newRow = shift;
  my $id = getSequence;
  execute "insert table (id, data) values ($id, $newRow);";
}

sub getSequence {
  my $seq = execute "select id from sequenceTable;";
  #now increment sequence
  execute "update sequenceTable set id = id + 1;";
  return $seq;
}
[download]

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.

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.

Like others have mentioned, having a select followed by an insert/update based on that value is a race condition waiting to happen.

One fairly cross-platform solution is to do "SELECT foo FROM bar FOR UPDATE" which places an exclusive/write lock on the row. This works for Oracle, MySQL, PostgreSQL, Sybase, and MS SQL Server, according to my experience + a quick Google.

Tragically it doesn't seem to work in SQLite :)

/J

Also, "select ... for update" does nothing to deal with race-conditions on *insert* (which is the case here), only race-conditions around updates and deletes.

The only ways to prevent a race-condition on insert are either to obtain an exclusive lock on the table or by the creative use of a unique constraint.

Anyway, as I mentioned elsewhere in this thread, there *is* a way to deal with this issue in oracle, and it is the "returning ... into" clause (coupled with bidirectional binding). That is, to my knowledge, the best (and nearly the only) way of handling this.

If you *absolutely* needed to have a different way of doing it (not that I can think of why you would want another way... but hey, TIMTOWTDI, I guess), it would be possible to make use of Oracle's dbms_output channel, and have your on-insert trigger do a dbms_output.put(:new.id), and read from the dbms_output channel in your perl code. I speak from experience, though, and this method sucks (for some reason I searched and searched and didn't find the "returning id into" construct until after I had implemented it via dbms_output, and that was a mess. I was glad to go back and replace it with the right way).

------------
:Wq
Not an editor command: Wq
[download]

select myseq.currval from dual;
[download]

with

• "myseq" being the Sequence you using
• "dual" is a table offered by oracle, that has always one entry
• ".currval" is the current value of the Sequence

Thanks — this is an interesting approach I hadn't thought of.

My IDs should ideally be fit for human consumption, so 128 bits is a little long, though. The kind of volume I'm expecting, there won't be more than a few IDs to generate every hour in phase one, then maybe a few a minute in phase two, but with rather fewer than that on average. In terms of address space, an eight- or nine-numeral wide decimal number ought to suffice for the lifetime of the project and that allows for quite some margin of error. Of course I'll allow for more than that in the database, but in thinking of how this would be used I'm comfortable with assuming the user would occasionally (not often) have to handle an identifier like "6126-7245".

In code, it looks something like this:

my $sql = "insert into foo (name,value) values (?,?) returning id into
+ ?";
my $sth = $dbh->prepare($sql);
$sth->bind_param(0, $name);
$sth->bind_param(1, $value);
$sth->bind_param_inout(2, \$id, 1024); # this is a reference that gets
+ written to!
$sth->execute;
print $id; # or whatever... $id has been written into by the execute
[download]

Granted, doing individual calls to bind_param / bind_param_inout is kind of painful and ugly, so when I do this I actually written a wrapper around calling all the binding, but this is the crux of what it does / how it works.

------------
:Wq
Not an editor command: Wq
[download]

This looks like a nice technique. A couple of questions:
1. What's the DML for the id field?
2. What's the SQLite equivalent? (If there is one.)
3. Aren't Oracle sequences safe to use unlocked from within a transaction? I was under the impression that they do work that way, so I can request a sequence and then use it. Obviously slower than what you do above, but a little more straightforward. Hmmmm, is your id just that, a sequence?
Thanks!

Yes, you can do this by accessing the sequence, and then inserting that value (explicitly), as two sequential statements in a transaction. What I was referring to was the standard oracle method of defining a sequence and using an on-insert trigger to bind the sequence to the id column of the table (so that inserts *implicitly* fetch from the sequence). For the case of the sequence used in a trigger (thus your code is not directly accessing the sequence), this is The Way. However, if you're not using triggers to pull from your sequence implicitly, then you can certainly fetch from the sequence and just use that value.

------------
:Wq
Not an editor command: Wq
[download]

In SQLite:

insert into table ...
select last_insert_rowid();
[download]

insert into table ...
select table_seq.currval from dual;
[download]

If you *really* want to be clever, you could set up an oracle package to contain a global session variable, and then make all your auto_increment-emulation triggers update that variable. Then you can write a last_insert_rowid() function to return that value. But you still need the "from dual" in oracle, so the sql is still different.

This is no good if you have concurrent sessions both inserting into the same table. You have no way of knowing that in between when process A inserted into the table and when process A checked the last value of the sequence, that process B hadn't inserted a row and caused the sequence to jump.

The way to handle this is to catch the sequence value that was actually used by the trigger, atomically as part of the insert statement itself. Oracle DOES IN FACT provide a way of doing PRECISELY THAT. You can write an insert statement like "insert into foo ... returning id into ?", and then use in/out binding (see DBI docs for bind_param_inout) to catch the value that is spit out into that trailing bind value.

This method works, reliably. It is easy enough. None of the other methods in this thread do work reliably.

------------
:Wq
Not an editor command: Wq
[download]

That is complete and utter misinformation.

Fortunately Oracle has a much better grasp on concurrency issues than you do.

Oracle sequences are designed to support heavy transactional loads. One thing they sacrifice for this is guaranteed serial-ness of the numbers. Each session gets its own cache of sequence numbers, so that it can avoid needing to lock the sequence every time you ask for a new number.

The number you get from NEXTVAL will be the number you get from CURRVAL, and no other sessions can affect your value, because it is private to your session. You even get an error if you ask for the CURRVAL before you have asked for a NEXTVAL in this session.