PostgreSQL Equivalent of SQLServer's NoLock Hint
PostgresqlNolockPostgresql Problem Overview
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.
Postgresql Solutions
Solution 1 - Postgresql
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 - Postgresql
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 - Postgresql
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
Solution 4 - Postgresql
The purpose of the nolock or readpast is to see if the record is currenlty locked. The user can use this in an update to see if the record identified was changed (rowsaffected); if the record was not locked, then therowsaffected would be 1; if o, then the record is locked
Based upon that outcome, then the user can use a select for update to lock it for their own use.
Solution 5 - Postgresql
Every SQL statement is an implicit transaction. The NOLOCK hint corresponds to READ UNCOMMITTED (DIRTY READ) transaction isolation level.
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT COUNT(1) FROM my_table;
END;
Actually, this code do the same that BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED but guarantees the expected behavior further.
Also avoid to use COUNT(*) except you really need it