73

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.

Cerin
  • 60,957
  • 96
  • 316
  • 522
  • 2
    Wait, let me see if I understand this. There's an option to IGNORE the locks on a table??? If true, that's a bad idea that ranks up there with the option to ignore existing rows when validating new constraints. – Matthew Wood Mar 07 '10 at 02:00
  • 16
    @Matthew Wood: In general, I would tend to agree. However, ignoring locks is useful for certain cases, like debugging when you want to inspect a table's contents even though it's in the middle of a very large update. Ignoring the lock is preferrable to waiting several minutes/hours for the update to complete. – Cerin Mar 07 '10 at 20:28

5 Answers5

96

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.

Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
  • 7
    This is wrong, it's equivalent of explicit locking with hint `WITH(UPDLOCK)`, but question was how to get No Lock! @matthew-wood's answer is more relevant. – Bogdan Mart Apr 27 '19 at 18:35
  • 4
    @BogdanMart That was his example to do a select with a lock because it implicitly doesn't lock. – swade Jun 13 '19 at 13:56
30

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

GabLeRoux
  • 16,715
  • 16
  • 63
  • 81
Matthew Wood
  • 16,017
  • 5
  • 46
  • 35
  • 11
    A small quote just to highlight the cause of this: _The reason that PostgreSQL only provides two isolation levels is that this is the only sensible way to map the standard isolation levels to the multiversion concurrency control architecture._ – dezso Feb 06 '13 at 08:33
  • 9
    @dezso: +1, but in 9.1 SERIALIZABLE was added, so the docs have been updated to say "...provides three isolation levels..." but is otherwise the same. – Matthew Wood Mar 11 '13 at 16:34
11

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

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) also will not block a SELECT statement.

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

T.S.
  • 18,195
  • 11
  • 58
  • 78
  • Yes, you need. The default isolation level may be different that READ COMMITTED, and the implementation of READ UNCOMMITED may be changed further – serge Mar 02 '22 at 14:54
  • 3
    @serge: no you simply don't need this `(nolock)` hint in Postgres (or Oracle for that matter). Readers never block writers and writers never block readers. –  Mar 02 '22 at 14:57
0

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.

user13950163
  • 11
  • 3
    The question was to know what is the **equivalent** for Postgres, not what is the purpose of nolock. – Alexis Jul 17 '20 at 20:07
-1

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

serge
  • 992
  • 5
  • 8
  • 1
    Postgres does not support read uncommitted - it will never allow dirty reads. Also the claim that `count(*)` is slower than `count(1)` is wrong for every DBMS out there. In fact, in Postgres `count(1)` is even slightly slower. –  Mar 02 '22 at 12:54
  • @a_horse_with_no_name you are completely wrong. 1. PostgreSQL supports all ANSI levels (https://www.postgresql.org/docs/12/sql-set-transaction.html). 2. According to SQL standard COUNT(*) should bring the count of all non-null values, it will check NULL values et always will be slower than COUNT(1) or even COUNT(id) – serge Mar 02 '22 at 14:47
  • 1
    `count(*)` and `count(1)` are completely identical in _what_ they do. You might want to read [this](https://blog.jooq.org/2019/09/19/whats-faster-count-or-count1/) for details on the (false) myth that `count(1)` is faster. Additionally from the link you refer to: "*In PostgreSQL READ UNCOMMITTED is treated as READ COMMITTED.*" - so you'll never get `read uncommitted` in Postgres –  Mar 02 '22 at 14:53
  • As I said in other comment: the default isolation level for a session may be different that READ COMMITTED, and the implementation of READ UNCOMMITED may be changed further. The COUNT() behavior should be considered as compliant to ANSI. You can test it easily with NULL values. – serge Mar 02 '22 at 14:58
  • 1
    But the isolation level will not change the locking behaviour in Postgres. And I am not talking about null values. I am talking about the difference between `count(*)` and `count(1)` which has absolutely no functional difference at all. Did you read the [blog post](https://blog.jooq.org/2019/09/19/whats-faster-count-or-count1/)? –  Mar 02 '22 at 15:02
  • You cannot develop relying on the hypothesis of how isolation levels are implemented actually in the DBMS. Furthermore, Postgres has their own rich mechanism of locking (table- and row-levels) which is used to implement ANSI isolation (https://www.postgresql.org/docs/12/explicit-locking.html). – serge Mar 02 '22 at 15:15
  • 1
    It's not a hypotheses. It's clear documented that there is no `read uncommitted` in Postgres. And it's clearly documented that readers don't block other readers. And readers don't block writers and writers don't block readers. –  Mar 02 '22 at 15:16
  • It's clear documented that PostgreSQL supports READ UNCOMMITTED, however the actual implementation is READ COMMITTED (https://www.postgresql.org/docs/12/sql-set-transaction.html). The internal implementation of ANSI levels may be always changed. – serge Mar 02 '22 at 15:18
  • 1
    So it's documented that there is no READ UNCOMITTED - you said it yourself. –  Mar 02 '22 at 15:19
  • @serge PostgreSQL SQL supports the _syntax_ for specifying READ UNCOMMITTED, but it does not support _executing_ a query in READ UNCOMMITED isolation level. – David Aldridge Mar 02 '22 at 15:20
  • Once again, the documentation said that PostgreSQL supports READ UNCOMMITTED, and you can start a transaction using it. The implementation may be changed further. – serge Mar 02 '22 at 15:21
  • There is absolutely no mechanism in PostgreSQL that will allow a session to see uncommited changes. It cannot be done. – David Aldridge Mar 02 '22 at 15:28