Row lock - Hold Lock

If I issue a Rowlock and holdlock statements in my select statement, would it
still escalate to pagelock or tablelock or it would keep the rowlock until
the transaction is done ?.
Thanks.
First off you can not limit the lock to a row by specifying the hint. That
only tells it to start there but it is still free to escalate up to a table
if the conditions are right. Locks never escalate from row to page, they
always go straight to table if they escalate at all. Adding HOLDLOCK to a
select does little or nothing to the way the locks are done. By default SQL
Server will lock the row as it is reading it and you don't need a hint to do
that. It releases it when it is done reading the row. HOLDLOCK is usually
used to hold the locks until the end of a transaction that was started with
a BEGIN TRAN and has multiple statements in it. What is the intended purpose
of the hint and why are you worried about it escalating? If you have proper
indexes and a proper WHERE clause it should never escalate unless you are
trying to touch a major portion of the total rows.
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> If I issue a Rowlock and holdlock statements in my select statement, would
> it
> still escalate to pagelock or tablelock or it would keep the rowlock until
> the transaction is done ?.
> Thanks.
|||Thanks for the info.......
We have a table with 15 columns. The first column is 'username' that has a
clustered index. 12 of the rest of the columns are deleted/inserted/updated
by individual users. If the users are running the same process at the same
time, each user inserts ~30000 rows into the table after deleting the rows
that are belong to them.
Out of the profiler, I have seen a few lock escalations. What is the best
way to index these columns ?
username column is like below;
username
user1
user1
user1
user1
user1
user1
user2
user2
user2
user2
user2
user3
user3
user3
user3
"Andrew J. Kelly" wrote:

> First off you can not limit the lock to a row by specifying the hint. That
> only tells it to start there but it is still free to escalate up to a table
> if the conditions are right. Locks never escalate from row to page, they
> always go straight to table if they escalate at all. Adding HOLDLOCK to a
> select does little or nothing to the way the locks are done. By default SQL
> Server will lock the row as it is reading it and you don't need a hint to do
> that. It releases it when it is done reading the row. HOLDLOCK is usually
> used to hold the locks until the end of a transaction that was started with
> a BEGIN TRAN and has multiple statements in it. What is the intended purpose
> of the hint and why are you worried about it escalating? If you have proper
> indexes and a proper WHERE clause it should never escalate unless you are
> trying to touch a major portion of the total rows.
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>
>
|||That doesn't make a lot of sense. You can't Delete or Insert a column. I
assume you mean the users Insert, Update or Delete the rows. How many rows
in the whole table? If you attempt to Delete 30K rows in a relatively small
table then SQL Server will most likely try to take out a table level lock.
Does the DELETE include the clustered column in the WHERE clause? You can
stop escalation to the table level by always having at least one shared lock
in the table. But if you delete the rows in smaller batches you won't
escalate as long as they are not all wrapped in a single transaction.
SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
efficient.
WHILE 1 = 1
BEGIN
DELETE FROM YourTable WHERE UserName = xxx
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...[vbcol=seagreen]
> Thanks for the info.......
> We have a table with 15 columns. The first column is 'username' that has a
> clustered index. 12 of the rest of the columns are
> deleted/inserted/updated
> by individual users. If the users are running the same process at the same
> time, each user inserts ~30000 rows into the table after deleting the rows
> that are belong to them.
> Out of the profiler, I have seen a few lock escalations. What is the best
> way to index these columns ?
> username column is like below;
> username
> user1
> user1
> user1
> user1
> user1
> user1
> user2
> user2
> user2
> user2
> user2
> user3
> user3
> user3
> user3
>
> "Andrew J. Kelly" wrote:
|||Yes, the users are doing the insert/update/delete through the application
which executes the stored procs.
I think that delete is not the problem but the update and the insert. First,
the particular use's name (username) is deleted from the table (all 30000
rows) but that is relatively quick. Then, the new 30000 rows are inserted to
the table. As the last step, the values are updated at the table after
certain calculations (Other 12 column)
thanks.
"Andrew J. Kelly" wrote:

> That doesn't make a lot of sense. You can't Delete or Insert a column. I
> assume you mean the users Insert, Update or Delete the rows. How many rows
> in the whole table? If you attempt to Delete 30K rows in a relatively small
> table then SQL Server will most likely try to take out a table level lock.
> Does the DELETE include the clustered column in the WHERE clause? You can
> stop escalation to the table level by always having at least one shared lock
> in the table. But if you delete the rows in smaller batches you won't
> escalate as long as they are not all wrapped in a single transaction.
>
> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
> efficient.
> WHILE 1 = 1
> BEGIN
> DELETE FROM YourTable WHERE UserName = xxx
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
>
>
|||Why not prep the values before you insert them so you don't have to make
several passes? How are these 30K rows inserted? Are they one by one or
are you using a bulk load process?
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:68BAEDEC-AEE5-4CE8-80C8-E1D4CC5573BA@.microsoft.com...[vbcol=seagreen]
> Yes, the users are doing the insert/update/delete through the application
> which executes the stored procs.
> I think that delete is not the problem but the update and the insert.
> First,
> the particular use's name (username) is deleted from the table (all 30000
> rows) but that is relatively quick. Then, the new 30000 rows are inserted
> to
> the table. As the last step, the values are updated at the table after
> certain calculations (Other 12 column)
> thanks.
>
> "Andrew J. Kelly" wrote: