I have the following table of counters:
CREATE TABLE cache ( key text PRIMARY KEY, generation int );
I would like to increment one of the counters, or set it to zero if the corresponding row doesn’t exist yet. Is there a way to do this without concurrency issues in standard SQL? The operation is sometimes part of a transaction, sometimes separate.
The SQL must run unmodified on SQLite, PostgreSQL and MySQL, if possible.
A search yielded several ideas which either suffer from concurrency issues, or are specific to a database:
- Try to
INSERTa new row, and
UPDATEif there was an error. Unfortunately, the error on
INSERTaborts the current transaction.
UPDATEthe row, and if no rows were modified,
INSERTa new row.
- MySQL has an
ON DUPLICATE KEY UPDATEclause.
EDIT: Thanks for all the great replies. It looks like Paul is right, and there’s not a single, portable way of doing this. That’s quite surprising to me, as it sounds like a very basic operation.
MySQL (and subsequently SQLite) also support the REPLACE INTO syntax:
REPLACE INTO my_table (pk_id, col1) VALUES (5, '123');
This automatically identifies the primary key and finds a matching row to update, inserting a new one if none is found.
SQLite supports replacing a row if it already exists:
INSERT OR REPLACE INTO [...blah...]
You can shorten this to
REPLACE INTO [...blah...]
This shortcut was added to be compatible with the MySQL
REPLACE INTO expression.
I would do something like the following:
INSERT INTO cache VALUES (key, generation) ON DUPLICATE KEY UPDATE key = key, generation = generation + 1;
Setting the generation value to 0 in code or in the sql but the using the ON DUP… to increment the value. I think that’s the syntax anyway.