Postgresql – How to keep an unique counter per row with PostgreSQL

lockingpostgresql

I need to keep an unique (per-row) revision number in a document_revisions table, where the revision number is scoped to a document, so it's not unique to the whole table, only to the related document.

I initially came up with something like:

current_rev = SELECT MAX(rev) FROM document_revisions WHERE document_id = 123;
INSERT INTO document_revisions(rev) VALUES(current_rev + 1);

But there is a race condition!

I'm trying to solve it with pg_advisory_lock, but the documentation is a bit scarce and I don't fully understand it, and I don't want to lock something by mistake.

Is the following acceptable, or am I doing it wrong, or is there a better solution?

SELECT pg_advisory_lock(123);
current_rev = SELECT MAX(rev) FROM document_revisions WHERE document_id = 123;
INSERT INTO document_revisions(rev) VALUES(current_rev + 1);
SELECT pg_advisory_unlock(123);

Shouldn't I lock the document row (key1) for a given operation (key2) instead? So that would be the proper solution:

SELECT pg_advisory_lock(id, 1) FROM documents WHERE id = 123;
current_rev = SELECT MAX(rev) FROM document_revisions WHERE document_id = 123;
INSERT INTO document_revisions(rev) VALUES(current_rev + 1);
SELECT pg_advisory_unlock(id, 1) FROM documents WHERE id = 123;

Maybe I'm not used to PostgreSQL and a SERIAL can be scoped, or maybe a sequence and nextval() would do the job better?

Best Answer

Assuming you store all revisions of the document in a table, an approach would be to not store the revision number but calculate it based on the number of revisions stored in the table.

It is, essentially, a derived value, not something that you need to store.

A window function can be used to calculate the revision number, something like

row_number() over (partition by document_id order by <change_date>)

and you'll need a column something like change_date to keep track of the order of the revisions.

On the other hand, if you just have revision as a property of the document and it indicates "how many times the document has changed", then I would go for the optimistic locking approach, something like:

update documents
set revision = revision + 1
where document_id = <id> and revision = <old_revision>;

If this updates 0 rows, then there has been intermediate update and you need to inform the user of this.

In general, try to keep your solution as simple as possible. In this case by

avoiding the use of explicit locking functions unless absolutely necessary
having fewer database objects (no per document sequences) and storing fewer attributes (don't store the revision if it can be calculated)
using a single update statement rather than a select followed by an insert or update

Related Solutions

MySQL – How to Get Next Unique Value Without Auto Increment

What is interesting about this situation can be solved using the MyISAM storage.

I answedred a question like this back in April 2012 : How can you have two auto-incremental columns in one table? You need to create one table whose sole purpose is the create sequences of work order for each site

CREATE TABLE site_workorder_seq
(
    SiteID int not null,
    SiteWorkorderNum int not null auto_increment,
    PRIMARY KEY (SiteID,SiteWorkorderNum)
) ENGINE=MyISAM;

Here is a sample loading into this table:

mysql>     DROP DATABASE david;
Query OK, 1 row affected (0.01 sec)

mysql>     CREATE DATABASE david;
Query OK, 1 row affected (0.00 sec)

mysql>     USE david
Database changed
mysql>     CREATE TABLE site_workorder_seq
    ->     (
    ->         SiteID int not null,
    ->         SiteWorkorderNum int not null auto_increment,
    ->         PRIMARY KEY (SiteID,SiteWorkorderNum)
    ->     ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql>     INSERT INTO site_workorder_seq (SiteID) VALUES
    ->     (1),(1),(2),(3),(3),(3),(3),(4),(4),(4),
    ->     (5),(5),(4),(2),(2),(2);
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql>     SELECT * FROM site_workorder_seq;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                1 |
|      1 |                2 |
|      2 |                1 |
|      2 |                2 |
|      2 |                3 |
|      2 |                4 |
|      3 |                1 |
|      3 |                2 |
|      3 |                3 |
|      3 |                4 |
|      4 |                1 |
|      4 |                2 |
|      4 |                3 |
|      4 |                4 |
|      5 |                1 |
|      5 |                2 |
+--------+------------------+
16 rows in set (0.00 sec)

mysql>

Let's look at the the last WorkorderNum from each site

mysql> SELECT SiteID,MAX(SiteWorkorderNum) SiteWorkorderNum
    -> FROM site_workorder_seq GROUP BY SiteID;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                2 |
|      2 |                4 |
|      3 |                4 |
|      4 |                4 |
|      5 |                2 |
+--------+------------------+
5 rows in set (0.05 sec)

mysql>

Now, suppose you want to get the next SiteWorkorderNum for SiteID 3. You could do this:

INSERT INTO site_workorder_seq (SiteID) VALUES (3);
SELECT MAX(SiteWorkorderNum) INTO @nextworkordernum
FROM site_workorder_seq WHERE SiteID=3;
SELECT @nextworkordernum;

Let's run this and see what happens

mysql> INSERT INTO site_workorder_seq (SiteID) VALUES (3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT MAX(SiteWorkorderNum) INTO @nextworkordernum
    -> FROM site_workorder_seq WHERE SiteID=3;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @nextworkordernum;
+-------------------+
| @nextworkordernum |
+-------------------+
|                 5 |
+-------------------+
1 row in set (0.03 sec)

mysql> SELECT * FROM site_workorder_seq;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                1 |
|      1 |                2 |
|      2 |                1 |
|      2 |                2 |
|      2 |                3 |
|      2 |                4 |
|      3 |                1 |
|      3 |                2 |
|      3 |                3 |
|      3 |                4 |
|      3 |                5 |
|      4 |                1 |
|      4 |                2 |
|      4 |                3 |
|      4 |                4 |
|      5 |                1 |
|      5 |                2 |
+--------+------------------+
17 rows in set (0.00 sec)

mysql> SELECT SiteID,MAX(SiteWorkorderNum) SiteWorkorderNum
    -> FROM site_workorder_seq GROUP BY SiteID;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                2 |
|      2 |                4 |
|      3 |                5 |
|      4 |                4 |
|      5 |                2 |
+--------+------------------+
5 rows in set (0.00 sec)

mysql>

As long as you use this one MyISAM apart from all InnoDB tables, you can generate workordernums per site to your hearts content.

Sql-server – Table Lock on INSERT – How to optimize

Are you 100% sure that this process is escalating to a tablock? You can verify this with Adam Machanic's sp_whoisactive. Take a snapshot (execute the proc) while the process is running (and presumably blocking). Collect the locks and the query plan - this will give you information needed to fix this issue. Please note what the spids waiting in the insert are waiting on...If they are waiting on latches -we have a page split situation locks its a blocking/locking issue
Remember lock escalation happens when a single statement graps 5K locks on an table or index. (http://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx) I have found that using the paglock hint (so you take fewer coarser locks) can be used to avoid tablock escalation.
Take a look at the query plan collected in step 1. What is the cardinality estimate of the row being insert (i.e. the select statement)... Is it much greater than 5K? If it is you might want to code a loop - so that you are only inserting 2500 rows at a time...

Best Answer

Related Solutions

MySQL – How to Get Next Unique Value Without Auto Increment

Sql-server – Table Lock on INSERT – How to optimize

Related Question