Search code examples
postgresqlnolock

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

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