6

How do I avoid read locks in my database?

Answers for multiple databases welcome!

e-sushi
  • 13,786
  • 10
  • 38
  • 57
Mark Harrison
  • 297,451
  • 125
  • 333
  • 465

4 Answers4

3

In SQL Server you can use the with(nolock) keyword in your select statements. For example:

Select table1.columna, table2.columna
from table1 with(nolock), table2 with(nolock)

Make sure to specify with(nolock) for each table/view in the query.

Joe Barone
  • 3,112
  • 3
  • 24
  • 20
  • Which means you read `uncommitted` data with possible disastrous results: http://stackoverflow.com/questions/686724/sql-when-should-you-use-with-nolock/686733#686733 Often this is not a problem, especially with the 'new' snapshot features, but you have to be aware of the consequences: http://www.codinghorror.com/blog/2008/08/deadlocked.html – Jeroen Wiert Pluimers Oct 03 '13 at 07:42
3

In Oracle the default mode of operation is the Read committed isolation level where a select statement is not blocked by another transaction modifying the data it's reading. From Data Concurrency and Consistency:

Each query executed by a transaction sees only data that was committed before the query (not the transaction) began. An Oracle query never reads dirty (uncommitted) data.

LucaM
  • 2,856
  • 1
  • 19
  • 11
3

PostgreSQL also uses MVCC (Multi-Version Concurrency Control), so using the default transaction isolation level (read-committed), you should never block, unless somebody is doing maintainace on th DB (dropping / adding columns / tables / indexes / etc).

Grey Panther
  • 12,870
  • 6
  • 46
  • 64
2

In Firebird writers never block readers and there are no dirty-reads. Only read-commited and snapshot isolation levels.
It uses a multi-generational engine (like oracle i believe) instead of simple page or record locking.

Douglas Tosi
  • 2,310
  • 2
  • 18
  • 18