CODEWITHSUNDEEP
sqlitetransactions
gimmeamilk
gimmeamilk

Reputation: 2120

sqlite: create a transaction ID for multi-insert transaction

I am adding a series of records (eg 100) per single transaction. I want each record to have a 'transaction ID' field, which increments for every new transaction and is the same value for each record inserted within a particular transaction. I want this so clients can perform a query and then at some point in future perform the same query, but only retrieve records with a newer transaction ID. What's the best way of doing it? I don't really want to generate the ID at the application level because the DB is accessed by several processes, so I would then have to coordinate ID allocation cross-process. Or I could have a dedicated TXID table with a single autoincrememt column, insert into that before starting the transaction and use the last_insert_rowid() as the TXID, but it seems a little inefficient to be storing a table of incrementing numbers just to generate IDs. Any thoughts? Thanks.

Upvotes: 2

Views: 1504

Answers (1)

Nick Bastin
Nick Bastin

Reputation: 31339

If you want an incrementing integer, you should indeed do what you have suggested - have another table maintaining transaction IDs. You can sweep this table fairly regularly to delete the rows, although they're going to be very small and efficient, so this shouldn't be a large concern.

There are a variety of other possible solutions, but as you've already figured out, most of them don't work in your situation (possibility of collisions, depending on your required fidelity). If you need to leverage the database to guarantee uniqueness, this is about the only way to do it in SQLite. Alternatively, you could try something like the random or randomblob functions, but there are obviously no uniqueness guarantees made for the PRG.

If you don't need a monotonically increasing integer, you could also generate an RFC 4122 UUID at the application level (these can still conflict, however rarely, if you are writing from many processes - you might also want to tag it with a process-specific string and make it a URN, something like urn:uuid:<tag>+<uuid>). If all the writers are on the same machine, using something like pid for a tag will be sufficient to guarantee uniqueness.

Upvotes: 1

Related Questions