PostgreSQL Equivalent of SQLServer's NoLock Hi

Question

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 1

I've done some research and it appears that the NOLOCK hint in SQL Server is roughly the same as READ UNCOMMITTED transaction isolation level. In PostgreSQL, you can set READ UNCOMMITTED, but it is silently upgrades the level to READ COMMITTED. READ UNCOMMITTED is not supported.

PostgreSQL 8.4 documentation for Transaction Isolation: http://www.postgresql.org/docs/8.4/static/transaction-iso.html

Answer 2

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.