What do you think about this?

It seems to me that isolation levels only affect reads (SELECT statements)
in the current transaction because isolation levels only affect how S locks
are used.

If the transaction contains only INSERT, UPDATE or DELETE, isolation level
doesn't matter since they all use X locks.

So in the transaction below, even READ UNCOMMITTED would work the same as
SERIALIZEABLE since there are no SELECT statements. I've done quite a bit of
testing to try to break it and I can't.

BEGIN TRANSACTION
UPDATE savings SET Balance = Balance - 1000 WHERE SSAN = 555-55-5555;
UPDATE checking SET Balance = Balance + 1000 WHERE SSAN = 555-55-5555;
COMMIT -- or ROLLBACK

What's wrong with my thinking?

I must confess that when it comes to critical data like money I tend to use
SERIALIZEABLE anyway, perhaps to avoid lawsuits. ;-) Also in case we later
add some SELECT statements that might need the protection. We should keep
the transaction as short as possible and COMMIT asap anyway, so it doesn't
hurt.

thanks,
Dick

You asked what I think happens in
UPDATE savings SET Balance = Balance - 1000 WHERE SSAN = 555-55-5555

I tested it and what happens is that an X lock is requested and must be
granted before the statement begins execution. So even with READ UNCOMMITTED
(which in fact only affects SELECT statements) this UPDATE will wait until
exclusive access is obtained before reading and then modifying the value. I
tried to get it to read dirty data and it won't because X is required before
starting.

Thanks for the response, but this is the behavior I found.

Dick


[quoted text, click to view]

You have to read again about transactions.
SERIALIZABLE is need only in whery rare cases.

Also what you think happen when you set "Balance = Balance + 1000"?
You _read_ old value and set new one.

Bojidar Alexandrov
[quoted text, click to view]

Hi Dick

You've posted this question several times in several different places.
Please try to limit yourself to asking a question once and then waiting for
a response for a reasonable amount of time.

What do you mean by 'break'? Setting an isolation level tells SQL Server
what behaviors you want to allow, you but won't get more errors in one level
than another.

Serializable is used to prevent data that you have accessed from being
changed by another process. You're right, that the X locks acquired by the
updates will prevent this anyway.

But, serializable ALSO prevents new rows from being inserted that would
satisfy the where clause of a query in the transaction. It does this by
acquiring KEY RANGE locks to protect a range of data in an index.

If you have a unique index on SSAN so there is only one row possible for
your queries, you probably wouldn't get any difference between SERIALIZABLE
and READ COMMITTED. But here is an example that shows the difference.
----------------------------------------------------------------------------
---------------


-- In the Northwind database, create a copy of the orders table with a
NONUNIQUE index on CustomerID.


use northwind
go
drop table orders2
go
select * into orders2 from orders
go
CREATE INDEX cust_index on orders2(CustomerID)
go

-- Now set the isolation level to the default and run these three statements
set transaction isolation level read committed
begin tran
update orders2 set ShippedDate = ShippedDate + 1
where CustomerID= 'ALFKI'
exec sp_lock
-- pause here

-- sp_lock will show you 14 locks, including RID X locks on the rows and IX
locks on the pages.

-- Now in another connection try to insert a row for customer 'ALFKI'

-- set identity_insert orders2 on
--
-- INSERT INTO [Northwind].[dbo].[orders2]([OrderID], [CustomerID],
[EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia],
[Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion],
[ShipPostalCode], [ShipCountry])
-- VALUES(11078, 'ALFKI', 7, '19960710', '19960720', '19960712', 2, 29.99,
'Hanari Carnes', 'Rua do Paço, 67', 'Rio de Janeiro', 'RJ', '05454-876',
'Brazil')

-- This insert will succeed, so if you try to update ORders2 again, you will
affect more rows than you did originally.

--Now rollback the transaction

rollback tran



-- Now change to SERIALIZABLE and run the exact same transaction

set transaction isolation level read committed
begin tran
update orders2 set ShippedDate = ShippedDate + 1
where CustomerID= 'ALFKI'
exec sp_lock
-- pause here

-- You should see a lot more locks, because SQL Server is acquired RANGE
locks of various flavors on the index on ALFKI.

-- Now in another connection try to insert another row for customer 'ALFKI'

-- set identity_insert orders2 on
--
-- INSERT INTO [Northwind].[dbo].[orders2]([OrderID], [CustomerID],
[EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia],
[Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion],
[ShipPostalCode], [ShipCountry])
-- VALUES(11079, 'ALFKI', 7, '19960710', '19960720', '19960712', 2, 29.99,
'Hanari Carnes', 'Rua do Paço, 67', 'Rio de Janeiro', 'RJ', '05454-876',
'Brazil')

-- This insert will block because of the RANGE locks on the Orders2 table.
The insert will not succeed until the current transaction in the original
session is either committed or rolled back. ,

rollback tran

Do the answer to 'does SERIALIZABLE make a difference' is IT DEPENDS. What
kinds of indexes do you have. RANGE locks are only acquired if there is an
index to support the WHERE condition and if there is a chance of a range of
rows. And, to see the difference between the levels, you have to be
accessing the same data or same range of data multiple times, and another
session needs to want to insert a new row into the range you are interested
in.

