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

[etcshadow]

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).

[jplindstrom]

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

Of course it does, the "select for update" followed by the upate was for getting a unique sequence number for the PK in the insert.

The next insert won't happen until the first transaction is comitted, since the second "select for update" will block until then (at least that's the locking semantics in Oracle).

This is also precicely why this is bad for concurrency.

Not only are the inserts serialized, the small sequence number table probably fits in a data block or two and is bound to incur a huge amount of block contention for _all_ tables that get their PK like this.

That's why it's a good idea to keep the database abstraction on a higher level, so that Oracle specific sequences can be used on Oracle, etc.

DBI is very useful to abstract away connectivity issues, but it would be nice to have a standard way to manage SQL dialect issues as well, like date formats, limit, PK generation and stuff like that.

/J

[etcshadow]

Well, if by "sequence" you meant "table in which you store a deliberately incremented value", then yes, with all the points you mentioned. The problem with that is, as you mentioned, all the ridiculous amounts of contention you get (and setting yourself up for big problems with deadlocking as a result). Of course, when you say "sequence" in the context of auto-increment tables, specifically in an oracle-specific context, it's pretty fair to assume that "sequence" means "oracle sequence", so I don't feel so bad for misinterpretting :-)

In one of my other replies in this thread I mentioned how you could do that etc, etc. The "right way" would be, in fact, to implement your own sequence by way of autonomous subtransactions (another oracle-specific feature... not to say that no other RDBMSs support them, but certainly not all RDBMSs support them).