IsolationLevel Enumerazione
Definizione
Importante
Alcune informazioni sono relative alla release non definitiva del prodotto, che potrebbe subire modifiche significative prima della release definitiva. Microsoft non riconosce alcuna garanzia, espressa o implicita, in merito alle informazioni qui fornite.
Specifica il comportamento di blocco della transazione per la connessione.
Questa enumerazione supporta una combinazione bit per bit dei rispettivi valori dei membri.
public enum class IsolationLevelpublic enum IsolationLevel[System.Flags]
public enum IsolationLeveltype IsolationLevel = [<System.Flags>]
type IsolationLevel = Public Enum IsolationLevel- Ereditarietà
- Attributi
Campi
| Chaos | 16 | Le modifiche in sospeso dalle transazioni più isolate non possono essere sovrascritte. |
| ReadCommitted | 4096 | La condivisione dei blocchi viene mantenuta durante la lettura dei dati per evitare letture dirty, anche se è possibile modificare i dati prima del termine della transazione, con conseguente produzione di letture non ripetibili o dati fantasma. |
| ReadUncommitted | 256 | È possibile una lettura dirty, ovvero non verrà emesso alcun blocco condiviso, né verrà rispettato alcun blocco esclusivo. |
| RepeatableRead | 65536 | I blocchi sono posizionati su tutti i dati utilizzati in una query, impedendo l'aggiornamento dei dati da parte di altri utenti. Vengono impedite le letture non ripetibili, ma possono essere presenti righe fantasma. |
| Serializable | 1048576 | Viene posizionato un blocco di intervallo sull'oggetto DataSet, per impedire ad altri utenti di aggiornare o immettere righe nel dataset fino al termine della transazione. |
| Snapshot | 16777216 | Riduce il blocco archiviando una versione dei dati che può essere letta da un'applicazione mentre gli stessi dati vengono modificati da un'altra applicazione. Indica che da una transazione non è possibile vedere le modifiche apportate in altre transazioni, anche se viene ripetuta la query. |
| Unspecified | -1 | Verrà utilizzato un livello di isolamento diverso da quello specificato, ma il livello non potrà essere determinato. |
Esempio
Questa applicazione illustra come usare IsolationLevel in DbTransaction. L'esempio illustra quali dei comportamenti seguenti sono consentiti nei diversi livelli di isolamento:
Letture sporche.
Letture non ripetibili.
Fantasmi.
Questa applicazione verrà eseguita nei livelli di isolamento seguenti:
ReadUncommitted
ReadCommitted
RepeatableRead
Serializable
Snapshot
La classe PhantomReadThreads dimostra se la transazione specifica consente il comportamento di lettura fantasma. Se la transazione consente il comportamento, i thread funzioneranno nell'ordine seguente:
Nel primo thread selezionare i prodotti (Tutti).
Nel secondo thread inserire un nuovo prodotto.
Eseguire il commit della transazione nel secondo thread.
Selezionare di nuovo i prodotti.
Eseguire il commit della transazione nel primo thread.
Se la transazione consente il comportamento, le due operazioni Select otterranno i diversi risultati.
La classe NonrepeatableReadThreads dimostra se la transazione specifica consente il comportamento Di lettura nonrepeatable. Se la transazione consente il comportamento, i thread funzioneranno nell'ordine seguente:
Nel primo thread selezionare il prodotto(ProductId=1).
Nel secondo thread aggiornare il valore Quantity(ProductId=1).
Eseguire il commit della transazione nel secondo thread.
Selezionare di nuovo il prodotto.
Eseguire il commit della transazione nel primo thread.
Se la transazione consente il comportamento, le due operazioni Select otterranno i diversi risultati.
La classe ExchangeValuesThreads illustra la differenza tra la transazione Serializable e Snapshot. Per la transazione serializzabile, i thread funzioneranno nell'ordine seguente:
Nel primo thread ottenere il prezzo del prodotto(ProductId=2) e archiviare nella variabile .
Nel primo thread aggiornare il prezzo del prodotto(ProductId=1) con il prezzo di product(ProductId=2).
Eseguire il commit della transazione nel primo thread.
Nel secondo thread ottenere il prezzo del prodotto(ProductId=1) e archiviare nella variabile .
Nel secondo thread aggiornare il prezzo del prodotto(ProductId=2) con il prezzo di product(ProductId=1).
Eseguire il commit della transazione nel secondo thread.
I valori di Price(ProductId=1 e ProductId=2) sono ora uguali al prezzo originale di Product(ProductId=2).
Per la transazione snapshot, i thread funzioneranno nell'ordine seguente:
Nel primo thread ottenere il prezzo del prodotto(ProductId=2) e archiviare nella variabile;
Nel primo thread aggiornare il prezzo del prodotto(ProductId=1) con il prezzo di product(ProductId=2).
Nel secondo thread ottenere il prezzo del prodotto(ProductId=1) dallo snapshot e archiviare nella variabile.
Nel secondo thread aggiornare il prezzo del prodotto(ProductId=2) con il prezzo di product(ProductId=1).
Eseguire il commit della transazione nel secondo thread.
Eseguire il commit della transazione nel primo thread.
Scambiare ora il prezzo dei prodotti(ProductId=1 e ProductId=2).
La classe DirtyReadThreads dimostra se la transazione specifica consente il comportamento Dirty Read. Se la transazione consente il comportamento, i thread funzioneranno nell'ordine seguente:
Nel primo thread iniziare una transazione e aggiungere il valore Quantity(ProductId=1).
Nel secondo thread leggere il valore Quantity e aggiungere di nuovo il valore.
Eseguire il commit della transazione nel secondo thread.
Eseguire il rollback della transazione nel primo thread.
Se la transazione consente il comportamento, il valore Quantity verrà aggiunto due volte.
using System;
using System.Data.SqlClient;
using System.Data;
using System.Threading;
using System.Threading.Tasks;
namespace CSDataIsolationLevel {
// Use the delegate to call the different threads.
public delegate void AsyncAccessDatabase(String connString, IsolationLevel level);
static class DirtyReadThreads {
public static void DirtyReadFirstThread(String connStrig, IsolationLevel level) {
Console.WriteLine("Begin the DirtyReadFirstThread.....");
using (SqlConnection conn = new SqlConnection(connStrig)) {
String cmdText = @"Use DbDataIsolationLevel;
Update dbo.Products set Quantity=Quantity+100 where ProductId=1;
WaitFor Delay '00:00:06';";
conn.Open();
using (SqlTransaction tran = conn.BeginTransaction(level, "DirtyReadFirst")) {
using (SqlCommand command = new SqlCommand(cmdText, conn)) {
command.Transaction = tran;
command.ExecuteNonQuery();
}
if (tran != null)
tran.Rollback();
}
}
Console.WriteLine("Exit from the DirtyReadFirstThread.....");
}
public static void DirtyReadSecondThread(String connStrig, IsolationLevel level) {
Console.WriteLine("Begin the DirtyReadSecondThread.....");
using (SqlConnection conn = new SqlConnection(connStrig)) {
String cmdText = @"Use DbDataIsolationLevel;
WaitFor Delay '00:00:03';
Declare @qty int;
select @qty=Quantity from dbo.Products where ProductId=1;
Update dbo.Products set Quantity=@qty+100 where ProductId=1;";
conn.Open();
using (SqlTransaction tran = conn.BeginTransaction(level, "DirtyReadSecond")) {
using (SqlCommand command = new SqlCommand(cmdText, conn)) {
command.Transaction = tran;
command.ExecuteNonQuery();
}
tran.Commit();
}
}
Console.WriteLine("Exit from the DirtyReadSecondThread.....");
}
}
static class NonrepeatableReadThreads {
public static void NonrepeatableReadFirstThread(String connStrig, IsolationLevel level) {
Console.WriteLine("Begin the NonrepeatableReadFirstThread.....");
using (SqlConnection conn = new SqlConnection(connStrig)) {
String cmdText = @"Use DbDataIsolationLevel;
Select ProductId,ProductName,Quantity,Price
from dbo.Products
where ProductId=1
WaitFor Delay '00:00:06';
Select ProductId,ProductName,Quantity,Price
from dbo.Products
where ProductId=1";
conn.Open();
using (SqlTransaction tran = conn.BeginTransaction(level, "NonrepeatableReadFirst")) {
using (SqlCommand command = new SqlCommand(cmdText, conn)) {
command.Transaction = tran;
using (SqlDataReader reader = command.ExecuteReader()) {
Boolean isFirstReader = true;
do {
Console.WriteLine("It's the result of {0} read:", isFirstReader ? "first" : "second");
TransactionIsolationLevels.DisplayData(reader);
isFirstReader = !isFirstReader;
} while (reader.NextResult() && !isFirstReader);
}
}
tran.Commit();
}
}
Console.WriteLine("Exit from the NonrepeatableReadFirstThread.....");
}
public static void NonrepeatableReadSecondThread(String connStrig, IsolationLevel level) {
Console.WriteLine("Begin the NonrepeatableReadSecondThread.....");
using (SqlConnection conn = new SqlConnection(connStrig)) {
String cmdText = @"Use DbDataIsolationLevel;
WaitFor Delay '00:00:03';
Update dbo.Products set Quantity=Quantity+100 where ProductId=1;";
conn.Open();
using (SqlTransaction tran = conn.BeginTransaction(level, "NonrepeatableReadSecond")) {
using (SqlCommand command = new SqlCommand(cmdText, conn)) {
command.Transaction = tran;
command.ExecuteNonQuery();
}
tran.Commit();
}
}
Console.WriteLine("Exit from the NonrepeatableReadSecondThread.....");
}
}
static class PhantomReadThreads {
public static void PhantomReadFirstThread(String connStrig, IsolationLevel level) {
Console.WriteLine("Begin the PhantomReadFirstThread.....");
using (SqlConnection conn = new SqlConnection(connStrig)) {
String cmdText = @"Use DbDataIsolationLevel;
Select ProductId,ProductName,Quantity,Price
from dbo.Products
WaitFor Delay '00:00:06';
Select ProductId,ProductName,Quantity,Price
from dbo.Products";
conn.Open();
using (SqlTransaction tran = conn.BeginTransaction(level, "PhantomReadFirst")) {
using (SqlCommand command = new SqlCommand(cmdText, conn)) {
command.Transaction = tran;
using (SqlDataReader reader = command.ExecuteReader()) {
Boolean isFirstReader = true;
do {
Console.WriteLine("It's the result of {0} read:", isFirstReader ? "first" : "second");
TransactionIsolationLevels.DisplayData(reader);
isFirstReader = !isFirstReader;
} while (reader.NextResult() && !isFirstReader);
}
}
tran.Commit();
}
}
Console.WriteLine("Exit from the PhantomReadFirstThread.....");
}
public static void PhantomReadSecondThread(String connStrig, IsolationLevel level) {
Console.WriteLine("Begin the PhantomReadSecondThread.....");
using (SqlConnection conn = new SqlConnection(connStrig)) {
String cmdText = @"Use DbDataIsolationLevel;
WaitFor Delay '00:00:03';
INSERT [dbo].[Products] ([ProductName], [Quantity], [Price])
VALUES (N'White Bike', 843, 1349.00)";
conn.Open();
using (SqlTransaction tran = conn.BeginTransaction(level, "PhantomReadSecond")) {
using (SqlCommand command = new SqlCommand(cmdText, conn)) {
command.Transaction = tran;
command.ExecuteNonQuery();
}
tran.Commit();
}
}
Console.WriteLine("Exit from the PhantomReadSecondThread.....");
}
}
// Demonstrates if the specific transaction allows the following behaviors:
// 1. Dirty reads;
// 2. Non-repeatable reads;
// 3. Phantoms.
static class TransactionIsolationLevels {
public static void DemonstrateIsolationLevel(String connString, IsolationLevel level) {
// Before connect the database, recreate the table.
OperateDatabase.CreateTable(connString);
DemonstrateIsolationLevel(connString, level, DirtyReadThreads.DirtyReadFirstThread, DirtyReadThreads.DirtyReadSecondThread);
DisplayData(connString);
Console.WriteLine();
OperateDatabase.CreateTable(connString);
DemonstrateIsolationLevel(connString, level, NonrepeatableReadThreads.NonrepeatableReadFirstThread, NonrepeatableReadThreads.NonrepeatableReadSecondThread);
Console.WriteLine();
OperateDatabase.CreateTable(connString);
DemonstrateIsolationLevel(connString, level, PhantomReadThreads.PhantomReadFirstThread, PhantomReadThreads.PhantomReadSecondThread);
Console.WriteLine();
}
// Demonstrates if the specific transaction allows the specific behaviors.
public static void DemonstrateIsolationLevel(String connString, IsolationLevel level,
AsyncAccessDatabase firstThread, AsyncAccessDatabase secondThread) {
Task[] tasks ={
Task.Factory.StartNew(()=>firstThread(connString, level)),
Task.Factory.StartNew(()=>secondThread(connString, level))
};
Task.WaitAll(tasks);
}
static class ExchangeValuesThreads {
public static void ExchangeValuesFirstThread(String connStrig, IsolationLevel level) {
Console.WriteLine("Begin the ExchangeValuesFirstThread.....");
using (SqlConnection conn = new SqlConnection(connStrig)) {
String cmdText = @"Use DbDataIsolationLevel;
Declare @price money;
select @price=Price from dbo.Products where ProductId=2;
Update dbo.Products set Price=@price where ProductId=1;
WaitFor Delay '00:00:06'; ";
conn.Open();
using (SqlTransaction tran = conn.BeginTransaction(level, "ExchangeValuesFirst")) {
using (SqlCommand command = new SqlCommand(cmdText, conn)) {
command.Transaction = tran;
command.ExecuteNonQuery();
}
tran.Commit();
}
}
Console.WriteLine("Exit from the ExchangeValuesFirstThread.....");
}
public static void ExchangeValuesSecondThread(String connStrig, IsolationLevel level) {
Console.WriteLine("Begin the ExchangeValuesSecondThread.....");
using (SqlConnection conn = new SqlConnection(connStrig)) {
String cmdText = @"Use DbDataIsolationLevel;
WaitFor Delay '00:00:03';
Declare @price money;
select @price=Price from dbo.Products where ProductId=1;
Update dbo.Products set Price=@price where ProductId=2;";
conn.Open();
using (SqlTransaction tran = conn.BeginTransaction(level, "ExchangeValuesSecond")) {
using (SqlCommand command = new SqlCommand(cmdText, conn)) {
command.Transaction = tran;
command.ExecuteNonQuery();
}
tran.Commit();
}
}
Console.WriteLine("Exit from the ExchangeValuesSecondThread.....");
}
}
// Demonstrates the difference between the Serializable and Snapshot transaction
public static void DemonstrateBetweenSnapshotAndSerializable(String connString) {
OperateDatabase.CreateTable(connString);
Console.WriteLine("Exchange Vaules in the Snapshot transaction:");
DemonstrateIsolationLevel(connString, IsolationLevel.Snapshot,
ExchangeValuesThreads.ExchangeValuesFirstThread,
ExchangeValuesThreads.ExchangeValuesSecondThread);
DisplayData(connString);
Console.WriteLine();
Console.WriteLine("Cannot Exchange Vaules in the Serializable transaction:");
OperateDatabase.CreateTable(connString);
DemonstrateIsolationLevel(connString, IsolationLevel.Serializable,
ExchangeValuesThreads.ExchangeValuesFirstThread,
ExchangeValuesThreads.ExchangeValuesSecondThread);
DisplayData(connString);
}
public static void DisplayData(String connString) {
using (SqlConnection conn = new SqlConnection(connString)) {
String cmdText = @"Use DbDataIsolationLevel;
Select ProductId,ProductName,Quantity,Price
from dbo.Products";
conn.Open();
using (SqlCommand command = new SqlCommand(cmdText, conn)) {
using (SqlDataReader reader = command.ExecuteReader()) {
DisplayData(reader);
}
}
}
}
public static void DisplayData(SqlDataReader reader) {
Boolean isFirst = true;
while (reader.Read()) {
if (isFirst) {
isFirst = false;
for (int i = 0; i < reader.FieldCount; i++)
Console.Write("{0,-12} ", reader.GetName(i));
Console.WriteLine();
}
for (int i = 0; i < reader.FieldCount; i++)
Console.Write("{0,-12} ", reader[i]);
Console.WriteLine();
}
}
}
// This class includes database operations. If there's no database 'DbDataIsolationLevel', create the database.
static class OperateDatabase {
public static Boolean CreateDatabase(String connString) {
using (SqlConnection conn = new SqlConnection(connString)) {
String cmdText = @"Use Master;
if Db_Id('DbDataIsolationLevel') is null
create Database [DbDataIsolationLevel];";
using (SqlCommand command = new SqlCommand(cmdText, conn)) {
conn.Open();
command.ExecuteNonQuery();
}
Console.WriteLine("Create the Database 'DbDataIsolationLevel'");
}
return true;
}
// If there's no table [dbo].[Products] in DbDataIsolationLevel, create the table; or recreate it.
public static Boolean CreateTable(String connString) {
using (SqlConnection conn = new SqlConnection(connString)) {
String cmdText = @"Use DbDataIsolationLevel
if Object_ID('[dbo].[Products]') is not null
drop table [dbo].[Products]
Create Table [dbo].[Products]
(
[ProductId] int IDENTITY(1,1) primary key,
[ProductName] NVarchar(100) not null,
[Quantity] int null,
[Price] money null
)";
using (SqlCommand command = new SqlCommand(cmdText, conn)) {
conn.Open();
command.ExecuteNonQuery();
}
}
return InsertRows(connString);
}
// Insert some rows into [dbo].[Products] table.
public static Boolean InsertRows(String connString) {
using (SqlConnection conn = new SqlConnection(connString)) {
String cmdText = @"Use DbDataIsolationLevel
INSERT [dbo].[Products] ([ProductName], [Quantity], [Price]) VALUES (N'Blue Bike', 365,1075.00)
INSERT [dbo].[Products] ([ProductName], [Quantity], [Price]) VALUES (N'Red Bike', 159, 1299.00)
INSERT [dbo].[Products] ([ProductName], [Quantity], [Price]) VALUES (N'Black Bike', 638, 1159.00)";
using (SqlCommand command = new SqlCommand(cmdText, conn)) {
conn.Open();
command.ExecuteNonQuery();
}
}
return true;
}
// Turn on or off 'ALLOW_SNAPSHOT_ISOLATION'
public static Boolean SetSnapshot(String connString, Boolean isOpen) {
using (SqlConnection conn = new SqlConnection(connString)) {
String cmdText = null;
if (isOpen)
cmdText = @"ALTER DATABASE DbDataIsolationLevel SET ALLOW_SNAPSHOT_ISOLATION ON";
else
cmdText = @"ALTER DATABASE DbDataIsolationLevel SET ALLOW_SNAPSHOT_ISOLATION OFF";
using (SqlCommand command = new SqlCommand(cmdText, conn)) {
conn.Open();
command.ExecuteNonQuery();
}
}
return true;
}
}
class Program {
static void Main(string[] args) {
String connString = "Data Source=(local);Initial Catalog=master;Integrated Security=True;Asynchronous Processing=true;";
OperateDatabase.CreateDatabase(connString);
Console.WriteLine();
Console.WriteLine("Demonstrate the ReadUncommitted transaction: ");
TransactionIsolationLevels.DemonstrateIsolationLevel(connString,
System.Data.IsolationLevel.ReadUncommitted);
Console.WriteLine("-----------------------------------------------");
Console.WriteLine("Demonstrate the ReadCommitted transaction: ");
TransactionIsolationLevels.DemonstrateIsolationLevel(connString,
System.Data.IsolationLevel.ReadCommitted);
Console.WriteLine("-----------------------------------------------");
Console.WriteLine("Demonstrate the RepeatableRead transaction: ");
TransactionIsolationLevels.DemonstrateIsolationLevel(connString,
System.Data.IsolationLevel.RepeatableRead);
Console.WriteLine("-----------------------------------------------");
Console.WriteLine("Demonstrate the Serializable transaction: ");
TransactionIsolationLevels.DemonstrateIsolationLevel(connString,
System.Data.IsolationLevel.Serializable);
Console.WriteLine("-----------------------------------------------");
Console.WriteLine("Demonstrate the Snapshot transaction: ");
OperateDatabase.SetSnapshot(connString, true);
TransactionIsolationLevels.DemonstrateIsolationLevel(connString,
System.Data.IsolationLevel.Snapshot);
Console.WriteLine("-----------------------------------------------");
Console.WriteLine("Demonstrate the difference between the Snapshot and Serializable transactions:");
TransactionIsolationLevels.DemonstrateBetweenSnapshotAndSerializable(connString);
OperateDatabase.SetSnapshot(connString, false);
Console.WriteLine();
}
}
}
Imports System.Data.SqlClient
Imports System.Data
Imports System.Threading
Imports System.Threading.Tasks
Namespace CSDataIsolationLevel
' Use the delegate to call the different threads.
Public Delegate Sub AsyncAccessDatabase(connString As [String], level As IsolationLevel)
NotInheritable Class DirtyReadThreads
Private Sub New()
End Sub
Public Shared Sub DirtyReadFirstThread(connStrig As [String], level As IsolationLevel)
Console.WriteLine("Begin the DirtyReadFirstThread.....")
Using conn As New SqlConnection(connStrig)
Dim cmdText As [String] = "Use DbDataIsolationLevel; " & vbCr & vbLf & vbCr & vbLf &
"Update dbo.Products set Quantity=Quantity+100 where ProductId=1;" & vbCr & vbLf &
"WaitFor Delay '00:00:06';"
conn.Open()
Using tran As SqlTransaction = conn.BeginTransaction(level, "DirtyReadFirst")
Using command As New SqlCommand(cmdText, conn)
command.Transaction = tran
command.ExecuteNonQuery()
End Using
If tran IsNot Nothing Then
tran.Rollback()
End If
End Using
End Using
Console.WriteLine("Exit from the DirtyReadFirstThread.....")
End Sub
Public Shared Sub DirtyReadSecondThread(connStrig As [String], level As IsolationLevel)
Console.WriteLine("Begin the DirtyReadSecondThread.....")
Using conn As New SqlConnection(connStrig)
Dim cmdText As [String] = "Use DbDataIsolationLevel;" & vbCr & vbLf & vbCr & vbLf &
"WaitFor Delay '00:00:03'; " & vbCr & vbLf & vbCr & vbLf &
"Declare @qty int;" & vbCr & vbLf &
"select @qty=Quantity from dbo.Products where ProductId=1;" & vbCr & vbLf & vbCr & vbLf &
"Update dbo.Products set Quantity=@qty+100 where ProductId=1;"
conn.Open()
Using tran As SqlTransaction = conn.BeginTransaction(level, "DirtyReadSecond")
Using command As New SqlCommand(cmdText, conn)
command.Transaction = tran
command.ExecuteNonQuery()
End Using
tran.Commit()
End Using
End Using
Console.WriteLine("Exit from the DirtyReadSecondThread.....")
End Sub
End Class
NotInheritable Class NonrepeatableReadThreads
Private Sub New()
End Sub
Public Shared Sub NonrepeatableReadFirstThread(connStrig As [String], level As IsolationLevel)
Console.WriteLine("Begin the NonrepeatableReadFirstThread.....")
Using conn As New SqlConnection(connStrig)
Dim cmdText As [String] = "Use DbDataIsolationLevel; " & vbCr & vbLf & vbCr & vbLf &
"Select ProductId,ProductName,Quantity,Price" & vbCr & vbLf &
"from dbo.Products" & vbCr & vbLf &
"where ProductId=1" & vbCr & vbLf & vbCr & vbLf &
"WaitFor Delay '00:00:06';" & vbCr & vbLf & vbCr & vbLf &
"Select ProductId,ProductName,Quantity,Price" & vbCr & vbLf &
"from dbo.Products" & vbCr & vbLf &
"where ProductId=1"
conn.Open()
Using tran As SqlTransaction = conn.BeginTransaction(level, "NonrepeatableReadFirst")
Using command As New SqlCommand(cmdText, conn)
command.Transaction = tran
Using reader As SqlDataReader = command.ExecuteReader()
Dim isFirstReader As [Boolean] = True
Do
Console.WriteLine("It's the result of {0} read:", If(isFirstReader, "first", "second"))
TransactionIsolationLevels.DisplayData(reader)
isFirstReader = Not isFirstReader
Loop While reader.NextResult() AndAlso Not isFirstReader
End Using
End Using
tran.Commit()
End Using
End Using
Console.WriteLine("Exit from the NonrepeatableReadFirstThread.....")
End Sub
Public Shared Sub NonrepeatableReadSecondThread(connStrig As [String], level As IsolationLevel)
Console.WriteLine("Begin the NonrepeatableReadSecondThread.....")
Using conn As New SqlConnection(connStrig)
Dim cmdText As [String] = "Use DbDataIsolationLevel;" & vbCr & vbLf & vbCr & vbLf &
"WaitFor Delay '00:00:03'; " & vbCr & vbLf & vbCr & vbLf &
"Update dbo.Products set Quantity=Quantity+100 where ProductId=1;"
conn.Open()
Using tran As SqlTransaction = conn.BeginTransaction(level, "NonrepeatableReadSecond")
Using command As New SqlCommand(cmdText, conn)
command.Transaction = tran
command.ExecuteNonQuery()
End Using
tran.Commit()
End Using
End Using
Console.WriteLine("Exit from the NonrepeatableReadSecondThread.....")
End Sub
End Class
NotInheritable Class PhantomReadThreads
Private Sub New()
End Sub
Public Shared Sub PhantomReadFirstThread(connStrig As [String], level As IsolationLevel)
Console.WriteLine("Begin the PhantomReadFirstThread.....")
Using conn As New SqlConnection(connStrig)
Dim cmdText As [String] = "Use DbDataIsolationLevel; " & vbCr & vbLf & vbCr & vbLf &
"Select ProductId,ProductName,Quantity,Price" & vbCr & vbLf &
"from dbo.Products" & vbCr & vbLf & vbCr & vbLf &
"WaitFor Delay '00:00:06';" & vbCr & vbLf & vbCr & vbLf &
"Select ProductId,ProductName,Quantity,Price" & vbCr & vbLf &
"from dbo.Products"
conn.Open()
Using tran As SqlTransaction = conn.BeginTransaction(level, "PhantomReadFirst")
Using command As New SqlCommand(cmdText, conn)
command.Transaction = tran
Using reader As SqlDataReader = command.ExecuteReader()
Dim isFirstReader As [Boolean] = True
Do
Console.WriteLine("It's the result of {0} read:", If(isFirstReader, "first", "second"))
TransactionIsolationLevels.DisplayData(reader)
isFirstReader = Not isFirstReader
Loop While reader.NextResult() AndAlso Not isFirstReader
End Using
End Using
tran.Commit()
End Using
End Using
Console.WriteLine("Exit from the PhantomReadFirstThread.....")
End Sub
Public Shared Sub PhantomReadSecondThread(connStrig As [String], level As IsolationLevel)
Console.WriteLine("Begin the PhantomReadSecondThread.....")
Using conn As New SqlConnection(connStrig)
Dim cmdText As [String] = "Use DbDataIsolationLevel;" & vbCr & vbLf & vbCr & vbLf &
"WaitFor Delay '00:00:03'; " & vbCr & vbLf & vbCr & vbLf &
"INSERT [dbo].[Products] ([ProductName], [Quantity], [Price]) " & vbCr & vbLf &
"VALUES (N'White Bike', 843, 1349.00)"
conn.Open()
Using tran As SqlTransaction = conn.BeginTransaction(level, "PhantomReadSecond")
Using command As New SqlCommand(cmdText, conn)
command.Transaction = tran
command.ExecuteNonQuery()
End Using
tran.Commit()
End Using
End Using
Console.WriteLine("Exit from the PhantomReadSecondThread.....")
End Sub
End Class
' Demonstrates if the specific transaction allows the following behaviors:
' 1. Dirty reads;
' 2. Non-repeatable reads;
' 3. Phantoms.
NotInheritable Class TransactionIsolationLevels
Private Sub New()
End Sub
Public Shared Sub DemonstrateIsolationLevel(connString As [String], level As IsolationLevel)
' Before connect the database, recreate the table.
OperateDatabase.CreateTable(connString)
DemonstrateIsolationLevel(connString, level, AddressOf DirtyReadThreads.DirtyReadFirstThread, AddressOf DirtyReadThreads.DirtyReadSecondThread)
DisplayData(connString)
Console.WriteLine()
OperateDatabase.CreateTable(connString)
DemonstrateIsolationLevel(connString, level, AddressOf NonrepeatableReadThreads.NonrepeatableReadFirstThread, AddressOf NonrepeatableReadThreads.NonrepeatableReadSecondThread)
Console.WriteLine()
OperateDatabase.CreateTable(connString)
DemonstrateIsolationLevel(connString, level, AddressOf PhantomReadThreads.PhantomReadFirstThread, AddressOf PhantomReadThreads.PhantomReadSecondThread)
Console.WriteLine()
End Sub
' Demonstrates if the specific transaction allows the specific behaviors.
Public Shared Sub DemonstrateIsolationLevel(connString As [String], level As IsolationLevel, firstThread As AsyncAccessDatabase, secondThread As AsyncAccessDatabase)
' Dim tasks As Task() = {Task.Factory.StartNew(Function() firstThread(connString, level)), Task.Factory.StartNew(Function() secondThread(connString, level))}
Dim tasks() As Task = {
Task.Factory.StartNew(Sub() firstThread(connString, level)),
Task.Factory.StartNew(Sub() secondThread(connString, level))
}
Task.WaitAll(tasks)
End Sub
Private NotInheritable Class ExchangeValuesThreads
Private Sub New()
End Sub
Public Shared Sub ExchangeValuesFirstThread(connStrig As [String], level As IsolationLevel)
Console.WriteLine("Begin the ExchangeValuesFirstThread.....")
Using conn As New SqlConnection(connStrig)
Dim cmdText As [String] = "Use DbDataIsolationLevel;" & vbCr & vbLf & vbCr & vbLf &
"Declare @price money;" & vbCr & vbLf &
"select @price=Price from dbo.Products where ProductId=2;" & vbCr & vbLf & vbCr & vbLf &
"Update dbo.Products set Price=@price where ProductId=1;" & vbCr & vbLf & vbCr & vbLf &
"WaitFor Delay '00:00:06'; "
conn.Open()
Using tran As SqlTransaction = conn.BeginTransaction(level, "ExchangeValuesFirst")
Using command As New SqlCommand(cmdText, conn)
command.Transaction = tran
command.ExecuteNonQuery()
End Using
tran.Commit()
End Using
End Using
Console.WriteLine("Exit from the ExchangeValuesFirstThread.....")
End Sub
Public Shared Sub ExchangeValuesSecondThread(connStrig As [String], level As IsolationLevel)
Console.WriteLine("Begin the ExchangeValuesSecondThread.....")
Using conn As New SqlConnection(connStrig)
Dim cmdText As [String] = "Use DbDataIsolationLevel;" & vbCr & vbLf & vbCr & vbLf &
"WaitFor Delay '00:00:03'; " & vbCr & vbLf & vbCr & vbLf &
"Declare @price money;" & vbCr & vbLf &
"select @price=Price from dbo.Products where ProductId=1;" & vbCr & vbLf & vbCr & vbLf &
"Update dbo.Products set Price=@price where ProductId=2;"
conn.Open()
Using tran As SqlTransaction = conn.BeginTransaction(level, "ExchangeValuesSecond")
Using command As New SqlCommand(cmdText, conn)
command.Transaction = tran
command.ExecuteNonQuery()
End Using
tran.Commit()
End Using
End Using
Console.WriteLine("Exit from the ExchangeValuesSecondThread.....")
End Sub
End Class
' Demonstrates the difference between the Serializable and Snapshot transaction
Public Shared Sub DemonstrateBetweenSnapshotAndSerializable(connString As [String])
OperateDatabase.CreateTable(connString)
Console.WriteLine("Exchange Vaules in the Snapshot transaction:")
DemonstrateIsolationLevel(connString, IsolationLevel.Snapshot, AddressOf ExchangeValuesThreads.ExchangeValuesFirstThread, AddressOf ExchangeValuesThreads.ExchangeValuesSecondThread)
DisplayData(connString)
Console.WriteLine()
Console.WriteLine("Cannot Exchange Vaules in the Serializable transaction:")
OperateDatabase.CreateTable(connString)
DemonstrateIsolationLevel(connString, IsolationLevel.Serializable, AddressOf ExchangeValuesThreads.ExchangeValuesFirstThread, AddressOf ExchangeValuesThreads.ExchangeValuesSecondThread)
DisplayData(connString)
End Sub
Public Shared Sub DisplayData(connString As [String])
Using conn As New SqlConnection(connString)
Dim cmdText As [String] = "Use DbDataIsolationLevel; " & vbCr & vbLf & vbCr & vbLf &
"Select ProductId,ProductName,Quantity,Price" & vbCr & vbLf &
"from dbo.Products"
conn.Open()
Using command As New SqlCommand(cmdText, conn)
Using reader As SqlDataReader = command.ExecuteReader()
DisplayData(reader)
End Using
End Using
End Using
End Sub
Public Shared Sub DisplayData(reader As SqlDataReader)
Dim isFirst As [Boolean] = True
While reader.Read()
If isFirst Then
isFirst = False
For i As Integer = 0 To reader.FieldCount - 1
Console.Write("{0,-12} ", reader.GetName(i))
Next
Console.WriteLine()
End If
For i As Integer = 0 To reader.FieldCount - 1
Console.Write("{0,-12} ", reader(i))
Next
Console.WriteLine()
End While
End Sub
End Class
' This class includes database operations. If there's no database 'DbDataIsolationLevel', create the database.
NotInheritable Class OperateDatabase
Private Sub New()
End Sub
Public Shared Function CreateDatabase(connString As [String]) As [Boolean]
Using conn As New SqlConnection(connString)
Dim cmdText As [String] = "Use Master;" & vbCr & vbLf & vbCr & vbLf &
"if Db_Id('DbDataIsolationLevel') is null" & vbCr & vbLf &
"create Database [DbDataIsolationLevel];"
Using command As New SqlCommand(cmdText, conn)
conn.Open()
command.ExecuteNonQuery()
End Using
Console.WriteLine("Create the Database 'DbDataIsolationLevel'")
End Using
Return True
End Function
' If there's no table [dbo].[Products] in DbDataIsolationLevel, create the table; or recreate it.
Public Shared Function CreateTable(connString As [String]) As [Boolean]
Using conn As New SqlConnection(connString)
Dim cmdText As [String] = "Use DbDataIsolationLevel" & vbCr & vbLf & vbCr & vbLf &
"if Object_ID('[dbo].[Products]') is not null" & vbCr & vbLf &
"drop table [dbo].[Products]" & vbCr & vbLf & vbCr & vbLf &
"Create Table [dbo].[Products]" & vbCr & vbLf &
"(" & vbCr & vbLf &
"[ProductId] int IDENTITY(1,1) primary key," & vbCr & vbLf &
"[ProductName] NVarchar(100) not null," & vbCr & vbLf &
"[Quantity] int null," & vbCr & vbLf &
"[Price] money null" & vbCr & vbLf & " )"
Using command As New SqlCommand(cmdText, conn)
conn.Open()
command.ExecuteNonQuery()
End Using
End Using
Return InsertRows(connString)
End Function
' Insert some rows into [dbo].[Products] table.
Public Shared Function InsertRows(connString As [String]) As [Boolean]
Using conn As New SqlConnection(connString)
Dim cmdText As [String] = "Use DbDataIsolationLevel" & vbCr & vbLf & vbCr & vbLf &
"INSERT [dbo].[Products] ([ProductName], [Quantity], [Price]) VALUES (N'Blue Bike', 365,1075.00)" & vbCr & vbLf &
"INSERT [dbo].[Products] ([ProductName], [Quantity], [Price]) VALUES (N'Red Bike', 159, 1299.00)" & vbCr & vbLf &
"INSERT [dbo].[Products] ([ProductName], [Quantity], [Price]) VALUES (N'Black Bike', 638, 1159.00)"
Using command As New SqlCommand(cmdText, conn)
conn.Open()
command.ExecuteNonQuery()
End Using
End Using
Return True
End Function
' Turn on or off 'ALLOW_SNAPSHOT_ISOLATION'
Public Shared Function SetSnapshot(connString As [String], isOpen As [Boolean]) As [Boolean]
Using conn As New SqlConnection(connString)
Dim cmdText As [String] = Nothing
If isOpen Then
cmdText = "ALTER DATABASE DbDataIsolationLevel SET ALLOW_SNAPSHOT_ISOLATION ON"
Else
cmdText = "ALTER DATABASE DbDataIsolationLevel SET ALLOW_SNAPSHOT_ISOLATION OFF"
End If
Using command As New SqlCommand(cmdText, conn)
conn.Open()
command.ExecuteNonQuery()
End Using
End Using
Return True
End Function
End Class
Class Program
Public Shared Sub Main(args As String())
Dim connString As [String] = "Data Source=(local);Initial Catalog=master;Integrated Security=True;Asynchronous Processing=true;"
OperateDatabase.CreateDatabase(connString)
Console.WriteLine()
Console.WriteLine("Demonstrate the ReadUncommitted transaction: ")
TransactionIsolationLevels.DemonstrateIsolationLevel(connString, System.Data.IsolationLevel.ReadUncommitted)
Console.WriteLine("-----------------------------------------------")
Console.WriteLine("Demonstrate the ReadCommitted transaction: ")
TransactionIsolationLevels.DemonstrateIsolationLevel(connString, System.Data.IsolationLevel.ReadCommitted)
Console.WriteLine("-----------------------------------------------")
Console.WriteLine("Demonstrate the RepeatableRead transaction: ")
TransactionIsolationLevels.DemonstrateIsolationLevel(connString, System.Data.IsolationLevel.RepeatableRead)
Console.WriteLine("-----------------------------------------------")
Console.WriteLine("Demonstrate the Serializable transaction: ")
TransactionIsolationLevels.DemonstrateIsolationLevel(connString, System.Data.IsolationLevel.Serializable)
Console.WriteLine("-----------------------------------------------")
Console.WriteLine("Demonstrate the Snapshot transaction: ")
OperateDatabase.SetSnapshot(connString, True)
TransactionIsolationLevels.DemonstrateIsolationLevel(connString, System.Data.IsolationLevel.Snapshot)
Console.WriteLine("-----------------------------------------------")
Console.WriteLine("Demonstrate the difference between the Snapshot and Serializable transactions:")
TransactionIsolationLevels.DemonstrateBetweenSnapshotAndSerializable(connString)
OperateDatabase.SetSnapshot(connString, False)
Console.WriteLine()
End Sub
End Class
End Namespace
Commenti
I IsolationLevel valori vengono usati da un provider di dati .NET durante l'esecuzione di una transazione.
Rimane IsolationLevel attivo fino a quando non viene modificato in modo esplicito, ma può essere modificato in qualsiasi momento. Il nuovo valore viene usato in fase di esecuzione, non analizza il tempo. Se modificato durante una transazione, il comportamento previsto del server consiste nell'applicare il nuovo livello di blocco a tutte le istruzioni rimanenti.
Quando si usa OdbcTransaction, se non si imposta OdbcTransaction.IsolationLevel o lo Unspecifiedsi imposta su , la transazione viene eseguita in base al livello di isolamento determinato dal driver in uso.
Si applica a
Collabora con noi su GitHub
L'origine di questo contenuto è disponibile in GitHub, in cui è anche possibile creare ed esaminare i problemi e le richieste pull. Per ulteriori informazioni, vedere la guida per i collaboratori.
