14:00

Commitment Issues

30 October 2017

One of the essentials of any database system is that a transaction is either in progress, committed, or rolled back. But consider what happens if…

BEGIN;
UPDATE table SET money=money+100000;
COMMIT;
-- And you get an error that the server has disconnected

Did that transaction commit, or didn’t it? You don’t really know, because you don’t know if the server even received the COMMIT statement; if it didn’t, the transaction might have aborted due to the client disconnection.

This isn’t that important for the vast majority of situations, but if you are (for example) building a two-phase commit architecture, it might be vital to know if the transaction really did commit or not.

In PostgreSQL, you can find this out using txid_current() and txid_status():

BEGIN;
UPDATE table SET money=money+100000;
SELECT txid_current();

 txid_current 
--------------
         8168
(1 row)
COMMIT;
-- And you get an error that the server has disconnected

Now, you can use that result to test the status of that transaction (as long as it is still available in the commit log). So, if a disconnection occurs, the client can reconnect and:

SELECT txid_status(8168);

 txid_status 
-------------
aborted
(1 row)

Now, the client knows it needs to rerun that transaction.

Again, most client applications don’t need this level of detail, but it’s great to have the capability if you do.

There are no comments yet.