SNAPSHOT ISOLATION transactions using versioning instead of locking.

SNAPSHOT ISOLATION transactions using versioning instead of locking. Both transactions shown in the SQL batch that follows will be executed as READ COMMITTED using versioning. alter the database ALTER DATABASE pubs SET ALLOW_SNAPSHOT_ISOLATION ON SET READ_COMMITTED_SNAPSHOT ON GO USE pubs GO SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRAN SQL expression will be executed as READ COMMITTED using versioning END TRAN SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN SQL expression will be executed as READ COMMITTED using versioning END TRAN Whether ALLOW_SNAPSHOT_ISOLATIONis ONor not can be checked for a particular database by the DATABASEPROPERTYEX command. This command returns the current database option or setting for a particular database. The setting to check is the SnapshotIsolationFrameworksetting, as in following code for the pubsdatabase: SELECT DATABASEPROPERTYEX ( pubs , SnapshotIsolationFramework ) As stated earlier, SQL Server does not actually make a copy of a database when a SNAPSHOT transaction is started. Whenever a record is updated, SQL Server stores in TEMPDB a copy (version) of the previously committed value and maintains these changes. All the versions of a record are marked with a timestamp of the transactions that made the change, and the versions are chained in TEMPDB using a linked list. The newest record value is stored in a database page and linked to the version store in TEMPDB. For read access in a SNAPSHOT isolation transaction, SQL Server first accesses from the data page the last committed record. It then retrieves the record value from the version store by traversing the chain of pointers to the specific record version of the data. The code in Table 7-3 shows an example of how SNAPSHOT isolation works. The example uses a table, snapTest, looking like this. it is necessary to run SET ALLOW_SNAPSHOT_ISOLATION ON if that s not done already

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

Bookmark the permalink.

Comments are closed.