PostgreSQL Equivalent of SQLServer's NoLock Hint

Cerin picture Cerin · Mar 7, 2010 · Viewed 61.3k times · Source

In SQLServer, you can use syntax "(nolock)" to ensure the query doesn't lock the table or isn't blocked by other queries locking the same table. e.g.

SELECT * FROM mytable (nolock) WHERE id = blah

What's the equivalent syntax in Postgres? I found some documentation on table locking in PG (http://www.postgresql.org/docs/8.1/interactive/sql-lock.html), but it all seems geared at how to lock a table, not ensure it's not locked.

Answer

Frank Heikens picture Frank Heikens · Mar 7, 2010

A SELECT doesn't lock any table in PostgreSQL, unless you want a lock:

SELECT * FROM tablename FOR UPDATE;

PostgreSQL uses MVCC to minimize lock contention in order to allow for reasonable performance in multiuser environments. Readers do not conflict with writers nor other readers.