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.
