в 13:46, 26 мая 2010

2010-05-26T13:46:16Z

Admin: 1 версия

2010-05-26T10:24:41Z

1 версия

Новая страница

== SET TRANSACTION ISOLATION LEVEL SERIALIZABLE==



<source lang="sql">
2> CREATE TABLE Product(
3> ProductID int NOT NULL,
4> Name nvarchar(25) NOT NULL,
5> ProductNumber nvarchar(25) ,
6> Color nvarchar(15) NULL,
7> StandardCost money NOT NULL,
8> Size nvarchar(5) NULL,
9> Weight decimal(8, 2) NULL,
10> ProductLine nchar(20) NULL,
11> SellStartDate datetime NOT NULL,
12> SellEndDate datetime NULL
13> )
14> GO
1> insert into Product values(1,"Product A", "1","Red",123.123,"1",1,"ProductLine A","1999-03-22","2000-03-22");
2> GO
(1 rows affected)
1>
2>
3> insert into Product values(2,"Product B", "2","Yellow",234.234,"1",3,"ProductLine B","2000-03-22","2001-03-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(3,"Product C", "3","Pink",345.345,"1",3,"ProductLine V","2001-09-22","2006-02-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(4,"Product D", "4","White",456.456,"1",4,"ProductLine D","2002-08-22","2006-03-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(5,"Product E", "5","Black",567.567,"1",5,"ProductLine E","2003-01-22","2003-04-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(6,"Product F", "6","Blue",678.678,"1",6,"ProductLine W","2004-02-22","2005-05-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(7,"Product G", "7","Drak",789.789,"1",7,"ProductLine Q","2005-03-22","2006-03-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(8,"Product H", "8","Gray",234.123,"1",8,"ProductLine F","2006-04-22","2006-09-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(9,"Product I", "9","Red",543.123,"1",9,"ProductLine R","2007-05-22","2008-03-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(0,"Product J", "0","Gold",765.123,"1",0,"ProductLine J","2008-06-22","2009-03-22");
4> GO
(1 rows affected)
1>
2>
3>
4>
5> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
6> GO
1> BEGIN TRANSACTION
2> GO
1> UPDATE Product SET StandardCost = StandardCost * 1.15
2> WHERE ProductID = 1
3> GO
(1 rows affected)
1> UPDATE Product SET StandardCost = StandardCost * 1.15
2> WHERE ProductID = 1
3> AND StandardCost < 1000
4> GO
(1 rows affected)
1> COMMIT TRANSACTION
2> GO
1>
2>
3> drop table Product;
4> GO</source>

== Syntax for SET TRANSACTION ISOLATION LEVEL==



<source lang="sql">
SET TRANSACTION ISOLATION LEVEL
{ READ COMMITTED
| READ UNCOMMITTED
| REPEATABLE READ
| SERIALIZABLE
}
The READ UNCOMMITTED option allows dirty reads.
It is the same as using the NOLOCK or READUNCOMMITTED table hint.
It does not create locks nor does it honor any locks.
The data you read while it is in effect may be in the process of being changed.
The REPEATABLE READ option additionally disallows phantom rows.
Shared locks remain in place for the duration of the transaction.
The outside process can still insert rows that will be picked up by your second SELECT.
The SERIALIZABLE option is the most restrictive.
It additionally prevents other processes from inserting rows between your first and second SELECTs.
If the INSERT will make no difference to your two SELECTs, it is allowed; otherwise, it is not.
Reference:
Advanced Transact-SQL for SQL Server 2000</source>

== Testing transaction isolation levels.==



<source lang="sql">
6>
7> CREATE TABLE authors(
8> au_id varchar(11),
9> au_lname varchar(40) NOT NULL,
10> au_fname varchar(20) NOT NULL,
11> phone char(12) NOT NULL DEFAULT ("UNKNOWN"),
12> address varchar(40) NULL,
13> city varchar(20) NULL,
14> state char(2) NULL,
15> zip char(5) NULL,
16> contract bit NOT NULL
17> )
18> GO
1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1)
2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1)
3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1)
4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1)
5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1)
6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1)
7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1)
8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0)
9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0)
10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1)
11> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
4> GO
1>
2> BEGIN TRAN
3> SELECT *
4> FROM authors
5> WHERE au_lname = "Green"
6> WAITFOR DELAY "00:02:00"
7> ROLLBACK TRAN
8> GO
drop table authors;
GO</source>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL Tutorial/Transaction/TRANSACTION ISOLATION LEVEL - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия