Here is an example of what I am talking about:
Query 1
Select statement on table "A" with a suitable where clause to define only the records desired.
Query 2
Multiple insert statements within a single transaction to several different tables including table "A."
The resulting scenario is that query 2 has crossed the magic threshold of 1250 locks and therefore escalates its lock to a table lock. Query 1, which is now blocked, also escalates to a table lock. The two queries then sit around waiting for each other to
finish, but they never do.
Here are my questions:
The lock manager will begin escalation if a single resource is using more than 1250 locks on table resources, or 765 locks on index resources. If the machine in question has 2GB of physical memory, how big is the memory pool for locks?
Even though this isn't the classic deadlock scenario, why doesn't the deadlock manager recognize this as a deadlock and choose a victim to help free up the resources?
Thanks.
Hi,
From your descriptions, I understood that you would like to know how much
memory will be allocated to locks and what happened in the scenario you
described. Have I understood you? If there is anything I misunderstood,
please feel free to let me know
For the first question, based on my knowledge, there are no standard for
allocating memory pool for locks, so I cannot tell how much it will be
For the second question, could you reporduce ti? It is appreciated if you
could provide me some sample scenario, which, I believe, will make us
closer to the resolution.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
|||Lock Pool:
According to a resource that I have now, the number of slots in the lock hash table directly relates to physical memory size. Since we have more than 1GB of physical memory, the number of slots in the hash table is 2 to the 19th, or 512K. If this has
h table gets fulls because of the number of locks in the system does it engage the escalation process and start upgrading locks to table locks?
Block Scenario:
Yes, we can reproduce this problem very easily. The best way to do it is to setup one query to read a set of rows (< 10) from table "A." This should be the first query executed. Then while that query is running, start another query that insert at leas
t 200 rows into table "A" within a transaction. What we are seeing here is that both resources get blocked, and never return.
Let me know if you need further details. Thanks.
Ken L
|||Hello Ken,
The lock escalation attempts are never blocked in SQL Server. If the lock
threshold is crossed and we decide we want to escalate but someone holds an
incompatible lock on the table, we simply cancel the escalation attempt and
continue acquiring locks at the page, row or key level.
Also, if there was truly a simple lock-based deadlock then the deadlock
monitor should detect it and roll back one of the participants.
As far as the particulars of lock escalation thresholds, they are
undocumented and have changed between 7.0 and SQL 2000, and again with
service packs for SQL 2000.
Investigation of your scenario would need trace, blocker script and
errorlog analysis which would be done quickly and effectively with direct
assistance from a Microsoft Support Professional through Microsoft Product
Support Services. You can contact Microsoft Product Support directly to
discuss additional support options you may have available, by contacting us
at 1-(800)936-5800 or by choosing one of the options listed at
http://support.microsoft.com/default...=sz;en-us;top.
If this is not an urgent issue and your would like us to create an incident
for you and have Microsoft Customer Service Representative contact you
directly, please send email to (remove "online." from this no Spam email
address): mailto:dscommhf@.online.microsoft.com with the following
information,
*Include "Followup: <Tomcat IssueID>" in the email Subject.
*Location of the post
*Subject Line
*First Name, Last Name
*MSDN Subscriber ID
*Company name (if any)
*Phone number
*e-mail address
Thanks for using MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Blocked Resources Never Release
>thread-index: AcQ4/1L2Dymf0MfBRy6pVZIiiOpc7A==
>X-WN-Post: microsoft.public.sqlserver.server
>From: "=?Utf-8?B?S2VuIEw=?=" <visDev@.online.nospam>
>References: <AC3EA969-391B-4849-8473-99BBA5EC96B5@.microsoft.com>
<cEHqVuNOEHA.424@.cpmsftngxa10.phx.gbl>
>Subject: RE: Blocked Resources Never Release
>Date: Thu, 13 May 2004 08:31:13 -0700
>Lines: 9
>Message-ID: <24381669-EE80-4206-9EDF-8D36C1C5CA3E@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>Path: cpmsftngxa10.phx.gbl
>Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:341150
>NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Lock Pool:
According to a resource that I have now, the number of slots in the
lock hash table directly relates to physical memory size. Since we have
more than 1GB of physical memory, the number of slots in the hash table is
2 to the 19th, or 512K. If this hash table gets fulls because of the number
of locks in the system does it engage the escalation process and start
upgrading locks to table locks?
Block Scenario:
Yes, we can reproduce this problem very easily. The best way to do it
is to setup one query to read a set of rows (< 10) from table "A." This
should be the first query executed. Then while that query is running, start
another query that insert at least 200 rows into table "A" within a
transaction. What we are seeing here is that both resources get blocked,
and never return.
Let me know if you need further details. Thanks.
Ken L
>
No comments:
Post a Comment