Again, it's not a case of one level giving errors, it's a case of the
stricter isolation levels preventing certain behaviors. Some of these
behaviors, l ike inserting a row into the range you're looking at, might be
ok, and some might not be. Please read all about isolation levels in Books
Online. My ebook, Troubleshooting Locking and Blocking by www.netimpress.com
gives a lot more info on isolation levels and range locks, and lots of
sample code for testing things out.

Good Luck
--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com


[quoted text, click to view]

Kalen,

Yes, this answers my questions, thank you very much. You did a lot of work
to put your complete examples together for me; I appreciate that very, very
much.

Your example showed that isolation can affect the amount of locking not only
for SELECT but also UPDATE (or DELETE I suppose since they all may have a
WHERE clause).

In my example I had two UPDATEs with WHERE primary-key = value so only one
row was accessed and only one X key lock was taken for each update. The same
locking appeared for READ COMMITTED as for SERIALIZEABLE and both should
give the correct result. I conclude that it's worth using SERIALIZEABLE in
any case, if it takes no more locks then there is no disadvantage. Maybe
that's why the Microsoft exam said the correct answer was SERIALIZEABLE.

It makes sense that an UPDATE with a multi-row WHERE predicate can cause
ranges locks just as SELECT does. Thanks for teaching me that.

Best Wishes,
Dick



----- Original Message -----
From: "Kalen Delaney" <replies@public_newsgroups.com>
Newsgroups: microsoft.public.sqlserver.programming
Sent: Saturday, June 26, 2004 10:16 AM
Subject: Re: ISOLATION levels and when to use SERIALIZEABLE


Hi Dick

What do you mean by 'break'? Setting an isolation level tells SQL Server
what behaviors you want to allow, you but won't get more errors in one level
than another.

Serializable is used to prevent data that you have accessed from being
changed by another process. You're right, that the X locks acquired by the
updates will prevent this anyway.

But, serializable ALSO prevents new rows from being inserted that would
satisfy the where clause of a query in the transaction. It does this by
acquiring KEY RANGE locks to protect a range of data in an index.

If you have a unique index on SSAN so there is only one row possible for
your queries, you probably wouldn't get any difference between SERIALIZABLE
and READ COMMITTED. But here is an example that shows the difference.
----------------------------------------------------------------------------
---------------


-- In the Northwind database, create a copy of the orders table with a
NONUNIQUE index on CustomerID.


use northwind
go
drop table orders2
go
select * into orders2 from orders
go
CREATE INDEX cust_index on orders2(CustomerID)
go

-- Now set the isolation level to the default and run these three statements
set transaction isolation level read committed
begin tran
update orders2 set ShippedDate = ShippedDate + 1
where CustomerID= 'ALFKI'
exec sp_lock
-- pause here

-- sp_lock will show you 14 locks, including RID X locks on the rows and IX
locks on the pages.

-- Now in another connection try to insert a row for customer 'ALFKI'

-- set identity_insert orders2 on
--
-- INSERT INTO [Northwind].[dbo].[orders2]([OrderID], [CustomerID],
[EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia],
[Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion],
[ShipPostalCode], [ShipCountry])
-- VALUES(11078, 'ALFKI', 7, '19960710', '19960720', '19960712', 2, 29.99,
'Hanari Carnes', 'Rua do Paço, 67', 'Rio de Janeiro', 'RJ', '05454-876',
'Brazil')

-- This insert will succeed, so if you try to update ORders2 again, you will
affect more rows than you did originally.

--Now rollback the transaction

rollback tran



-- Now change to SERIALIZABLE and run the exact same transaction

set transaction isolation level SERIALIZEABLE
begin tran
update orders2 set ShippedDate = ShippedDate + 1
where CustomerID= 'ALFKI'
exec sp_lock
-- pause here

-- You should see a lot more locks, because SQL Server is acquired RANGE
locks of various flavors on the index on ALFKI.

-- Now in another connection try to insert another row for customer 'ALFKI'

-- set identity_insert orders2 on
--
-- INSERT INTO [Northwind].[dbo].[orders2]([OrderID], [CustomerID],
[EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia],
[Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion],
[ShipPostalCode], [ShipCountry])
-- VALUES(11079, 'ALFKI', 7, '19960710', '19960720', '19960712', 2, 29.99,
'Hanari Carnes', 'Rua do Paço, 67', 'Rio de Janeiro', 'RJ', '05454-876',
'Brazil')

-- This insert will block because of the RANGE locks on the Orders2 table.
The insert will not succeed until the current transaction in the original
session is either committed or rolled back. ,

rollback tran

Do the answer to 'does SERIALIZABLE make a difference' is IT DEPENDS. What
kinds of indexes do you have. RANGE locks are only acquired if there is an
index to support the WHERE condition and if there is a chance of a range of
rows. And, to see the difference between the levels, you have to be
accessing the same data or same range of data multiple times, and another
session needs to want to insert a new row into the range you are interested
in.

Again, it's not a case of one level giving errors, it's a case of the
stricter isolation levels preventing certain behaviors. Some of these
behaviors, like inserting a row into the range you're looking at, might be
ok, and some might not be. Please read all about isolation levels in Books
Online. My ebook, Troubleshooting Locking and Blocking by www.netimpress.com
gives a lot more info on isolation levels and range locks, and lots of
sample code for testing things out.

Good Luck
--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com


[quoted text, click to view]