Alternatively, you might consider making the id_allocator table have an
auto-increment primary key, and then insert null to have sqlite generate
id's for you, and use last_insert_rowid() to find out the value it
generated.
/* Initialize system */
BEGIN;
CREATE TABLE id_allocator(id INTEGER PRIMARY KEY);
COMMIT;
/* Retrieve next id in sequence: */
INSERT INTO id_allocator (id) VALUES (NULL);
SELECT last_insert_rowid(); /* This is the id to use */
The sqlite documentation here:
http://www.sqlite.org/capi3ref.html#sqlite3_last_insert_rowid
doesn't make it entirely clear, but in a quick test I did sqlite seems
to do the right thing, which is to return the last insert rowid for your
database handle irrespective of whether other db handles did inserts
between your insert and select. I think this implies you don't need a
transaction wrapped around the insert/select.
Dan Kennedy wrote:
/* 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.
__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
