Understanding Transactions And Locking In PostgreSQL
Introduction To Locks
Locking is achieved through the SQL LOCK statement and there are many different locking mechanisms to choose from. In our case we will require an ‘Exclusive’ lock on the accounting table to ensure that no other process can post a debit while our function is running. If you’re thinking that accounting functions have to work 100% of the time, don’t worry, the transactions that cannot run are queued so that when your function is done running they will, then, execute.
An exclusive lock will ‘conflict’ with many different locking modes. In other words, your function will have to wait till other locks are released to start, and once started other locks will have to wait for your lock to be released.
In this case we are concerned that all writes have completed to the accounting table so by getting an ‘Exclusive’ lock we know that all INSERT/UPDATE/DELETE actions have finished because those statements will themselves try to get a ‘ROW EXCLUSIVE’ lock (A lesser lock to the full ‘Exclusive’ lock we are trying to obtain).
If there are ‘ROW EXCLUSIVE’ locks outstanding (For example, an update query is running) your function will have to wait for it to complete. Once your function starts running, no insert can achieve a ‘ROW EXCLUSIVE’ lock because your ‘Exclusive’ lock will conflict. The inserts/updates will pile up in a queue and wait for your function to finish before they too are run.
Note that an ‘EXCLUSIVE’ lock is a write only lock. Any other concurrent process will continue to be able to read the tables data. If you need to restrict read access until after your function is done you will want an ‘ACCESS EXCLUSIVE’ lock.
For information about what locks conflict with what other locks see http://www.postgresql.org/docs/current/static/explicit-locking.html.