Re: row vs page locking...
From: John (jsparrowNOSPAM_at_ecclesdeletethiscollege.ac.uk)
Date: 09/05/04
- Next message: Dan Guzman: "Re: row vs page locking..."
- Previous message: John: "Re: Single user mode after maint plan"
- In reply to: Randall Sell: "row vs page locking..."
- Next in thread: Randall Sell: "Re: row vs page locking..."
- Reply: Randall Sell: "Re: row vs page locking..."
- Reply: Russell Fields: "Re: row vs page locking..."
- Messages sorted by: [ date ] [ thread ]
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
- Next message: Dan Guzman: "Re: row vs page locking..."
- Previous message: John: "Re: Single user mode after maint plan"
- In reply to: Randall Sell: "row vs page locking..."
- Next in thread: Randall Sell: "Re: row vs page locking..."
- Reply: Randall Sell: "Re: row vs page locking..."
- Reply: Russell Fields: "Re: row vs page locking..."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
