|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Isolation Level ....Is this a bug ?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Isolation Level ....Is this a bug ?
|
|
| Ramesh 2002-11-01, 10:23 pm |
|
"Ramesh" <raman_ramesh@k2c.biz> wrote in message news:...
> Isolation Level : Read committed (SQL Server default level).
>
>
> Connection A, issues the following sql batch
>
> begin tran
> update table1 set i= 'val4' where i='val4'
>
>
> Connection B
> Select * from table1
> (Connection B can view all the data (dirty read), even though connection A
> has not issued an Commit or RollBack tran )
>
> This situation is seen, when a column is updated with the original value.
> But if the column is updated with some other value other than the original
> value, Connection
> B has to wait till Connection A issues an Commit / RollBack tran
statement.
>
> Is this a bug ? Kindly advice.
>
>
| |
| Hal Berenson 2002-11-02, 12:23 am |
| Yes.
Only happens if the table is stored as a heap, not if there is a clustered
index.
Only happens if the Select has no predicate (WHERE clause).
Also happens for Isolation Level Repeatable Read (on the updater).
Doesn't happen if the updater uses Serializable (because that takes some
additional locks).
--
Hal Berenson
True Mountain Consulting
"Ramesh" <raman_ramesh@k2c.biz> wrote in message
news:OjnDuEigCHA.1652@tkmsftngp09...
>
> "Ramesh" <raman_ramesh@k2c.biz> wrote in message news:...
> > Isolation Level : Read committed (SQL Server default level).
> >
> >
> > Connection A, issues the following sql batch
> >
> > begin tran
> > update table1 set i= 'val4' where i='val4'
> >
> >
> > Connection B
> > Select * from table1
> > (Connection B can view all the data (dirty read), even though connection
A
> > has not issued an Commit or RollBack tran )
> >
> > This situation is seen, when a column is updated with the original
value.
> > But if the column is updated with some other value other than the
original
> > value, Connection
> > B has to wait till Connection A issues an Commit / RollBack tran
> statement.
> >
> > Is this a bug ? Kindly advice.
> >
> >
>
>
| |
| David Campbell 2002-11-02, 7:23 pm |
| Ramesh,
You're right. The key is that you're updating the value to its previous
value. There are two optimizations in SQL 2K that cause this behavior.
1. Updates are "diff'ed" to produce a minimal log record describing the
change. In this case, there is no difference and so no actual change happens
on the page.
2. There is a lock avoidance technique used in SQL 2K that looks at the page
to determine if everything on the page has been committed, (in which case no
locks are required for reading). Since there was no actual change to the
page, the page state hasn't been changed and the system knows it doesn't
need to take locks.
I don't consider this a bug as there are no pending, externally visible
changes. However, it does cause a surprise for people doing null updates to
try and lock a particular row.
-dave.
| |
| Dejan Sarka 2002-11-03, 9:23 am |
| Hal,
Sorry, but my testnings show different results, do please check my sceipt
bellow:
> Only happens if the table is stored as a heap, not if there is a clustered
> index.
Not true, look at the script at the bottom, I am doing the check on the
pubs.dbo.authors table, it has clustered index.
> Only happens if the Select has no predicate (WHERE clause).
Not true, again check my script.
> Also happens for Isolation Level Repeatable Read (on the updater).
True.
> Doesn't happen if the updater uses Serializable (because that takes some
> additional locks).
Not true, it happens on the Serializable level as well. According to my
tests, it happens always. Here is the script I used:
/***** Locking *****/
SELECT @@version
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
USE pubs
GO
/* Select with XLOCK
Gets the exclusive rowlock, but 2nd session does not honor it
Not possible in 7.0 */
BEGIN TRAN
SELECT * FROM dbo.authors WITH (XLOCK)
WHERE au_ID = '172-32-1176'
EXEC sp_lock @@spid
ROLLBACK TRAN
/* Select with UPDLOCK
Gets the update rowlock, 2nd session can read it
(normal, expected behavior, same in 7.0) */
BEGIN TRAN
SELECT * FROM dbo.authors WITH (UPDLOCK)
WHERE au_ID = '172-32-1176'
EXEC sp_lock @@spid
ROLLBACK TRAN
/* Dummy update
Gets the exclusive rowlock, but 2nd session does not honor it
In SQL 7.0 2nd session honors the lock */
BEGIN TRAN
UPDATE dbo.authors
SET au_lname=au_lname
WHERE au_ID = '172-32-1176'
EXEC sp_lock @@spid
ROLLBACK TRAN
/* Real update
Gets the exclusive rowlock, 2nd session honors it
In SQL 7.0 same behavior */
BEGIN TRAN
UPDATE dbo.authors
SET au_lname='Pink'
WHERE au_ID = '172-32-1176'
EXEC sp_lock @@spid
ROLLBACK TRAN
/* Select with XLOCK and PAGLOCK
Gets the exclusive paglock, 2nd session honors it
Not possible in 7.0 */
BEGIN TRAN
SELECT * FROM dbo.authors WITH (PAGLOCK,XLOCK)
WHERE au_ID = '172-32-1176'
EXEC sp_lock @@spid
ROLLBACK TRAN
/* 2nd session - run for each step in 1st session */
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
USE pubs
GO
BEGIN TRAN
SELECT * FROM dbo.authors
WHERE au_ID = '172-32-1176'
EXEC sp_lock @@spid
ROLLBACK TRAN
/***** Locking *****/
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
| |
| Hal Berenson 2002-11-03, 11:23 am |
| Didn't happen that way on my tests, so it's pretty data dependent (ie, the
plans the optimizer chooses impact the locking behavior).
--
Hal Berenson
True Mountain Consulting
"Dejan Sarka" < dejan_please_reply_to_newsgrou
ps.sarka@reproms.si> wrote in
message news:OlVqnf0gCHA.2232@tkmsftngp09...
> Hal,
>
> Sorry, but my testnings show different results, do please check my sceipt
> bellow:
>
> > Only happens if the table is stored as a heap, not if there is a
clustered
> > index.
>
> Not true, look at the script at the bottom, I am doing the check on the
> pubs.dbo.authors table, it has clustered index.
>
> > Only happens if the Select has no predicate (WHERE clause).
>
> Not true, again check my script.
>
> > Also happens for Isolation Level Repeatable Read (on the updater).
>
> True.
>
> > Doesn't happen if the updater uses Serializable (because that takes some
> > additional locks).
>
> Not true, it happens on the Serializable level as well. According to my
> tests, it happens always. Here is the script I used:
>
> /***** Locking *****/
>
> SELECT @@version
> GO
>
> SET TRANSACTION ISOLATION LEVEL READ COMMITTED
> -- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
> -- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> GO
>
> USE pubs
> GO
>
> /* Select with XLOCK
> Gets the exclusive rowlock, but 2nd session does not honor it
> Not possible in 7.0 */
> BEGIN TRAN
> SELECT * FROM dbo.authors WITH (XLOCK)
> WHERE au_ID = '172-32-1176'
> EXEC sp_lock @@spid
> ROLLBACK TRAN
>
> /* Select with UPDLOCK
> Gets the update rowlock, 2nd session can read it
> (normal, expected behavior, same in 7.0) */
> BEGIN TRAN
> SELECT * FROM dbo.authors WITH (UPDLOCK)
> WHERE au_ID = '172-32-1176'
> EXEC sp_lock @@spid
> ROLLBACK TRAN
>
> /* Dummy update
> Gets the exclusive rowlock, but 2nd session does not honor it
> In SQL 7.0 2nd session honors the lock */
> BEGIN TRAN
> UPDATE dbo.authors
> SET au_lname=au_lname
> WHERE au_ID = '172-32-1176'
> EXEC sp_lock @@spid
> ROLLBACK TRAN
>
> /* Real update
> Gets the exclusive rowlock, 2nd session honors it
> In SQL 7.0 same behavior */
> BEGIN TRAN
> UPDATE dbo.authors
> SET au_lname='Pink'
> WHERE au_ID = '172-32-1176'
> EXEC sp_lock @@spid
> ROLLBACK TRAN
>
> /* Select with XLOCK and PAGLOCK
> Gets the exclusive paglock, 2nd session honors it
> Not possible in 7.0 */
> BEGIN TRAN
> SELECT * FROM dbo.authors WITH (PAGLOCK,XLOCK)
> WHERE au_ID = '172-32-1176'
> EXEC sp_lock @@spid
> ROLLBACK TRAN
>
> /* 2nd session - run for each step in 1st session */
> SET TRANSACTION ISOLATION LEVEL READ COMMITTED
> GO
>
> USE pubs
> GO
>
> BEGIN TRAN
> SELECT * FROM dbo.authors
> WHERE au_ID = '172-32-1176'
> EXEC sp_lock @@spid
> ROLLBACK TRAN
> /***** Locking *****/
>
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
>
| |
|
|
"Hal Berenson" < haroldb@truemountainconsulting
.com> wrote in message
news:#7SJyi1gCHA.2512@tkmsftngp10...
> Didn't happen that way on my tests, so it's pretty data dependent (ie, the
> plans the optimizer chooses impact the locking behavior).
She loves me, she loves me not
| |
| Hal Berenson 2002-11-03, 12:23 pm |
| I finally got enough time to really think about this.
It isn't a bug in terms of violating Isolation. Isolation is defined in
terms of behaviors, not locking. Locking protocols happen to be the most
precise way to express isolation, with the simplest protocol being the most
precise. But optimizations to the protocol are certainly valid as long as
the correct behavior is preserved. Note that from looking at sp_lock data
SQL Server is taking out the appropriate locks to make isolation work, it's
the optimization of realizing nothing on the page has changed and not even
checking the locks that is coming into play here. That's a valid
optimization.
My quick tests showed some, what seem like, random behavior. But they are
perfectly understandable. The way locking works in SQL Server is that the
optimizer passes in a locking request that expresses what it needs in order
for a query (or update) to behave properly. The lock manager then decides
what actual locks to take. So different queries will result in different
requests being made to the lock manager, and then the lock manager will
choose various actual locking strategies. I used small amounts of data,
which resulted in cases where a page or table lock was taken. Larger
amounts of data would have stuck with row locking and yielded different
results.
This behavior bugs me, but isn't necessarily a bug in SQL Server. I don't
like the fact that I can have a stored procedure that is called thousands of
times, but if @p1 and @p2 happen to hold the same value (or a calculation
yields the same value) then I get very different behaviors on the locking
front. This is different than Dave's case of someone intentionally trying
to take a row lock by modifying a value to itself in that the application
isn't trying to spoof the server. It's just seeing inconsistent behavior.
But I still put this in the nit category. Apps won't break, unless they are
trying to incorporate direct knowledge of the underlying rdbms internals.
And that's a general no no in application design.
--
Hal Berenson
True Mountain Consulting
"Hal Berenson" < haroldb@truemountainconsulting
.com> wrote in message
news:#Ie97GjgCHA.2700@tkmsftngp11...
> Yes.
>
> Only happens if the table is stored as a heap, not if there is a clustered
> index.
> Only happens if the Select has no predicate (WHERE clause).
> Also happens for Isolation Level Repeatable Read (on the updater).
> Doesn't happen if the updater uses Serializable (because that takes some
> additional locks).
>
> --
> Hal Berenson
> True Mountain Consulting
>
>
> "Ramesh" <raman_ramesh@k2c.biz> wrote in message
> news:OjnDuEigCHA.1652@tkmsftngp09...
> >
> > "Ramesh" <raman_ramesh@k2c.biz> wrote in message news:...
> > > Isolation Level : Read committed (SQL Server default level).
> > >
> > >
> > > Connection A, issues the following sql batch
> > >
> > > begin tran
> > > update table1 set i= 'val4' where i='val4'
> > >
> > >
> > > Connection B
> > > Select * from table1
> > > (Connection B can view all the data (dirty read), even though
connection
> A
> > > has not issued an Commit or RollBack tran )
> > >
> > > This situation is seen, when a column is updated with the original
> value.
> > > But if the column is updated with some other value other than the
> original
> > > value, Connection
> > > B has to wait till Connection A issues an Commit / RollBack tran
> > statement.
> > >
> > > Is this a bug ? Kindly advice.
> > >
> > >
> >
> >
>
>
| |
| JXStern 2002-11-04, 12:23 pm |
| On Sat, 2 Nov 2002 09:16:10 +0530, "Ramesh" <raman_ramesh@k2c.biz>
wrote:
>> begin tran
>> update table1 set i= 'val4' where i='val4'
Try doing a select with (updlock) instead, works in SQL7.
J.
|
|
|
|
|