How do I avoid read locks in my database?

Question

Answers for multiple databases welcome!

Answer 1

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.

Answer 2

Jeff Atwood has a good post on this topic:

http://www.codinghorror.com/blog/archives/001166.html

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

Answer 4

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.

Answer 5

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