How to avoid read locks in my database?

Question

How to avoid read locks in my database?

Responses to several databases are welcome!

Answer 1

Oracle uses Read isolation isolation mode by default, where the select statement is not blocked by another transaction that modifies the data it reads. From Concurrency Data and Consistency :

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

Answer 2

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) 

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

Answer 3

Jeff Atwood has a good post in this thread:

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

Answer 4

In Firebird, writers never block readers and have no dirty readings. Read-only and snapshot isolation levels.
It uses a multi-generational mechanism (like an oracle, I believe) instead of just blocking a page or post.

Answer 5

PostgreSQL also uses MVCC (Multi-Version Concurrency Control), therefore, using the default transaction isolation level (read with commit), you should never block unless someone is supporting the database (deleting / adding columns / tables / indexes / etc).