Hi Andrew, I might have missed the context in my reply - we were discussing Sybase which is one of those databases missing sequence support.
We are a little off topic, but Bas was suggesting a reasonable way around the missing sequences (very reasonable compared with many other attempts to work around it) and I was basically bemoaning the fact that I have to work with such a lame database! Sybase, like SQLlite, does (now) support an auto-incrementing column type, but AFAICT there is no way to find out which id you just inserted. On 4/4/06, Andrew Piskorski <[EMAIL PROTECTED]> wrote: > On Tue, Apr 04, 2006 at 03:02:21PM +1000, Mark Aufflick wrote: > > Doesn't that still serialize all updates requiring access to that sequence? > > No. Sequences in an RDBMS are designed to scale gracefully under > heavy concurrent load. (This is basic stuff, grab any good > intro. book on databases and read up on it.) > > They basically do this by guaranteeing ONLY that the integer you get > from the sequence is unique and generally increasing over time - > that's it. The sequence can skip numbers, and the sequence integers > are NOT necessarily in any particular order by either commit time or > sequence request time. > > > If you used it within a transaction it would also have implications > > with other transactions using that "sequence" especially in a rollback > > situation. > > No, it doesn't, not in any database with real built-in sequence > support, like Oracle or PostgreSQL. If you rollback a transaction > which used a sequence, that sequence integer is simply discarded, > never to be used again. > > Of course, if you are implementing sequences yourself with a helper > table, then yes, you get to worry about all the above issues. (They > are only hard to solve *IF* you also need concurrency.) This is why > it's nice to have real sequences in the RDBMS... > > > In the context of AOLServer you could minimise the impact of these > > problems by initialising a totally seperate pool of db connections > > just for this "sequence" manipulation proc. This could decrease > > AFAICT, you're speculating about solving a problem that never actually > exists. > > There are real RDBMSs, like MS SQL Server, which don't support the > sort of sequences I described above, but those all tend to have some > different but similar mechanism intended to solve the same problems. > I think SQL Server and Sybase use auto-incrementing column values, for > example. > > I bet SQLite, for instance, doesn't provide any such sequence-like > support at all, so you'd emulate it yourself by simply taking an > exclusive lock on a my_sequence table or whatever. But, SQLite is a > lightweight embedded database designed for low or no concurrency, so > by definition, you don't have a concurrency problem anyway! (SQLite > takes an exlusive lock on the whole database for every single write > transaction, so if you DO have a concurrency problem, it's unlikely to > be solely because of your ad-hoc table-based sequence support!) > > But actually I'm wrong, SQLite does support auto-incrementing columns, > and provides a helper function to return the the integer key that just > got automatically generated by your insert: > > http://www.sqlite.org/faq.html#q1 > > And of course, serializing all writes (or even all transactions > period) works just fine if your load is moderate and your application > is carefully designed to insure that ALL your transactions are very > fast. That seems to be exactly the approach that D. Richard Hipp, > creator of SQLite, takes in building his own SQLite-backed websites. > > -- > Andrew Piskorski <[EMAIL PROTECTED]> > http://www.piskorski.com/ > > > -- > AOLserver - http://www.aolserver.com/ > > To Remove yourself from this list, simply send an email to <[EMAIL > PROTECTED]> with the > body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: > field of your email blank. > -- AOLserver - http://www.aolserver.com/ To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]> with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
