Re: UPDLOCK and HOLDLOCK
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 11/16/04
- Next message: JProk: "rownum?"
- Previous message: Andrew J. Kelly: "Re: TRANSACTION QUESTION"
- In reply to: Toby Herring: "Re: UPDLOCK and HOLDLOCK"
- Next in thread: Toby Herring: "Re: UPDLOCK and HOLDLOCK"
- Reply: Toby Herring: "Re: UPDLOCK and HOLDLOCK"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 16 Nov 2004 17:43:52 -0500
It's purpose is to tell sql server where to start and not where to end up.
Since the default is ROW level it is usually pointless to specify a ROWLOCK
hint but to be complete and cover all the bases they have it. I can think
of only one time where I saw it used effectively and in a nutshell it went
something like this: There were updates being done that affected thousands
of rows at a time. Most of these rows were contiguous and all on one of
several pages. It would be more efficient for SQL Server to use a Pagelock
instead of several thousand row locks. But there was one or two rows in
amongst these that needed to be available for another process to read and
update. If page level locks were taken it would block the other process.
By specifying Rowlock in the update it kept it at a row level and not the
page. You see locks are only escalated directly up to the table level. It
never goes from Row - page - table. It's always Row - table or Page to
table. There were not enough locks to warrant a table level escalation so
the Rowlock hint kept sql server from starting at the Page level which it
has all rights to do if the conditions are right. The more useful of the
locking hints is actually the PAGLOCK hint. That tells SQL Server to start
at the page level and skip the rows altogether. In a situation such as I
mentioned above where you don't have contention on any of the pages being
updated you can save a lot of locks and overhead. Let me know if that
clears it up or just confuses you more<g>.
-- Andrew J. Kelly SQL MVP "Toby Herring" <therring*@*teletrack.com> wrote in message news:uDEAVHCzEHA.3408@tk2msftngp13.phx.gbl... > Really? My experience says otherwise. Do you know of any sample cases > that show this to be the case? > > Everybody else in here jumped all over my about a month ago about ROWLOCK > being the default locking mechanism. If it is in fact the default, but it > doesn't prevent escalation, what exactly is the point of its existence? > > -- > Toby Herring > MCDBA, MCSD, MCP+SB > Need a Second Life? > http://secondlife.com/ss/?u=03e0e5b303c234bf08e80ee40119a65e > > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:%23z7G1PBzEHA.2016@TK2MSFTNGP15.phx.gbl... >> They believe it will keep the locks from escalating but this is just not >> true. > >
- Next message: JProk: "rownum?"
- Previous message: Andrew J. Kelly: "Re: TRANSACTION QUESTION"
- In reply to: Toby Herring: "Re: UPDLOCK and HOLDLOCK"
- Next in thread: Toby Herring: "Re: UPDLOCK and HOLDLOCK"
- Reply: Toby Herring: "Re: UPDLOCK and HOLDLOCK"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
