PostgreSQL Equivalent of SQLServer's NoLock Hint

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.

Solution 1:

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.

Solution 2:

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 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

Solution 3:

This is an old question, but I think the actual question has not been answer.

A SELECT query (that does not contain an for update clause) will never lock any rows (or the table) nor will it block concurrent access to the table. Concurrent DML (INSERT, UPDATE, DELETE) will also not block a SELECT statement.

Simply put: there is no need for (nolock) in Postgres. Readers never block writers and writers never block readers