Kbase P120988: SQL Server 2005 SNAPSHOT Isolation & OpenEdge DataServer
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
08/12/2006 |
|
Status: Unverified
GOAL:
SQL Server 2005 SNAPSHOT Isolation & OpenEdge DataServer
GOAL:
What are differences between ABL and SQL server 2005 default isolation level?
GOAL:
Progress position regarding SQL server 2005 snapshot isolation level
FACT(s) (Environment):
OpenEdge 10.x
MS SQL DataServer
MS SQL 2005
FIX:
The OpenEdge ABL is pessimistic and transactional (OLTP) in nature and for the OpenEdge DataServer technology to emulate the NO-LOCK/EXCLUSIVE-LOCK lock types of the OpenEdge database; it uses the READ UNCOMMITTED isolation level to provide the best and most efficient match.
SQL Server 2005 SNAPSHOT ISOLATION (row versioning) provides an optimistic locking environment for read intensive operations (OLAP). However, READ UNCOMMITTED isolation is still recommended for transaction-intensive (OLTP) applications due to the added load imposed on the server running update transactions with snapshot isolation.
The OpenEdge DataServer for MSS will function against a SNAPSHOT ISOLATION (row versioned) enabled MSS database but any OpenEdge ABL logic that relies on pessimistic lock management (for instance, conditions where the OpenEdge client might issue a ?<table.field> is in use by USER ABC? message will be affected and therefore alter application behavior). Conflict resolution is also handled differently between pessimistic and optimistic locking models and so data integrity issues will also be of concern if SNAPSHOT ISOLATION is used with the OpenEdge Dataserver technology. To obtain the best concurrency control and inter-transactional protection in your ABL applications, Progress software recommends not using SNAPSHOT isolation.
SNAPSHOT isolation, in effect, provides alternate implementations of SERIALIZABLE and READ COMMITTED levels of isolation that use optimistic locking to control concurrent access rather than pessimistic locking. Unless your OpenEdge ABL application requires one of these two isolation levels, Progress recommends that customers consider neither transaction-level nor statement-level SNAPSHOT isolation for their applications using the DataServer for MS SQL Server. While SNAPSHOT isolation can be used to emulate SERIALIZABLE and READ COMMITTED isolation levels, Progress Software also recommends not using SNAPSHOT isolation to achieve them.