rowlock hint and lock escalation
Amol Kasbekar
I have a table, in which one of the columns is connectionid, populated by
@@spid. Every client has his own @@spid and uses it as a part of primary key
to insert and delete rows from this table. Thus rows for each
client(connection) are exclusive.
I have a stored procedure that inserts and deletes rows into this table for
every client that connects to the db. We found that in a lot of instances,
the first client that connects to the DB and tries to insert row into this
table, gets a tablelock and all other subsequent users get blocked. To
workaround this, we added the rowlock hint to every insert and delete
statement on this table in the procedure. There are no update statements.
This worked fine initially on my local db on my machine and performance was
not drastically affected too. However, when I tried to test this on a DB on
a different server, I found that the SQL Server was still placing a
tablelock on the table for the first user. Subsequently I used
sp_indexoption to modify the pk index on this table to disallow pagelocking
and then it started working properly again. However it is slower now.
Can anyone explain to me why this behaviour. Does SQL Server still escalate
locks to table level <if it finds too many rowlocks> inspite of the rowlock
hint ?? The database on both the servers are identical.
Thanks in advance,
Amol.
Kalen Delaney
Escalation can still occur when the total amount of memory that SQL Server
is using to keep track of locks crosses an internal threshold. This
threshold is not configurable.
--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Amol Kasbekar" <a...@cbord.com> wrote in message
news:uDVHoFTN...@TK2MSFTNGP11.phx.gbl...
Amol Kasbekar
Thanks Kalen for the quick reply. So that means there isnt "any" way at all
to force the Server to maintain the row-level granularity while locking,
that really hurts us. Also how did disallowing the index to use page locking
help in the second case ??
- Amol.
"Kalen Delaney" <replies@public_newsgroups.com> wrote in message
news:uV2vdJTN...@TK2MSFTNGP11.phx.gbl...
Kalen Delaney
INF: Resolving Blocking Problems That Are Caused by Lock Escalation in SQL
Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;323630
There is ALWAYS a tradeoff; as the KB article mentions, if you disallow
escalation, you can end up eating up all your memory for locks, and then
EVERYONE will suffer.
I can't say exactly why disallowing page locks helped without a lot more
analysis.
--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Amol Kasbekar" <a...@cbord.com> wrote in message
news:O$V6hUTND...@tk2msftngp13.phx.gbl...
JXStern
wrote:
>Can anyone explain to me why this behaviour. Does SQL Server still escalate
>locks to table level <if it finds too many rowlocks> inspite of the rowlock
>hint ?? The database on both the servers are identical.
As Kalen says, it may still escalate.
It probably does this based on a percentage of rows in the table, not
on a fixed count. You might try adding a few hundred (or thousand)
dummy records to the table and see if the problem goes away.
That said, I'm not clear on why ANY rows are locked in your scenario.
Do your user connections start a transaction, add a few rows, and then
sit there without finishing the transaction? If so, SQLServer is not
going to like that, as you have seen, and the dummy rows will probably
not fix your problem.
Joshua Stern
Kalen Delaney
used for managing locks. Adding more rows to a table isn't going to help, it
will just produce more rows that could potentially be locked.
"JXStern" <JXSternC...@gte.net> wrote in message
news:1tgvev4bt73vllgc1...@4ax.com...
SQL Guy
Gary
"Amol Kasbekar" <a...@cbord.com> wrote in message
news:uDVHoFTN...@TK2MSFTNGP11.phx.gbl...
>
AJAY R
Suffering from same problem .
Got some knowledge from your message
thanks
but I would like to know how did u use rowlock hint with a query , any
practical example will help me .
the indexoption I am going to try it .
Regards
Ajay
"Amol Kasbekar" <a...@cbord.com> wrote in message
news:uDVHoFTN...@TK2MSFTNGP11.phx.gbl...
>
JXStern
<replies@public_newsgroups.com> wrote:
>Escalation occurs when a fixed percentage of SQL Server's total memory is
>used for managing locks. Adding more rows to a table isn't going to help, it
>will just produce more rows that could potentially be locked.
Kalen,
Thanks, I (obviously) didn't know that. Not memory for that process,
even? Interesting design point, have to remember it. Has it been
that way since SQL7? (I'd assume so).
Joshua Stern
Kalen Delaney
unexpected, but that's the way it works. One process could hold zillions of
locks, but be just shy of the threshhold for escalation, and then another
one comes along and takes just a few dozen locks, and wham, they get
escalated because now SQL Server has crossed the threshhold.
The lock manager will be undergoing significant changes for the next
version, but I don't know any exact details yet.
--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"JXStern" <JXSternC...@gte.net> wrote in message
news:tcu1fvkh70ktsqtdm...@4ax.com...