Thanks to all who replied. I really appreciate the great feedback! I will probably end-up using something like the scheme offered below :-) -Clark
----- Original Message ---- From: Dan Kennedy <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Thursday, October 06, 2005 09:42:32 Subject: RE: [sqlite] Maintaining a sequence that's not rowid > > /* Retrieve next id in sequence: */ > > BEGIN; > > SELECT id FROM id_allocator; /* This is the id to use */ > > UPDATE id_allocator SET id = id + 1; > > COMMIT; /* Can't use the id until the transaction successfully commits! > > */ > > Just a side note; Traditionally this is done the other way around, i.e. > update then select. The > reason for this is the (small) window in the critical section for another > session to issue the > select at the same time causing a deadlock. Locking the table exclusively at > the beginnng of the > transaction avoids that problem. True enough. You also need to do "BEGIN EXCLUSIVE;" instead of begin. Or just handle the busy condition.
