IsolationLevel Enum
Definisi
Penting
Beberapa informasi terkait produk prarilis yang dapat diubah secara signifikan sebelum dirilis. Microsoft tidak memberikan jaminan, tersirat maupun tersurat, sehubungan dengan informasi yang diberikan di sini.
Menentukan perilaku penguncian transaksi untuk koneksi.
Enumerasi ini mendukung kombinasi bitwise dari nilai yang termasuk di dalamnya.
public enum class IsolationLevelpublic enum IsolationLevel[System.Flags]
public enum IsolationLeveltype IsolationLevel = [<System.Flags>]
type IsolationLevel = Public Enum IsolationLevel- Warisan
- Atribut
Bidang
| Chaos | 16 | Perubahan yang tertunda dari transaksi yang lebih sangat terisolasi tidak dapat ditimpa. |
| ReadCommitted | 4096 | Kunci bersama ditahan saat data sedang dibaca untuk menghindari pembacaan kotor, tetapi data dapat diubah sebelum akhir transaksi, menghasilkan bacaan atau data phantom yang tidak dapat diulang. |
| ReadUncommitted | 256 | Bacaan kotor dimungkinkan, yang berarti bahwa tidak ada kunci bersama yang dikeluarkan dan tidak ada kunci eksklusif yang dihormati. |
| RepeatableRead | 65536 | Kunci ditempatkan pada semua data yang digunakan dalam kueri, mencegah pengguna lain memperbarui data. Mencegah pembacaan yang tidak dapat diulang tetapi baris phantom masih dimungkinkan. |
| Serializable | 1048576 | Kunci rentang ditempatkan pada DataSet, mencegah pengguna lain memperbarui atau menyisipkan baris ke dalam himpunan data hingga transaksi selesai. |
| Snapshot | 16777216 | Mengurangi pemblokiran dengan menyimpan versi data yang dapat dibaca oleh satu aplikasi saat aplikasi lain memodifikasi data yang sama. Menunjukkan bahwa dari satu transaksi Anda tidak dapat melihat perubahan yang dilakukan dalam transaksi lain, bahkan jika Anda mengkueri ulang. |
| Unspecified | -1 | Tingkat isolasi yang berbeda dari yang ditentukan sedang digunakan, tetapi tingkat tidak dapat ditentukan. |
Contoh
Aplikasi ini menunjukkan cara menggunakan IsolationLevel di DbTransaction. Sampel akan menunjukkan perilaku mana yang diizinkan dalam tingkat isolasi yang berbeda:
Bacaan kotor.
Bacaan yang tidak dapat diulang.
Hantu.
Aplikasi ini akan dijalankan dalam tingkat isolasi berikut:
ReadUncommitted
ReadCommitted
RepeatableRead
Serializable
Snapshot
Kelas PhantomReadThreads menunjukkan apakah transaksi tertentu memungkinkan perilaku Phantom Read. Jika transaksi memungkinkan perilaku, utas akan beroperasi dalam urutan berikut:
Di utas pertama, pilih produk(Semua).
Di utas kedua, masukkan produk baru.
Terapkan transaksi di utas kedua.
Pilih produk lagi.
Terapkan transaksi di utas pertama.
Jika transaksi memungkinkan perilaku, dua operasi Pilih akan mendapatkan hasil yang berbeda.
Kelas NonrepeatableReadThreads menunjukkan apakah transaksi tertentu memungkinkan perilaku Baca yang Tidak Dapat Dipecat. Jika transaksi memungkinkan perilaku, utas akan beroperasi dalam urutan berikut:
Di utas pertama, pilih produk(ProductId=1).
Di utas kedua, perbarui nilai Kuantitas(ProductId=1).
Terapkan transaksi di utas kedua.
Pilih produk lagi.
Terapkan transaksi di utas pertama.
Jika transaksi memungkinkan perilaku, dua operasi Pilih akan mendapatkan hasil yang berbeda.
Kelas ExchangeValuesThreads menunjukkan perbedaan antara transaksi Serializable dan Snapshot. Untuk transaksi Serializable, utas akan beroperasi dalam urutan berikut:
Di utas pertama, dapatkan Harga produk (ProductId=2) dan simpan dalam variabel .
Di utas pertama, perbarui Harga produk (ProductId=1) dengan harga produk(ProductId=2).
Terapkan transaksi di utas pertama.
Di utas kedua, dapatkan Harga produk (ProductId=1) dan simpan dalam variabel .
Di utas kedua, perbarui Harga produk(ProductId=2) dengan harga produk(ProductId=1).
Terapkan transaksi di utas kedua.
Sekarang nilai Price(ProductId=1 dan ProductId=2) sama dengan Harga Asli Produk(ProductId=2).
Untuk transaksi Rekam Jepret, utas akan beroperasi dalam urutan berikut:
Di utas pertama, dapatkan Harga produk (ProductId=2) dan simpan dalam variabel;
Di utas pertama, perbarui Harga produk (ProductId=1) dengan harga produk(ProductId=2).
Di utas kedua, dapatkan Harga produk(ProductId=1) dari rekam jepret dan simpan dalam variabel.
Di utas kedua, perbarui Harga produk(ProductId=2) dengan harga produk(ProductId=1).
Terapkan transaksi di utas kedua.
Terapkan transaksi di utas pertama.
Sekarang tukarkan Harga produk(ProductId=1 dan ProductId=2).
Kelas DirtyReadThreads menunjukkan apakah transaksi tertentu memungkinkan perilaku Dirty Read. Jika transaksi memungkinkan perilaku, utas akan beroperasi dalam urutan berikut:
Di utas pertama, mulai transaksi dan tambahkan nilai Kuantitas(ProductId=1).
Di utas kedua, baca nilai Kuantitas dan tambahkan nilai lagi.
Terapkan transaksi di utas kedua.
Gulung balik transaksi di utas pertama.
Jika transaksi memungkinkan perilaku, nilai Kuantitas akan ditambahkan dua kali.
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
Keterangan
Nilai IsolationLevel digunakan oleh penyedia data .NET saat melakukan transaksi.
IsolationLevel Tetap berlaku sampai diubah secara eksplisit, tetapi dapat diubah kapan saja. Nilai baru digunakan pada waktu eksekusi, bukan waktu penguraian. Jika diubah selama transaksi, perilaku server yang diharapkan adalah menerapkan tingkat penguncian baru ke semua pernyataan yang tersisa.
Saat menggunakan OdbcTransaction, jika Anda tidak mengatur OdbcTransaction.IsolationLevel atau Anda mengaturnya ke Unspecified, transaksi dijalankan sesuai dengan tingkat isolasi yang ditentukan oleh driver yang digunakan.
Berlaku untuk
Berkolaborasi dengan kami di GitHub
Sumber untuk konten ini dapat ditemukan di GitHub, yang juga dapat Anda gunakan untuk membuat dan meninjau masalah dan menarik permintaan. Untuk informasi selengkapnya, lihat panduan kontributor kami.
