Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 335
Cerin Avatar asked Mar 06 '10 23:03

Cerin


People also ask

Does Postgres have Nolock?

There is no LOCK TABLE in the SQL standard, which instead uses SET TRANSACTION to specify concurrency levels on transactions. PostgreSQL supports that too; see SET TRANSACTION for details.

What is Nolock hint?

The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not get blocked by other processes. This can improve query performance by removing the blocks, but introduces the possibility of dirty reads.

What is deadlock in PostgreSQL?

In PostgreSQL, when a transaction cannot acquire the requested lock within a certain amount of time (configured by `deadlock_timeout`, with default value of 1 second), it begins deadlock detection.


1 Answers

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.

like image 51
Frank Heikens Avatar answered Sep 23 '22 14:09

Frank Heikens