This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction.

ms sql updating one table from another-20

In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either: Note When you set the READ_COMMITTED_SNAPSHOT option, only the connection executing the ALTER DATABASE command is allowed in the database.

There must be no other open connection in the database until ALTER DATABASE is complete.

Transactions writing data do not block SNAPSHOT transactions from reading data.

During the roll-back phase of a database recovery, SNAPSHOT transactions will request a lock if an attempt is made to read data that is locked by another transaction that is being rolled back.

Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction.

READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions.

I had a requirement wherein I had to fetch 10 random rows from My SQL database and display it on screen.

I used following simple query for retrieving random records from database table.

A transaction cannot be set to SNAPSHOT isolation level that started with another isolation level; doing so will cause the transaction to abort.

If a transaction starts in the SNAPSHOT isolation level, you can change it to another isolation level and then back to SNAPSHOT.

Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data.