[postgis-devel] Concurrent Modification and Feature Locking

[Cory Horner]

Kevin wrote:

> The system view pg_locks 
> (http://www.postgresql.org/docs/8.1/static/view-pg-locks.html) might 
> give you a partial solution by identifying what tables currently have 
> locks on them.

Sandro wrote:

>Have you taken a look at the new Long Transaction Support
>in PostGIS 1.1.3 ? 
>
>http://postgis.refractions.net/docs/ch06.html#id2531072
>
Paul wrote:

> http://www.postgresql.org/docs/current/static/sql-select.html#SQL-FOR-UPDATE-SHARE
>
> check the part on NOWAIT


Thanks guys!  Yesterday I implemented my first attempt at row locking 
using SELECT ... FOR UPDATE, but was surprised to discover that this 
query also blocked, even with the query timeout value set.  This led to 
another attempt using a timer thread (did not work well, as postgres 
does not take kindly to dropped connections).  Fortunately Paul has 
discovered NOWAIT which will likely solve all our woes.

Our current workflow is:

SELECT ...  FOR UPDATE NOWAIT
- on success UPDATE ...
- on failure query pg_locks to generate an intelligent exception, citing 
who has the row locked, etc

Thanks again,
Cory.