T-SQL ENHANCEMENTS Transaction 1 Transaction 2 Snapshot Transaction

T-SQL ENHANCEMENTS Transaction 1 Transaction 2 Snapshot Transaction 3 Initial Database Snapshot Snapshot Figure 7-1: Snapshot Versioning The row versioning model is built upon having multiple copies of the data. When reading data, the read happens against the copy, and no locks are held. When writing the data, the write happens against the real data, and it is protected with a write lock. For example, in a system implementing row versioning, user A starts a transaction and updates a column in a row. Before the transaction is committed, user B wants to read the same column in the same row. He is allowed to do the read but will read an older value. This is not the value that A is in the process of updating to, but the value A is updating from. In statement-level SNAPSHOT isolation, the reader always reads the last committed value of a given row, just as READ COMMITTEDdoes in a versioning database. Let s say we have a single-row table (called tab) with two columns: ID and name. Table 7-1 shows a versioning database at READ COMMITTEDisolation. The other transaction isolation level in a versioning database, SERIALIZABLE, is always implemented by the behavior that the reader always reads the row as of the beginning of the transaction, regardless of whether other users changes are committed during the duration of the transaction or not. This was shown qualitatively in Figure 7-1. Table 7-2 shows a specific example of how two transactions interoperate when the SERIALIZABLElevel of SNAPSHOTisolation is used. The difference between this table and Table 7-1 occurs at step 5. Even though user 2 has updated a row and committed the update, user 1, using the SERIALIZABLE transaction isolation level, does not see the

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

Bookmark the permalink.

Comments are closed.