Re: row vs page locking...

From: John (jsparrowNOSPAM_at_ecclesdeletethiscollege.ac.uk)
Date: 09/05/04 

Date: Sun, 5 Sep 2004 16:08:57 +0100

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

Relevant Pages

  • Re: UPDLOCK and HOLDLOCK
    ... It's purpose is to tell sql server where to start and not where to end up. ... hint but to be complete and cover all the bases they have it. ... If page level locks were taken it would block the other process. ... If it is in fact the default, but it> doesn't prevent escalation, what exactly is the point of its existence? ...
    (microsoft.public.sqlserver.programming)
  • Re: Detecting Table Locks?
    ... And no its not a problem with the sql as to why the operation is taking ... A damn fair use of SQL Server if you ask me (isnt that one of its ... Yes, when your are disconnected there is no locks, but when you call ... The OP is talking about 5 minutes of locking, in my idea that can never be ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Drop Down Box Locking Record
    ... When I look at the locks section in the SQL server it is ... showing that the above statement is locking that record ... It has something to do with multiple users but I can't ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Drop Down Box Locking Record
    ... I would concur with what you say but I look in the locks ... information in SQL Server and the select statement that is ... the drop down box is locking the record. ...
    (microsoft.public.access.adp.sqlserver)
  • pk_dtproperties Question
    ... I'm having some weird locking in SQL Server 2000 and I look the locks with a ... modified sp_locks it shows thousands of the locks below. ... any code or SQL scripts that use that table and the table has 0 rows. ...
    (microsoft.public.sqlserver.server)