SQL Server/T-SQL Tutorial/Transaction/TRANSACTION ISOLATION LEVEL

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

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

Syntax for SET TRANSACTION ISOLATION LEVEL

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

Testing transaction isolation levels.

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