Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - ANSI ISOLATIONS levels are very important part of any database.

  • ANSI ISOLATIONS levels are very important part of any database. There are two database servers both have different isolations levels. Due to the same different results are returned to the users. Let us help them by educating regarding various isolations levels and their various behaviors with example. What are the different ANSI ISOLATION Levels? What is the additional ISOLATION Level with SQL Server and explain its behavior?

    Posted on 01-01-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

13  Answers  

Subscribe to Notifications
Previous 1 | 2 Next
  • Score
    8

    SQL Server supports all four isolation levels as defined in ANSI SQL standard. The ANSI standard does not dictate how these need to implemented but SQL Server has implemented them using locks with the exception of RCSI/SI . These isolation levels are:

    Read Uncommitted (aka dirty read): A transaction T1 executing under this isolation level can access data changed by concurrent transaction(s). For example, if a concurrent transaction T2 updates a row R1, it can still be read under T1 even though T2 can potentially roll back later.

    o Pros: No read locks needed to read data (i.e. no reader/writer blocking). Note, T1 still takes transaction duration locks for any data modified.

    o Cons: Data is not guaranteed to be transactionally consistent.

    o Usage: It is typically used in queries/applications where data inconsistency can be tolerated. For example, computing average salary of employees.

    Read Committed: A transaction T1 executing under this isolation level can only access committed data. For example, if a concurrent transaction T2 updates a row R1, it cannot be accessed under T1, in fact T1 will get blocked until T2 either commits or rolls back. The S (i.e. Share) lock is held when accessing the data and is released once the access is complete. So for example, if T1 reads R1, R2, and R3 in a SQL statement, T1 acquires/releases S lock on R1 and then acquires/releases lock on R2. In some cases, the ‘S’ lock is not released until the end of the statement . o Pros: Good compromise between concurrency and consistency.

    o Cons: Locking and blocking. The data can change when accessed multiple times within the same transaction.

    o Usage: Very commonly used isolation level. In fact, this is the default isolation level in SQL Server.

    Repeatable Read: A transaction T1 executing under this isolation level can only access committed data with an additional guarantee that any data read cannot change (i.e. it is repeatable) for the duration of the transaction. SQL Server achieves it by holding an S lock for the duration of the transaction. However, it does not protect phantoms, new data rows that qualify the query predicate can appear.

    Example: Assume there is a table ACCOUNTS that stores customer’s account information along with the money balance.

    Ø Start a transaction (T1) under repeatable read isolation level. Query the ACCOUNTS table with predicate (account_balance > 1000). Let us say it returns 10 rows

    Ø Another transaction (T2) inserts a new row in the ACCOUNTS table with account_balance = 1020 and commits.

    Ø Query the ACCOUNTS table again with the same predicate (account_balance > 1000) and it will return 11 rows. The reason is that transaction T1 only locked the 10 qualifying rows but did not lock the predicate range. With the result, the transaction T2 could insert a new row in the same predicate range.

    o Pros: Higher data consistency.

    o Cons: Locking and blocking. The S locks are held for the duration of the transaction that can lower the concurrency. It does not protect against phantom rows.

    o Usage: Not very common.

    Serializable: A transaction T1 executing under this isolation level provides the highest data consistency including elimination of phantoms but at the cost of reduced concurrency. It prevents phantoms by taking a range lock or table level lock if range lock can’t be acquired (i.e. no index on the predicate column) for the duration of the transaction.

    o Pros: Full data consistency including phantom protection. Serializable isolation level guarantees transactions will end up with one possible serial order with an appearance that concurrent transactions did not interfere with each other. For example, if T1, T2, T3 are running under serializable isolation level, the possible serial orders are (T1, T2, T3), (T1, T3, T2), (T2, T1, T3), (T2, T3, T1), (T3, T1, T2), (T3, T2, T1).

    o Cons: Locking and blocking. The S locks are held for the duration of the transaction that can lower the concurrency.

    o Usage: It is used in cases where data consistency is an absolute requirement.

    In addition to these isolation level, SQL Server, starting with SQL Server 2005 release, added two changes as follows

    Read-Committed-Snapshot (RCSI): This is not a new isolation level but a new implementation of read committed isolation level that does not take any S lock on the data. The word snapshot stems from the fact that query under RCSI sees the snapshot of the database as of the beginning of the statement. It is a better alternative for applications that must access only committed data but without taking locks. SQL Server implements it using row versioning that has some overhead. You can map blocking implementation of read committed isolation level to RCSI by enabling READCOMMITTEDSNAPSHOT option at the database level without forcing any changes to applications.

    o Pros: Non-blocking access to transactionally consistent data.

    o Cons: Some overhead of maintaining row versions.

    o Usage: To minimize reader/writer blocking and to replace ‘read uncommitted’ or NOLOCK access to data with transactional consistent data

    Snapshot Isolation (SI): This is new proprietary isolation level that provides non-blocking access for read operations. The transactions under SI see the snapshot of the database as of the beginning of the transaction. There is no automatic mapping of transaction isolation levels to SI so you must change your application to access data under SI isolation level.

    o Pros: Non-blocking access to transactionally consistent data. Higher consistency than repeatable read.

    o Cons: Some overhead of maintaining row versions.

    o Usage: To get non-blocking access to consistent data across multiple statements within a transaction.

    As an aside, you can explicitly set isolation levels by executing ‘set transaction isolation level ’ or you can override it at an object level in a statement using locking hints

    Replied on Jan 1 2012 3:45AM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    4

    SQL Server supports all isolation levels defined by ANSI. Defined isolation levels can be found on WikiPedia

    Supported Isolation levels are:

    1. Read uncommitted (the lowest level where transactions are isolated only enough to ensure that physically corrupt data is not read)
    2. Read committed (Database Engine default level)
    3. Repeatable read
    4. Serializable (the highest level, where transactions are completely isolated from one another)

    In addition to above isolation levels, SQL Server also supports two transaction isolation levels that use row versioning. They are as follows:

    1. Read committed isolation
    2. Snapshot
    Replied on Jan 2 2012 1:29AM  . 
    Hardik Doshi
    20 · 9% · 2853
  • Score
    5

    The default isolation level in SQL Server is read committed. If the user fires update in one transaction and other user tries to access the same table, the statement will be in wait state until the prior transaction is committed or rolled back.

    Read Uncommitted: This implies dirty reads. The read is immediate without wait so concurrency is good but data could be dirty.

    Read Committed: as mentioned previously, the read might take some time but the result will be consistent, as this level ensures that it will read only last committed value. What happens is, the update statement acquires an exclusive lock on the record and read statement asks for shared lock. So the read statement will have to wait till exclusive lock is released (shred and exclusive are not compatible). This level protects from dirty reads but non-repeatable reads, phantom rows can still be part of the result.

    Repeatable Read: To ensure that, within the same transaction, the same select statement should not produce different result set, this isolation can be used. It protects from dirty reads and change of data that's within a transaction processed by select statement. Phantom rows can still appear.

    Serializable: This is the most secure level of isolation. It protects from dirty reads, non-repeatable reads and phantom rows, but at the cost of concurrency.

    Last but not the least, snapshot level which is new since SQL Server 2005. The snapshot level is very much similar to Serializable in result as it produces no dirty reads, no non-repeatable reads, and no phantom rows. How it's different from Serializable is that it improves the concurrency performance without losing data integrity. Basically it reads the last committed data from tempdb thus does not wait for any other transactions to complete.

    Replied on Jan 2 2012 3:37AM  . 
    manik
    753 · 0% · 42
  • Score
    6

    Isolation is the fence between two transactions. Regardless of what any other transaction is doing, a transaction must be able to continue with the exact same data sets it started with. According to “SQL:2008”, the sixth revision of the ISO and ANSI standard for the SQL database query language, a relational database has the following isolation levels:

    READ UNCOMMITTED Allows statements to read rows that were updated by a transaction before the rows are committed to the database. This isolation level minimizes contention but allows dirty reads and nonrepeatable (phantom) reads.

    READ COMMITTED Allows statements within the current connection and transaction to experience nonrepeatable (phantom) reads but prevents dirty reads (data updated by another connection’s open transaction). This is the default setting for SQL Server 2008.

    REPEATABLE READ Does not allow transactions to read noncommitted modified data (dirty reads) and ensures that shared locks are maintained until the current transaction is completed.

    SERIALIZABLE Does not allow data to be read that has been modified but not committed by other transactions. In addition, no other transactions can update data that has been read by the current transaction until the current transactions is complete. The SERIALIZABLE isolation level protects against phantom reads but causes the highest level of blocking and contention.

    SQL Server also supports two transaction isolation levels that use row versioning. One is a new implementation of read committed isolation, and one is a new transaction isolation level, snapshot.

    • The read committed isolation uses row versioning to provide statement-level read consistency, when the READCOMMITTEDSNAPSHOT database option is set ON. Read operations require only SCH-S table level locks and no page or row locks. When the READCOMMITTEDSNAPSHOT database option is set OFF, which is the default setting, read committed isolation behaves as it did in earlier versions of SQL Server. Both implementations meet the ANSI definition of read committed isolation.

    • The snapshot isolation level uses row versioning to provide transaction-level read consistency. Read operations acquire no page or row locks; only SCH-S table locks are acquired. When reading rows modified by another transaction, they retrieve the version of the row that existed when the transaction started. You can only use Snapshot isolation against a database when the ALLOWSNAPSHOTISOLATION database option is set ON. By default, this option is set OFF for user databases.

    The details about each of these isolation levels are available in the below table:

    alt text

    (Unable to display image due to technical issues. The link to the image is : https://docs.google.com/leaf?id=0B5Ci1SyJI9WrMjk1NWRhOTgtODc5ZS00MmIzLTgxYTctYjMyYzMxMTI1ZTVm&hl=enUS

    Replied on Jan 2 2012 5:05AM  . 
    Vishal Soni
    1027 · 0% · 25
  • Score
    1
    Replied on Jan 2 2012 8:40AM  . 
    Yogesh Kamble
    142 · 1% · 349
  • Score
    1
    Replied on Jan 3 2012 5:49AM  . 
    Sineetha
    106 · 2% · 492
  • Score
    8

    Isolation Levels controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server.The isolation level that your transaction runs in determines how sensitive your application is to changes other users' transactions make, and consequently, how long your transaction must hold locks to protect against these changes.

    ANSI 92 standard isolation levels are:

    1. read uncommitted
    2. read commited
    3. repeatable read
    4. serializable

    Uncommitted Read

    Uncommitted Read, or dirty read, lets a transaction read any data currently on a data page, whether or not that data has been committed. For example, although another user might have a transaction in progress that has updated data, and that transaction is holding exclusive locks on the data, your transaction can read the data anyway, and possibly take further actions based on the values you read. The other user might then decide to roll back his or her transaction, so logically, those changes never occurred.

    Eg. Use the SQL Server 7.0 Query Analyzer, and start two separate connections. Use the pubs database in each one. In the first connection, begin a transaction, but don't commit it:

    BEGIN TRAN
    UPDATE ISOLATION_TEST
       SET col2 = 'New Value'
    

    Now, use the second connection, and change your isolation level before trying to access the same table.

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SELECT * FROM ISOLATION_TEST
    

    All the values in col1 are 0, even though the transaction in the first connection has not committed yet. In fact, the transaction might never commit. If you took some action based on the fact that all the values are 0, you could regret it if the changes turned out not to be permanent. Back in the first connection, roll back the transaction:

    ROLLBACK TRAN
    

    Now rerun the SELECT statement in the second connection to see that all the values are back to what they were before.

    Committed Read

    Committed Read is SQL Server's default isolation level. It ensures that an operation will never read data another application has changed but not yet committed. Because you can never read uncommitted data, if a transaction running with Committed Read isolation revisits data, that data might have changed, or new rows might appear that meet the criteria of the original query. Rows that appear in this way are called phantoms.

    Eg: open two new connections in query analyzer using pubs again. In the first connection, run the following batch:

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    BEGIN TRAN
    SELECT AVG(col1) from ISOLATION_TEST
    

    In the second connection, update the table:

    UPDATE ISOLATION_TEST 
       SET col1 = 500 WHERE col1 = 50
    

    Notice that the update is successful, even though the first connection is still inside a transaction. Go back to the first connection and run the same SELECT statement:

    SELECT AVG(col1) from ISOLATION_TEST
    

    The average value is now different. The default isolation level does not prevent another connection from changing data you have read. Because you are not guaranteed to see the same data if you rerun the SELECT within the transaction, the read operations are not guaranteed to be repeatable.

    Repeatable Read

    The Repeatable Read isolation level adds to the properties of Committed Read by ensuring that if a transaction revisits data or if a query is reissued, the data will not have changed. In other words, issuing the same query twice within a transaction won't pick up any changes to data values that another user's transaction has made. No other user can modify the data that your transaction visits as long as you have not yet committed or rolled back your transaction.

    To see Repeatable Read behavior, close all the connections, and open two new ones in pubs. Issue the same two queries as above, but this time, have the first connection SET ISOLATION LEVEL REPEATABLE READ. The second connection will have to use a slightly different update statement, because the value of 50 for col1 no longer exists:

    UPDATE ISOLATION_TEST 
       SET col1 = 5000 WHERE col1 = 500
    

    This update will block when it tries to update the ISOLATION_TEST table. And the first connection will get the same result when it reissues its original SELECT. Preventing nonrepeatable reads is a desirable safeguard, but it comes at a price. The cost of this extra safeguard is that all the shared locks in a transaction must be held until the completion (COMMIT or ROLLBACK) of the transaction.

    Serializable

    The Serializable isolation level ensures that if a query is reissued, no data will have changed and no new rows will appear in the interim. In other words, you won't see phantoms if the same query is issued twice within a transaction. You pay a price to prevent phantoms. In addition to locking all the data you have read, enforcing the Serializable isolation level requires that SQL Server also lock data that doesn't exist! The Serializable level gets its name from the fact that running multiple serializable transactions at the same time is the equivalent of running them one at a time—that is, serially—regardless of sequence.

    New Transaction Isolation Levels in SQL Server 2005

    Serializable Isolation Level

    The Snapshot Isolation Level works with Row Versioning technology. Whenever the transaction requires a modification for a record, SQL Server first stores the consistence version of the record in the tempdb. If another transaction that runs under Snapshot Isolation Level requires the same record, it can be taken from the version store. This Isolation Level prevents all concurrency related problems just like Serializable Isolation Level, in addition to that it allows multiple updates for same resource by different transactions concurrently.

    Since there is a performance impact with Snapshot Isolation Level it has been turned off by default. The impact is explained below with the sample. You can enable it by altering the database

    Read Committed Snapshot Isolation Level

    This is the new implementation of the Read Committed Isolation Level. It has to be set not at session/connection level but database level. The only different between Read Committed and Read Committed Snapshot is, Read Committed Snapshot is Optimistic whereas Read Committed is Pessimistic. The Read Committed Snapshot differs from Snapshot in two ways; Unlike Snapshot, it always returns latest consistence version and no conflict detection.

    Reference:

    http://www.sqlmag.com/article/tsql3/transaction-isolation-levels

    http://msdn.microsoft.com/en-us/library/ms173763.aspx?WT.mcid=aff-n-in-loc--pd

    Replied on Jan 3 2012 6:10AM  . 
    Sineetha
    106 · 2% · 492
  • Score
    7

    Supported Isolation levels are:
    Read uncommitted
    Read committed
    Repeatable read
    Serializable

    SQL Server also supports two transaction isolation levels that use row versioning. They are as follows:
    Read committed isolation
    Snapshot

    http://beyondrelational.com/blogs/jacob/archive/2008/08/28/introduction-to-sql-server-transaction-isolation-levels.aspx

    http://msdn.microsoft.com/en-us/library/ms173763.aspx?WT.mcid=aff-n-in-loc--pd

    http://msdn.microsoft.com/en-us/library/ms172001.aspx

    Read uncommitted

    Under READ UNCOMMITTED, a query can read data modified by other transactions before they are commited. As the name indicates, you can read UNCOMMITTED data. When you try to read rows, SQL Server will not place a shared lock and hence such queries will not block. This will help the queries to execute faster, compared to READ COMMITTED isolation level.
    Usage of NOLOCK and READ UNCOMMITTED isolation level can cause dirty reads
    Example
    http://beyondrelational.com/blogs/jacob/archive/2008/08/30/sql-server-transaction-isolation-level-repeatable-read.aspx

    Read committed

    The default transaction isolation level of SQL server is READ COMMITTED.

    Under READ COMMITTED isolation levels, changes to the data is not visible to other transactions until the transaction is committed. When reading data, SQL Server will attempt to put a shared lock on all the rows that it reads and when it finds a row exclusively locked by another transaction, the query will wait till the transaction completes and the lock is released. Usage of READ COMMITTED isolation level can cause nonrepeatable reads or phantom data.
    Example
    http://beyondrelational.com/blogs/jacob/archive/2008/08/28/sql-server-transaction-isolation-level-read-committed.aspx

    Repeatable read

    Under REPEATABLE READ isolation level, a query cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.
    Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. Usage of REPEATABLE READ isolation level can cause Phantom Rows.
    concurrency is lower than the default READ COMMITTED isolation level.
    Phantom Rows
    Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. If the current transaction then retries the statement it will retrieve the new rows, which results in phantom reads.
    Example
    http://beyondrelational.com/blogs/jacob/archive/2008/08/30/sql-server-transaction-isolation-level-repeatable-read.aspx

    Serializable

    Under the SERIALIZABLE Isolation Statements cannot read data that has been modified but not yet committed by other transactions. No other transactions can modify data that has been read by the current transaction until the current transaction completes. Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

    Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction.This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction.

    The range locks are held until the transaction completes. This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes.
    Example
    http://beyondrelational.com/blogs/jacob/archive/2008/08/30/sql-server-transaction-isolation-level-serializable.aspx

    Snapshot

    Under the SNAPSHOT Isolation 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.

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

    If a transaction using the SNAPSHOT isolation level accesses data in multiple databases, ALLOWSNAPSHOTISOLATION must be set to ON in each database.
    Example
    http://beyondrelational.com/blogs/jacob/archive/2008/08/30/sql-server-transaction-isolation-level-snapshot.aspx

    Replied on Jan 4 2012 7:04AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    5

    What is Transaction Isolation Level

    Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Isolation levels are described in terms of which concurrency side-effects, such as dirty reads or phantom reads, are allowed.

    Transaction isolation levels control:

    • Whether locks are taken when data is read, and what type of locks are requested.
    • How long the read locks are held.
    • Whether a read operation referencing rows modified by another transaction:
    • Blocks until the exclusive lock on the row is freed.
    • Retrieves the committed version of the row that existed at the time the statement or transaction started.
    • Reads the uncommitted data modification.

    ISO Standard Isolation Levels

    The ISO standard defines the following isolation levels, all of which are supported by the SQL Server Database Engine:

    • Read uncommitted (the lowest level where transactions are isolated only enough to ensure that physically corrupt data is not read)
    • Read committed (Database Engine default level)
    • Repeatable read
    • Serializable (the highest level, where transactions are completely isolated from one another)

    Additional Isolation Levels in SQL Server

    SQL Server also supports two transaction isolation levels that use row versioning. One is a new implementation of read committed isolation, and one is a new transaction isolation level, snapshot.

    1. When the READCOMMITTEDSNAPSHOT database option is set ON, read committed isolation uses row versioning to provide statement-level read consistency. Read operations require only SCH-S table level locks and no page or row locks. When the READCOMMITTEDSNAPSHOT database option is set OFF, which is the default setting, read committed isolation behaves as it did in earlier versions of SQL Server. Both implementations meet the ANSI definition of read committed isolation.
    2. The snapshot isolation level uses row versioning to provide transaction-level read consistency. Read operations acquire no page or row locks; only SCH-S table locks are acquired. When reading rows modified by another transaction, they retrieve the version of the row that existed when the transaction started. You can only use Snapshot isolation against a database when the ALLOWSNAPSHOTISOLATION database option is set ON. By default, this option is set OFF for user databases.

    Here is a very fine explanatin and example by Pinal Dave on Snapshot Isolation level;

    Simple Example of Snapshot Isolation

    Replied on Jan 9 2012 1:53AM  . 
    ATif-ullah Sheikh
    133 · 1% · 391
  • Score
    5

    These terms actually come from the ISO/ANSI standard for SQL-92.

    ANSI isolation levels

    Read phenomenas

    The ANSI/ISO standard SQL 92 speaks of three different phenomenas when a session (S1) reads data that another session (S2) might have changed: dirty read,

    Non-repeatable read and Phantom read dirty read If S1 can read data that S2 has written but not yet commited, this is called a dirty read. It is dirty because S2 might later decide to rollback the transaction which leads to the situation that S1 works with data that actually must be considered as non-existant.

    Non-repeatable read

    S1 reads data which is later changed and commited by S2. If S1 reads the same data again (after S2's commit) and finds it to have changed or to be deleted (according to S2's changes), this is called a non-repeatable read. It is called non-repeatable because the same select statement doesn't return the same data (within the same transaction).

    Phantom read

    S1 reads data (select) with a specific where condition. After this read, S2 inserts some data that meets the S1's where condition and commits the inserted data. When S1 issues a select statement with the same where condition, it finds new records. It is called phantom read because the new records seem to be of phantom origin. A phantom read is thus a special case of a non-repeatable read.

    Isolation levels

    Said SQL 92 standard also speaks of four different isolation levels:

    read uncommitted

    read commited

    repeatable read

    serializable

    According to SQL 92 , a transaction is always in exactly one of these isolation levels. Further: the isolation level cannot change within a transaction. These isolation levels define which read phenomenas can be expected.

    In SQL Server

    Isolation Levels that Experience Dirty Reads

    The only isolation level in which dirty reads are possible is READ UNCOMMITTED. You can also use a NOLOCK hint inside a SELECT query to achieve the same behavior.

    I’m not afraid of the occasional NOLOCK hint or the READ UNCOMMITTED level. It helps on tables that I know are static or tables that are growing (INSERTS and SELECTS but no DELETES or UPDATES). It can be pretty powerful when used correctly. The NOLOCK hint used to be my favorite deadlock killer. But lately, I’ve used it less often, especially when another solution is available.

    The Non-Repeatable Read

    The non-repeatable read occurs when a transaction re-reads a single record and finds that it has been changed or deleted.

    From ISO/ANSI: Process P1 reads a row. Process P2 then modifies or deletes that rows and commits the change. If P1 rereads the row it receives the modified value or discovers the row has been deleted.

    Changing Data

    I like to think of Non-Repeatable Reads as being about reading data that has changed.

    But Non-Repeatable reads are not always a bad thing. In fact they’re often harmless or even required. When data has changed, you have to decide whether you want consistent data or whether you want current data. If you need to rely on consistent data, then a non-repeatable read is detrimental. Here is an example of a non-repeatable read that causes problems:

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    BEGIN TRANSACTION
    
        IF EXISTS(
        	SELECT 1
        	FROM Theatre.AvailableSeats
        	WHERE seat = 'B23')
        BEGIN
    
        	-- imagine a concurrent transaction
        	-- sells seat B23 here and removes the record
        	-- from table Theatre.AvailableSeats
    
        	-- then no rows are returned here:
        	SELECT price
        	FROM Theatre.AvailableSeats
        	WHERE seat = 'B23'
    
        	-- and zero rows are affected here:
        	DELETE FROM Theatre.AvailableSeats
        	WHERE seat = 'B23'
    
        END
    COMMIT
    

    Isolation Levels that Experience Non-Repeatable Reads

    Just like dirty reads, this phenomenon is possible when using the READ UNCOMMITTED isolation level, but it is also possible when using the READ COMMITTED isolation level. And in SQL Server READ COMMITTED is the default isolation level for transactions.

    The Phantom Read Imagine your transaction retrieves records filtered by some criteria. A phantom read occurs when your transaction retrieves records using the same criteria and new records are returned. In this case, either the new records you see have been inserted or data has changed in existing records such that these records now meet the filter criteria. Either way these records are new to you.

    From ISO/ANSI: Process P1 reads the set of rows N that satisfy some search condition. Process P2 then executes statements that generate one or more rows that satisfy the search condition. If P1 repeats the query it obtains a different collection of rows.

    Example It’s cheesy, but I use the following example as a mnemonic. Imagine there’s a database of pop culture, and that transactions can last years: Then we have

    use PopCultureDb
    
    SELECT Name
    FROM CarModels
    WHERE Manufacturer = 'Rolls Royce'
    -- over 1925
    -- query would give new record: "The Phantom I"
    
    SELECT Name
    FROM TonyAwards
    WHERE AwardName = 'Best Musical'
    -- over 1988
    -- query would give new record: "Phantom of the Opera"
    
    SELECT Name
    FROM Movies
    WHERE SagaName = 'Star Wars'
    -- over 1999
    -- query would give new record "Star Wars: Episode I - The Phantom Menace"
    

    In each example if a transaction lasted over the years indicated, the new records are examples of phantom reads.

    SERIALIZABLE Serializable is the most isolated transaction level. Basically when a transaction reads or writes data from the database, that’s what it’s going to be until the end of the transaction:

    From ISO/ANSI: [Execution of concurrent SERIALIZABLE transctions are guaranteed to be serializable which is] defined to be an execution of the operations of concurrently executing SQL-transactions that produces the same effect as some serial execution of those same SQL-transactions. A serial execution is one in which each SQL-transaction executes to completion before the next SQL-transaction begins.

    So that’s it! SERIALIZABLE transactions see database data as if there were no other transactions running at the same time. So no dirty, phantom or non-repeatable reads (but maybe some blocking).

    It’s interesting that the standard defines SERIALIZABLE as the default level. Microsoft doesn’t subscribe to that notion and makes READ COMMITTED the default level.

    Replied on Jan 23 2012 1:20AM  . 
    indika saminda kannangara
    188 · 1% · 251
Previous 1 | 2 Next

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.