11

If I SELECT... FOR UPDATE a row in a transaction, it will obviously block the row from being written to, but will it disallow reads as well? I'd prefer to still be able to read from the row, so if the answer is yes, can you provide a solution to work this?

orokusaki
  • 55,146
  • 59
  • 179
  • 257
  • I imagine if you can't, you'll have to use `READ UNCOMMITTED` – OMG Ponies Aug 19 '12 at 19:55
  • 2
    @OMGPonies: Postgres - like many other DBMS - does **not** allow read uncommitted. –  Aug 19 '12 at 21:33
  • @a_horse_with_no_name: [SQL Server does](http://msdn.microsoft.com/en-us/library/ms173763.aspx), at transaction or table level (though the optimizer can ignore it depending on criteria). [Oracle supports READ UNCOMMITTED](http://www.oracle.com/technetwork/issue-archive/2005/05-nov/o65asktom-082389.html)... – OMG Ponies Aug 19 '12 at 23:10
  • @OMGPonies: I know that SQL Server violates the ACID principle if asked for. But that's about the only one out there if I'm not mistaken. In my eyes it's a dirty workaround for not having proper (i.e. transactionally safe) non-blocking reads –  Aug 20 '12 at 06:28
  • @a_horse_with_no_name: Sybase ASE also supports `READ UNCOMMITTED` (which is not surprising considering that MS SQL Server is derived from it), and I have actually had to use it. We brought up a web site that was read-only, where queries tended to be from a table down a bit in the schema structure, while the replication was happening as on the source, from the top down. Deadlocks galore. We had to change all indexes to `UNIQUE` (by adding any missing PK columns) and use RU isolation. MVCC databases are much nicer in this regard, no need to degrade isolation level. – kgrittn Aug 20 '12 at 19:31

1 Answers1

14

You can read just fine. There are lock modes that prevent reading but this isn't one of them.

http://www.postgresql.org/docs/current/static/explicit-locking.html

Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
Richard Huxton
  • 21,516
  • 3
  • 39
  • 51
  • Excellent, thanks - And, of course this is the exact answer I was hoping for! – orokusaki Aug 19 '12 at 20:23
  • Wish I could upvote this more. Answered the question with no prevarication. – ILoveCode Jan 10 '19 at 15:11
  • Just eye-balling this a little more. Pure `SELECT` statements do not block reads, but another concurrent `SELECT... FOR UPDATE` operation **will** block until the first `SELECT... FOR UPDATE` transaction completes. – Samir May 04 '21 at 08:52
  • You've explicitly asked for a lock on all the rows returned then though, haven't you? It wouldn't make any sense to return the rows *before* you knew you could gain the lock on them. – Richard Huxton May 04 '21 at 13:13