Menu
How do I avoid read locks in my database?
Answers for multiple databases welcome!
467
When a transaction reads a row, the isolation level of the transaction determines if a read lock is acquired. Once a row is read locked, no other transaction can obtain a write lock on it. Acquiring a read lock ensures that a different transaction does not modify or delete a row while it is being read.
At a high level though, locks are used to ensure the integrity of data. When a database resource is locked by one process, another process is not permitted to change the locked data. Locking is necessary to enable the DBMS to facilitate the ACID properties of transaction processing.
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.
140
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.
38
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
