Page vs Row 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 sellRandall Sell (randall@.bytewise.nospam.com.au) writes:
> 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.

That poster may have been thinking of SQL 6.5, which had a bascially
unchanged engine from 4.x days, but where you could set a table option
to get "insert row locks" which was good for tables with heavy insert
frequency at a hot spot.

But that was very long ago. MS shipped SQL7 in the end of 1998, and SQL7
was almost complete rewrite, and very little of the original Sybase code
survived. There are still page locks in SQL Server, but I have to confess
that I don't know when they are used. Row locks and table locks is what
you usually see. (Table locks when there is no suitable index, or the
query affects the entire table anyway.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||thanx for the info Erland
-rs

Erland Sommarskog wrote:

> Randall Sell (randall@.bytewise.nospam.com.au) writes:
>>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.
>
> That poster may have been thinking of SQL 6.5, which had a bascially
> unchanged engine from 4.x days, but where you could set a table option
> to get "insert row locks" which was good for tables with heavy insert
> frequency at a hot spot.
> But that was very long ago. MS shipped SQL7 in the end of 1998, and SQL7
> was almost complete rewrite, and very little of the original Sybase code
> survived. There are still page locks in SQL Server, but I have to confess
> that I don't know when they are used. Row locks and table locks is what
> you usually see. (Table locks when there is no suitable index, or the
> query affects the entire table anyway.)
>