Introduction:
In my previous article I have explained about how to Get
system name using sql or c#.net. In
this article I will explain about different transaction levels in sql
server. And which one we should use for better performance.
Explanation:
Transaction isolation level Controls the
default transaction locking behavior for all Microsoft SQL Server SELECT
statements issued by a connection.
Different transaction isolation levels are
·
READ COMMITTED
·
READ UNCOMMITTED
·
REPEATABLE READ
·
SERIALIZABLE
·
SNAPSHOT
READ COMMITTED
Specifies that shared locks
are held while the data is being read to avoid dirty reads, but the data can be changed
before the end of the transaction, resulting in nonrepeatable reads or phantom data.
This option is the SQL Server default.
Implements dirty read, or isolation
level 0 locking, which means that no shared locks are issued and no exclusive
locks are honored. When this option is set, it is possible to read uncommitted
or dirty data; values in the data can be changed and rows can appear or
disappear in the data set before the end of the transaction. This option has
the same effect as setting NOLOCK on all tables in all SELECT statements in a
transaction. This is the least restrictive of the four isolation levels.
Locks are placed on all data that
is used in a query, preventing other users from updating the data, but new
phantom rows can be inserted into the data set by another user and are included
in later reads in the current transaction. Because concurrency is lower than
the default isolation level, use this option only when necessary.
Places a range lock on the
data set, preventing other users from updating or inserting rows into the data
set until the transaction is complete. This is the most restrictive of the four
isolation levels. Because concurrency is lower, use this option only when
necessary. This option has the same effect as setting HOLDLOCK on all tables in
all SELECT statements in a transaction.
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. Data modifications made by other transactions
after the start of the current transaction are not visible to statements
executing in the current transaction. The effect is as if the statements in a
transaction get a snapshot of the committed data as it existed at the start of
the transaction.
These isolation levels
allow different types of behavior.
|
Isolation level
|
Dirty read
|
Nonrepeatable read
|
Phantom
|
|
Read uncommitted
|
Yes
|
Yes
|
Yes
|
|
Read committed
|
No
|
Yes
|
Yes
|
|
Repeatable read
|
No
|
No
|
Yes
|
|
Serializable
|
No
|
No
|
No
|
Transactions must be run
at an isolation level of repeatable read or higher to prevent lost updates that
can occur when two transactions each retrieve the same row, and then later
update the row based on the originally retrieved values. If the two transactions
update rows using a single UPDATE statement and do not base the update on the
previously retrieved values, lost updates cannot occur at the default isolation
level of read committed.
Remarks
Only one of the options can be set at a time, and it remains set
for that connection until it is explicitly changed. This becomes the default
behavior unless an optimization option is specified at the table level in the
FROM clause of the statement.The setting of SET TRANSACTION ISOLATION LEVEL is set at execute or run time and not at parse time.
Syntax
SET TRANSACTION ISOLATION
LEVEL
{ READ COMMITTED
| READ UNCOMMITTED
| REPEATABLE READ
| SERIALIZABLE
}
Examples
This example sets the TRANSACTION ISOLATION LEVEL for the session.
For each Transact-SQL statement that follows, SQL Server holds all of the
shared locks until the end of the transaction.
SET TRANSACTION ISOLATION
LEVEL REPEATABLE
READ
GO
BEGIN TRANSACTION
SELECT * FROM publishers
SELECT * FROM authors
...
COMMIT TRANSACTION
Do you like this article. Then share with your friends. Or like our Facebook page. Or post your comments below…
Comments
Post a Comment