Interested to Buy Any Domain ? << Click Here >> for more details...
Answer Posted / arunyadav007
Isolation Level controls the locking and row versioning
behavior of Transact-SQL statements issued by a connection
to SQL Server.
SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}
READ UNCOMMITTED: Specifies that statements can read rows
that have been modified by other transactions but not yet
committed. This option has the same effect as setting
NOLOCK on all tables in all SELECT statements in a
transaction.
READ COMMITTED: Specifies that statements cannot read data
that has been modified but not committed by other
transactions. This prevents dirty reads. 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.
REPEATABLE READ: Specifies that statements 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.
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. 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.
SERIALIZABLE: Specifies the following:
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.
USE AdventureWorks;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
BEGIN TRANSACTION;
GO
SELECT *
FROM HumanResources.EmployeePayHistory;
GO
SELECT *
FROM HumanResources.Department;
GO
COMMIT TRANSACTION;
GO
| Is This Answer Correct ? | 1 Yes | 0 No |
Post New Answer View All Answers
How to change the system date and time from SQL Plus Terminal ?
What are the 7 disadvantages to a manual system?
How do you create type- insensitive operator?
List few advantages of stored procedure.
Does the order of columns in update statements matter?
Can you change the data type of a column in a table after the table has been created? If so, which command would you use?
What do I need to start working with sql studio? : sql server management studio
What is the difference between MVC and Teir Architecher? Plz explain with Layyered Programming example...? Thanks
Explain few of the new features of sql server 2008 management studio
How to create a view on an existing table in ms sql server?
which table keeps the locking information? : Sql server administration
What is the cartesian product of the table?
What are the difference between “where” and “having” clause in sql server?
Explain concepts of analysis services?
what is the information that can be stored inside a bit column? : Sql server database administration
