<< Click to Display Table of Contents >> Navigation:  EDP > Troubleshooting > Committing EDDs Blocks Other DB Processes

Issue:

 

Some clients using SQL Server have observed that committing EDDs blocks other queries (e.g. reports) from executing until the EDD transaction is committed. This blocking is caused by the transaction locks generated by the EDD being loaded and is the default functionality of SQL Server. However, there are some database options that can be turned on that will change this behavior. Before making these changes, we recommend that you consult your database administrator and read the following documentation provided by Microsoft:

 

http://msdn.microsoft.com/en-us/library/ms173763(v=SQL.90).aspx

 

*************************************************************************************************************************

 

READ COMMITTED

Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

 

The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:

 

If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. The shared locks are released when the statement completes.

If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.

When the READ_COMMITTED_SNAPSHOT database option is ON, you can use the READCOMMITTEDLOCK table hint to request shared locking instead of row versioning for individual statements in transactions running at the READ_COMMITTED isolation level.

 

SNAPSHOT

Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.

 

Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. SNAPSHOT transactions reading data do not block other transactions from writing data. 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. The SNAPSHOT transaction is blocked until that transaction has been rolled back. The lock is released immediately after it has been granted.

 

The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before you can start a transaction that uses the SNAPSHOT isolation level. If a transaction using the SNAPSHOT isolation level accesses data in multiple databases, ALLOW_SNAPSHOT_ISOLATION must be set to ON in each database.

 

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. A transaction starts the first time it accesses data.

 

A transaction running under SNAPSHOT isolation level can view changes made by that transaction. For example, if the transaction performs an UPDATE on a table and then issues a SELECT statement against the same table, the modified data will be included in the result set.

 

**************************************************************************************************************************

 

As highlighted above, using the SNAPSHOT isolation level changes the default functionality of the database such that a query will return the data as it existed prior to the start of any other running transaction. Suppose that Joe is committing an EDD and Fred attempts to run a report. With SNAPSHOT isolation level turned off (default), Fred will have to wait until Joe commits his EDD transaction before getting any data. With SNAPSHOT isolation level turned on, Fred's report will ignore the running transaction and return the data as it existed prior to the start of Joe's transaction. That means that Fred will not see any changes that are made by Joe's transaction.

 

In order to set SNAPSHOT isolation as the default functionality, you can run the following queries.

 

 

Code: