sqlite - sqlite3 autoincrement - am I missing something? -

i want create unique order numbers each day. ideally, in postgresql instance, create sequence , read these unique numbers, because readback both gets me new number , atomic. @ close of day, i'd reset sequence.

in sqlite3, however, see autoincrement integer field type. set table autoincrement field, , insert record new number (seems awfully inefficient way it, anyway...) when go read max back, task hasn't gone in there , inserted record, thereby causing me read miss, number 1 far advanced (and duplicate of other task reads back.)

conceptually, require:

• fast lock wait other tasks
• increment number
• retrieve number
• unlock

...i don't see how sqlite3. can enlighten me?

in sqlite, autoincrementing fields intended used actual primary keys records. should id orders table.

if want have atomic counter independent of corresponding table records, use table single record. acid ensured transactions:

begin; select number mytable; update mytable set number = ? + 1; commit;