row vs page locking...

Hello all,
Somewhere on these newsgroups I recall reading that SQL Server 6 and prior
(when they were married with Sybase) used page locking and not row level
locking. Hence you could be locking a lot more records then what you think
when doing an UPDATE or INSERT SQL.
Now I notice that SQL Server 7 and 2000 claim to use row level locking. (As
you can see, I have been out of the SQL arena for some time). So what I'd
like to know if this is all true? Or marketing mumbo-jumbo? Has Microsoft
made changes at the core of their engine to lock rows? I know that other
RDMSs like Interbase have a versioning engine so it was built from the
ground up for concurrence. And I've read that MSs row level locking is a
band-aid on its unchanged core engine, although the author of that message
did not expand further on this.
so I figured I'd ask the experts what the truth is. Any help is much
appreciated.
If indeed it has changed, what sort of test can I run to prove this to my boss?
regards,
-randall sellI'm not an expert, but my understanding it that , yes, SQL Server 2000 has
true row-level locking. But in heavy use situations this can be expensive,
so they automatically escalate to page level locks. Hence lots of talk about
'Lock Escalation' - see Books Online.
- "Lock escalation is the process of converting many fine-grain locks into
fewer coarse-grain locks, reducing system overhead. Microsoft® SQL ServerT
2000 automatically escalates row locks and page locks into table locks when
a transaction exceeds its escalation threshold."
I use Interbase / Firebird too. SQL Server 2005 is going to have a Snapshot
isolation level that will offer similar functionality to Firebird (though
implemented very differently).
My only reservation about Firebird is the 'magic' it has to use to enforce
unique indexes, foreign keys etc. Ie you could get a situation like this:
Trans 1:
Insert into mytable values (1); -- this is the PK
-- don't commit yet
Trans 2:
Insert into mytable values (1); -- doesn't work, PK violation. So try this:
Select count(*) from mytable where pkfield = 1 -- returns 0. hhhmmmm!
This is because your "trans 2 Insert" can see the "trans 1 insert" before
it's committed. But the "trans 2 select" can't.
Cool, but it makes custom Referential Integrity difficult, and requires
special db design.
Good old fashioned locking is less sexy, but I find, more productive!
John
"Randall Sell" <randall@.bytewise.nospam.com.au> wrote in message
news:UnE_c.20421$D7.10166@.news-server.bigpond.net.au...
> Hello all,
> Somewhere on these newsgroups I recall reading that SQL Server 6 and prior
> (when they were married with Sybase) used page locking and not row level
> locking. Hence you could be locking a lot more records then what you think
> when doing an UPDATE or INSERT SQL.
> Now I notice that SQL Server 7 and 2000 claim to use row level locking.
> (As you can see, I have been out of the SQL arena for some time). So what
> I'd like to know if this is all true? Or marketing mumbo-jumbo? Has
> Microsoft made changes at the core of their engine to lock rows? I know
> that other RDMSs like Interbase have a versioning engine so it was built
> from the ground up for concurrence. And I've read that MSs row level
> locking is a band-aid on its unchanged core engine, although the author of
> that message did not expand further on this.
> so I figured I'd ask the experts what the truth is. Any help is much
> appreciated.
> If indeed it has changed, what sort of test can I run to prove this to my
> boss?
> regards,
> -randall sell|||There was a major rewrite of much of the core database engine code in SQL 7.
SQL 7 and above do a great job of row-level locking.
In versions prior to 7, one would sometimes implement convoluted techniques
to avoid 'hot spots' to circumvent page-level locking issues. Thanks to
row-level locking, hot spots are actually a good thing and can help
performance due to reduced i/o.
However, there is still no row versioning the current SQL Server version.
Snapshot isolation is planned for SQL Server 2005.
> And I've read that MSs row level locking is a band-aid on its unchanged
> core engine, although the author of that message did not expand further on
> this.
Perhaps the author is referring to version 6.5 insert row locking. This is
not true for versions 7 and above.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Randall Sell" <randall@.bytewise.nospam.com.au> wrote in message
news:UnE_c.20421$D7.10166@.news-server.bigpond.net.au...
> Hello all,
> Somewhere on these newsgroups I recall reading that SQL Server 6 and prior
> (when they were married with Sybase) used page locking and not row level
> locking. Hence you could be locking a lot more records then what you think
> when doing an UPDATE or INSERT SQL.
> Now I notice that SQL Server 7 and 2000 claim to use row level locking.
> (As you can see, I have been out of the SQL arena for some time). So what
> I'd like to know if this is all true? Or marketing mumbo-jumbo? Has
> Microsoft made changes at the core of their engine to lock rows? I know
> that other RDMSs like Interbase have a versioning engine so it was built
> from the ground up for concurrence. And I've read that MSs row level
> locking is a band-aid on its unchanged core engine, although the author of
> that message did not expand further on this.
> so I figured I'd ask the experts what the truth is. Any help is much
> appreciated.
> If indeed it has changed, what sort of test can I run to prove this to my
> boss?
> regards,
> -randall sell|||Hi Randall
The complete storage engine was rewritten for SQL Server 7. I worked for the
Sybase Corporation for many years (starting with their version 2), in
training and tech support, and I taught the high-end performance tuning and
internals classes.
When I started working with MS SQL Server, the architectures were almost
identical and the smallest unit of locking was the page. But when SQL 7 came
out, almost everything had to be relearned from an core engine perspective.
Yes, there is true row level locking.
Maybe the person you heard was referring to a pseudo-row-locking introduced
in SQL Servfer 6.5, but that was just because they couldn't rewrite the
whole engire for that version. For SQL 7, they did.
I even wrote a book about it. If you want all the gory internal details,
check out Inside SQL Server 7 and/or Inside SQL Server 2000.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Randall Sell" <randall@.bytewise.nospam.com.au> wrote in message
news:UnE_c.20421$D7.10166@.news-server.bigpond.net.au...
> Hello all,
> Somewhere on these newsgroups I recall reading that SQL Server 6 and prior
> (when they were married with Sybase) used page locking and not row level
> locking. Hence you could be locking a lot more records then what you think
> when doing an UPDATE or INSERT SQL.
> Now I notice that SQL Server 7 and 2000 claim to use row level locking.
(As
> you can see, I have been out of the SQL arena for some time). So what I'd
> like to know if this is all true? Or marketing mumbo-jumbo? Has Microsoft
> made changes at the core of their engine to lock rows? I know that other
> RDMSs like Interbase have a versioning engine so it was built from the
> ground up for concurrence. And I've read that MSs row level locking is a
> band-aid on its unchanged core engine, although the author of that message
> did not expand further on this.
> so I figured I'd ask the experts what the truth is. Any help is much
> appreciated.
> If indeed it has changed, what sort of test can I run to prove this to my
boss?
> regards,
> -randall sell|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:#AAKqx1kEHA.3632@.TK2MSFTNGP09.phx.gbl...
> There was a major rewrite of much of the core database engine code in SQL
7.
> SQL 7 and above do a great job of row-level locking.
> In versions prior to 7, one would sometimes implement convoluted
techniques
> to avoid 'hot spots' to circumvent page-level locking issues. Thanks to
> row-level locking, hot spots are actually a good thing and can help
> performance due to reduced i/o.
> However, there is still no row versioning the current SQL Server version.
> Snapshot isolation is planned for SQL Server 2005.
The penalty of snapshot isolation being that there is no true
'serializable' possible.
The OP suggested that row-level locking is inferior compared
to multi versioning.
Personally I think that both have their advantages.
And using locks in concurrency situations is a choice.
Using multi versioning for concurrencty is another choice.
Both have their advantages and disadvantages.
ben brugman
> > And I've read that MSs row level locking is a band-aid on its unchanged
> > core engine, although the author of that message did not expand further
on
> > this.
> Perhaps the author is referring to version 6.5 insert row locking. This
is
> not true for versions 7 and above.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>|||John wrote:
> My only reservation about Firebird is the 'magic' it has to use to enforce
> unique indexes, foreign keys etc. Ie you could get a situation like this:
> Trans 1:
> Insert into mytable values (1); -- this is the PK
> -- don't commit yet
> Trans 2:
> Insert into mytable values (1); -- doesn't work, PK violation. So try this:
> Select count(*) from mytable where pkfield = 1 -- returns 0. hhhmmmm!
>
Indeed, I can produce the same issue. I would have though that changing the
isolation level would address this, but it does not. This borders (in my
mind) as a bit of a bug. Since the second transaction shouldn't know
anything about an inserted record of "1" from the first transaction hence
it should also execute successfully, and not until one of the two commits,
should the other raise an exception.
Thanx for pointing this out.|||ben brugman wrote:
> The OP suggested that row-level locking is inferior compared
> to multi versioning.
> Personally I think that both have their advantages.
> And using locks in concurrency situations is a choice.
> Using multi versioning for concurrencty is another choice.
> Both have their advantages and disadvantages.
> ben brugman
I would agree; I am not suggesting that either is better then the other. My
problem is that my client would like the software to work on a multitude of
RDMSs. InterBase/Firebird/Oracle and SQL Server. So I am envisioning that
their will be some ugly scenarios where I am trying to cater for both a
locking scenarios and versioning scenario which could get technically very
challenging. On the other hand, if I can convince him to throw out
InterBase/FB and stick with Oracle and SQL Server only, my coding will get
a lot easier. The big plus for Interbase is it's simplicity in installation
and very small footprint compared to the others.
regards,
-randall|||Thanx Kalen. You book actually sits next to my desk 24/7 :)
-randall
Kalen Delaney wrote:
> Hi Randall
> The complete storage engine was rewritten for SQL Server 7. I worked for the
> Sybase Corporation for many years (starting with their version 2), in
> training and tech support, and I taught the high-end performance tuning and
> internals classes.
> When I started working with MS SQL Server, the architectures were almost
> identical and the smallest unit of locking was the page. But when SQL 7 came
> out, almost everything had to be relearned from an core engine perspective.
> Yes, there is true row level locking.
> Maybe the person you heard was referring to a pseudo-row-locking introduced
> in SQL Servfer 6.5, but that was just because they couldn't rewrite the
> whole engire for that version. For SQL 7, they did.
> I even wrote a book about it. If you want all the gory internal details,
> check out Inside SQL Server 7 and/or Inside SQL Server 2000.
>|||"Randall Sell" <randall@.bytewise.nospam.com.au> wrote in message
news:SSR_c.20880$D7.12600@.news-server.bigpond.net.au...
> ben brugman wrote:
> >
> > The OP suggested that row-level locking is inferior compared
> > to multi versioning.
> >
> > Personally I think that both have their advantages.
> > And using locks in concurrency situations is a choice.
> > Using multi versioning for concurrencty is another choice.
> > Both have their advantages and disadvantages.
> >
> > ben brugman
> I would agree; I am not suggesting that either is better then the other.
Aah: but you did write :
"I know that other
RDMSs like Interbase have a versioning engine so it was built from the
ground up for concurrence. "
Suggesting that SQL-server is not built from the ground up for concurrence.
But sorry if I misread.
Then implementing for more than one RDBMS, I can only speak for the
combination Oracle, SQL-server.
Experienced Oracle programmers are used to readers not blocking writers and
writers not blocking readers, so sometimes they do not account for the
behaviour in SQL-server. Building programs with multiple connections, one
connetion doing reading another doing the mutating. In SQL-server this
solution might cause application deadlocks where there where no
application deadlocks in Oracle. The program does some reads in an
open connection passes on the information to another part of the program
with a sepperate connection, this part makes mutations in the just read
parts. SQL-server blocks the mutations because another connection is still
reading. The reading part of the program waits for the mutation part to
finish.
So there is a deadlock, it won't be detected by SQL-server.
The other way round (this is rare), it could be that SQL-server programmers
use
application programmed bussiness rules which make a decision based on read
data. (For the isolation level serializable). This data can not be changed
by another
user and the decision is valid at the end of the transaction.
In Oracle though, allthough your view of the data does not change (because
of the multi-versioning) the 'real' and 'actual' data does change. So at the
end
of the transaction your decision might not be valid.
Allthough it is rare to have business constraints where such an occurence
might
happen, and it's also rare that this will go wrong, it is something you can
not
really test for, so if there is a problem it takes a long while before it
happens and
even a longer while before it is caught.
But these rare events often give more trouble than an error which does
occure often.
ben
My
> problem is that my client would like the software to work on a multitude
of
> RDMSs. InterBase/Firebird/Oracle and SQL Server. So I am envisioning that
> their will be some ugly scenarios where I am trying to cater for both a
> locking scenarios and versioning scenario which could get technically very
> challenging. On the other hand, if I can convince him to throw out
> InterBase/FB and stick with Oracle and SQL Server only, my coding will get
> a lot easier. The big plus for Interbase is it's simplicity in
installation
> and very small footprint compared to the others.
> regards,
> -randall|||John,
Did you notice that your quote from the BOL shows that row locks do not
escalate to table locks? Yes, both row and page locks escalate to table
locks.
FWIW,
Russell Fields
"John" <jsparrowNOSPAM@.ecclesdeletethiscollege.ac.uk> wrote in message
news:%23ICeFp1kEHA.2848@.TK2MSFTNGP15.phx.gbl...
> I'm not an expert, but my understanding it that , yes, SQL Server 2000 has
> true row-level locking. But in heavy use situations this can be expensive,
> so they automatically escalate to page level locks. Hence lots of talk
about
> 'Lock Escalation' - see Books Online.
> - "Lock escalation is the process of converting many fine-grain locks into
> fewer coarse-grain locks, reducing system overhead. Microsoft® SQL ServerT
> 2000 automatically escalates row locks and page locks into table locks
when
> a transaction exceeds its escalation threshold."
> I use Interbase / Firebird too. SQL Server 2005 is going to have a
Snapshot
> isolation level that will offer similar functionality to Firebird (though
> implemented very differently).
> My only reservation about Firebird is the 'magic' it has to use to enforce
> unique indexes, foreign keys etc. Ie you could get a situation like this:
> Trans 1:
> Insert into mytable values (1); -- this is the PK
> -- don't commit yet
> Trans 2:
> Insert into mytable values (1); -- doesn't work, PK violation. So try
this:
> Select count(*) from mytable where pkfield = 1 -- returns 0. hhhmmmm!
> This is because your "trans 2 Insert" can see the "trans 1 insert" before
> it's committed. But the "trans 2 select" can't.
> Cool, but it makes custom Referential Integrity difficult, and requires
> special db design.
> Good old fashioned locking is less sexy, but I find, more productive!
> John
>
> "Randall Sell" <randall@.bytewise.nospam.com.au> wrote in message
> news:UnE_c.20421$D7.10166@.news-server.bigpond.net.au...
> > Hello all,
> >
> > Somewhere on these newsgroups I recall reading that SQL Server 6 and
prior
> > (when they were married with Sybase) used page locking and not row level
> > locking. Hence you could be locking a lot more records then what you
think
> > when doing an UPDATE or INSERT SQL.
> >
> > Now I notice that SQL Server 7 and 2000 claim to use row level locking.
> > (As you can see, I have been out of the SQL arena for some time). So
what
> > I'd like to know if this is all true? Or marketing mumbo-jumbo? Has
> > Microsoft made changes at the core of their engine to lock rows? I know
> > that other RDMSs like Interbase have a versioning engine so it was built
> > from the ground up for concurrence. And I've read that MSs row level
> > locking is a band-aid on its unchanged core engine, although the author
of
> > that message did not expand further on this.
> >
> > so I figured I'd ask the experts what the truth is. Any help is much
> > appreciated.
> >
> > If indeed it has changed, what sort of test can I run to prove this to
my
> > boss?
> >
> > regards,
> > -randall sell
>|||So reread the locking chapter!
:-)
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Randall Sell" <randall@.bytewise.nospam.com.au> wrote in message
news:RUR_c.20882$D7.8300@.news-server.bigpond.net.au...
> Thanx Kalen. You book actually sits next to my desk 24/7 :)
> -randall
> Kalen Delaney wrote:
> > Hi Randall
> >
> > The complete storage engine was rewritten for SQL Server 7. I worked for
the
> > Sybase Corporation for many years (starting with their version 2), in
> > training and tech support, and I taught the high-end performance tuning
and
> > internals classes.
> >
> > When I started working with MS SQL Server, the architectures were almost
> > identical and the smallest unit of locking was the page. But when SQL 7
came
> > out, almost everything had to be relearned from an core engine
perspective.
> > Yes, there is true row level locking.
> >
> > Maybe the person you heard was referring to a pseudo-row-locking
introduced
> > in SQL Servfer 6.5, but that was just because they couldn't rewrite the
> > whole engire for that version. For SQL 7, they did.
> >
> > I even wrote a book about it. If you want all the gory internal details,
> > check out Inside SQL Server 7 and/or Inside SQL Server 2000.
> >|||The Firebird guys don't consider this a bug, just an artifact of a different
concurrency system... it's certainly something you should be aware of. I
think there are thousands of systems out there that are potentially buggy in
multi-user situations (I know some of mine are! shhhhhh).
Maybe it's a problem of Interbase marketing - it used to be aimed at dbase /
Access crowd, whereas actually it is *very* specialist and arguably the
concurrency semantics are more tricky to learn than SQL Server.
I agree it would be nice if these were deferred until commit. That would
seem to be 1) easier to implement 2) more logical. While it may be a pain in
the ass for client/server systems (long transaction), n-tier and web systems
would be fine with it. Who writes long transaction apps these days' (leave
the room, now! grin).
Consider checking for overlapping periods. Table UserCosts like:
pk int primary key
startdate datetime (timestamp in Interbase)
enddate datetime
You want to prevent overlapping periods. Can't do it with indexes, so no
Interbase 'magic' can't come into play. In SQL Server you can write a
trigger something like:
if exists(select i.entryid from inserted i inner join UserCosts u on
not(i.startdate > u.enddate or i.enddate < u.startdate) and i.entryid <>
u.entryid)
begin
raiserror('UserCost intersects another record',16,1)
rollback transaction
return
end
In Interbase / Firebird you can't see other uncommitted records!!! You have
two choices: 1) use table_stability isolation, and essentially go
single-user. or 2) implement a dodgy linked-list style approach.
Interbase has a 'check contraint select' that is totally broken - because it
uses the user context and not the 'magic' system context.
After exploring these issues, I switched development of a project from
Firebird to SQL Server.
Having said all this, I do think Firebird is cool. You just need to be aware
of it's quirks and limitations. It's not in the same league as SQL Server.
But it is very good (and free, more so than MySQL, and multi-platform).
Perhaps in the n-tier / webby world, cool concurrency with long-duration
transactions is not so important?
John
"Randall Sell" <randall@.bytewise.nospam.com.au> wrote in message
news:UKR_c.20876$D7.9498@.news-server.bigpond.net.au...
> John wrote:
>> My only reservation about Firebird is the 'magic' it has to use to
>> enforce unique indexes, foreign keys etc. Ie you could get a situation
>> like this:
>> Trans 1:
>> Insert into mytable values (1); -- this is the PK
>> -- don't commit yet
>> Trans 2:
>> Insert into mytable values (1); -- doesn't work, PK violation. So try
>> this:
>> Select count(*) from mytable where pkfield = 1 -- returns 0. hhhmmmm!
> Indeed, I can produce the same issue. I would have though that changing
> the isolation level would address this, but it does not. This borders (in
> my mind) as a bit of a bug. Since the second transaction shouldn't know
> anything about an inserted record of "1" from the first transaction hence
> it should also execute successfully, and not until one of the two commits,
> should the other raise an exception.
> Thanx for pointing this out.|||Hello Kalen,
Although I have read the Inside and
the Hands-On SQL server 2000 Troubleshooting :Locking and Blocking.
The last several times and specific for locking and blocking caused
by selects only. I must say I never found a clear indication that
a select on a single row in one table could be 'causing' a deadlock.
(In combination with another transaction doing updates on that
one single row).
If that is in your book or Hands-On, it would be a help if you could
point this out to me.
Thanks for your books,
greetings,
ben brugman
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:#sS#MsClEHA.1652@.TK2MSFTNGP09.phx.gbl...
> So reread the locking chapter!
> :-)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Randall Sell" <randall@.bytewise.nospam.com.au> wrote in message
> news:RUR_c.20882$D7.8300@.news-server.bigpond.net.au...
> > Thanx Kalen. You book actually sits next to my desk 24/7 :)
> >
> > -randall
> >
> > Kalen Delaney wrote:
> >
> > > Hi Randall
> > >
> > > The complete storage engine was rewritten for SQL Server 7. I worked
for
> the
> > > Sybase Corporation for many years (starting with their version 2), in
> > > training and tech support, and I taught the high-end performance
tuning
> and
> > > internals classes.
> > >
> > > When I started working with MS SQL Server, the architectures were
almost
> > > identical and the smallest unit of locking was the page. But when SQL
7
> came
> > > out, almost everything had to be relearned from an core engine
> perspective.
> > > Yes, there is true row level locking.
> > >
> > > Maybe the person you heard was referring to a pseudo-row-locking
> introduced
> > > in SQL Servfer 6.5, but that was just because they couldn't rewrite
the
> > > whole engire for that version. For SQL 7, they did.
> > >
> > > I even wrote a book about it. If you want all the gory internal
details,
> > > check out Inside SQL Server 7 and/or Inside SQL Server 2000.
> > >
>|||Hi Ben
Did I miss something in this thread? I thought it was just talking about
whether SQL Server really did row locking.
If you want to start a new thread, that's fine. But give it a specific
subject so it's easier to track.
Thanks!
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ben brugman" <ben@.niethier.nl> wrote in message
news:u$K3dLNlEHA.3712@.TK2MSFTNGP15.phx.gbl...
> Hello Kalen,
> Although I have read the Inside and
> the Hands-On SQL server 2000 Troubleshooting :Locking and Blocking.
> The last several times and specific for locking and blocking caused
> by selects only. I must say I never found a clear indication that
> a select on a single row in one table could be 'causing' a deadlock.
> (In combination with another transaction doing updates on that
> one single row).
> If that is in your book or Hands-On, it would be a help if you could
> point this out to me.
> Thanks for your books,
> greetings,
> ben brugman
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:#sS#MsClEHA.1652@.TK2MSFTNGP09.phx.gbl...
>> So reread the locking chapter!
>> :-)
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Randall Sell" <randall@.bytewise.nospam.com.au> wrote in message
>> news:RUR_c.20882$D7.8300@.news-server.bigpond.net.au...
>> > Thanx Kalen. You book actually sits next to my desk 24/7 :)
>> >
>> > -randall
>> >
>> > Kalen Delaney wrote:
>> >
>> > > Hi Randall
>> > >
>> > > The complete storage engine was rewritten for SQL Server 7. I worked
> for
>> the
>> > > Sybase Corporation for many years (starting with their version 2), in
>> > > training and tech support, and I taught the high-end performance
> tuning
>> and
>> > > internals classes.
>> > >
>> > > When I started working with MS SQL Server, the architectures were
> almost
>> > > identical and the smallest unit of locking was the page. But when SQL
> 7
>> came
>> > > out, almost everything had to be relearned from an core engine
>> perspective.
>> > > Yes, there is true row level locking.
>> > >
>> > > Maybe the person you heard was referring to a pseudo-row-locking
>> introduced
>> > > in SQL Servfer 6.5, but that was just because they couldn't rewrite
> the
>> > > whole engire for that version. For SQL 7, they did.
>> > >
>> > > I even wrote a book about it. If you want all the gory internal
> details,
>> > > check out Inside SQL Server 7 and/or Inside SQL Server 2000.
>> > >
>>
>|||Hello the OP mentioned row versus page locking and versioning
mechanisms. And suggesting the versioning is superior to
locking ("built from the ground up for concurrence").
If one distincts between these methods, one should distinct as well
between the disadvantages and advantages between these methods.
For both systems (versioning and locking) the disadvantages are
not advertised.
Even the better books do not mention some disadvantages.
Had I know of these disadvantages than it would have
saved me some time. Mentioning this in a different thread would
maybe not have reached the OP. So I thought it appropriate to point
out these disadvantages in the thread.
My apologies if this was not clearly stated in my previous mail.
ben brugman.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:#RHzIKUlEHA.3564@.TK2MSFTNGP14.phx.gbl...
> Hi Ben
> Did I miss something in this thread? I thought it was just talking about
> whether SQL Server really did row locking.
> If you want to start a new thread, that's fine. But give it a specific
> subject so it's easier to track.
> Thanks!
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:u$K3dLNlEHA.3712@.TK2MSFTNGP15.phx.gbl...
> > Hello Kalen,
> >
> > Although I have read the Inside and
> > the Hands-On SQL server 2000 Troubleshooting :Locking and Blocking.
> >
> > The last several times and specific for locking and blocking caused
> > by selects only. I must say I never found a clear indication that
> > a select on a single row in one table could be 'causing' a deadlock.
> > (In combination with another transaction doing updates on that
> > one single row).
> >
> > If that is in your book or Hands-On, it would be a help if you could
> > point this out to me.
> >
> > Thanks for your books,
> > greetings,
> > ben brugman
> >
> >
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:#sS#MsClEHA.1652@.TK2MSFTNGP09.phx.gbl...
> >> So reread the locking chapter!
> >> :-)
> >>
> >> --
> >> HTH
> >> --
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Randall Sell" <randall@.bytewise.nospam.com.au> wrote in message
> >> news:RUR_c.20882$D7.8300@.news-server.bigpond.net.au...
> >> > Thanx Kalen. You book actually sits next to my desk 24/7 :)
> >> >
> >> > -randall
> >> >
> >> > Kalen Delaney wrote:
> >> >
> >> > > Hi Randall
> >> > >
> >> > > The complete storage engine was rewritten for SQL Server 7. I
worked
> > for
> >> the
> >> > > Sybase Corporation for many years (starting with their version 2),
in
> >> > > training and tech support, and I taught the high-end performance
> > tuning
> >> and
> >> > > internals classes.
> >> > >
> >> > > When I started working with MS SQL Server, the architectures were
> > almost
> >> > > identical and the smallest unit of locking was the page. But when
SQL
> > 7
> >> came
> >> > > out, almost everything had to be relearned from an core engine
> >> perspective.
> >> > > Yes, there is true row level locking.
> >> > >
> >> > > Maybe the person you heard was referring to a pseudo-row-locking
> >> introduced
> >> > > in SQL Servfer 6.5, but that was just because they couldn't rewrite
> > the
> >> > > whole engire for that version. For SQL 7, they did.
> >> > >
> >> > > I even wrote a book about it. If you want all the gory internal
> > details,
> >> > > check out Inside SQL Server 7 and/or Inside SQL Server 2000.
> >> > >
> >>
> >>
> >
> >
>|||ben brugman wrote:
> Hello the OP mentioned row versus page locking and versioning
> mechanisms. And suggesting the versioning is superior to
> locking ("built from the ground up for concurrence").
> If one distincts between these methods, one should distinct as well
> between the disadvantages and advantages between these methods.
> For both systems (versioning and locking) the disadvantages are
> not advertised.
> Even the better books do not mention some disadvantages.
> Had I know of these disadvantages than it would have
> saved me some time. Mentioning this in a different thread would
> maybe not have reached the OP. So I thought it appropriate to point
> out these disadvantages in the thread.
> My apologies if this was not clearly stated in my previous mail.
> ben brugman.
Hi ben !
http://citeseer.ist.psu.edu/berenson95critique.html (click upper right
corner to get pdf) did a detailed analysis on mvcc, mvrc, mvto, snapshot
isolation level.
It shows, that MS SQL Server is 10 years behind current "state of the
art" in sql server design, you can see, if you have a look at the
thousands of threads here in m.p.s.s. Those MVP desperately try to tell
us, that those problems don't come from bad design of sql server, those
would be programming mistakes.
regards, Guido Stepken