Here's a really weird one for any SQL Server gurus out there...
We have observed (SQL Server 2000) scenarios where a stored procedure
which
(a) begins a transaction
(b) inserts some rows into a table
(c) re-queries another table using a subquery which references the
inserted table (correlated or not)
will deadlock itself at point (c), even when it is the only task on
the server.
[I use the term 'deadlock' because I can't think of anything else
appropriate, even though, as stated, this is the ONLY task executing,
so this isn't a classical deadlock situation]
A typical deadlocking scenario would be (assume insert_table is the
table into which some rows are being inserted)
begin transaction
insert insert_table(co l....) select (col....) from some_other_tabl e
/* this following query will deadlock and never complete */
select some_other_tabl e.col
from some_other_tabl e
where not exists (select *
from insert_table
where some_other_tabl e.col = insert_table.co l )
Whereas if the offending second query in the sequence is rewritten as
a join
e.g
select some_other_tabl e.col
from some_other_tabl e
left join insert_table
on some_other_tabl e.col = insert_table.co l
where insert_table.co l is null
the query will not deadlock.
If the subquery is an aggregate function, a deadlock will also not
occur.
If the transaction is committed prior to executing the blocking query,
then it will not block (hardly surprising; if it did, there'd be major
problems with SQL Server!).
Note that this is a canonical case of a much more complex SP, and that
simplified test cases often will not deadlock; you need a significant
amount of data, typically 30,000 rows or more to see the problem. The
blocking query is, in real life, used to drive a subsequent table
insert operation, but this is not relevant to the problem.
We conclude that there is some problem, possibly involving catalogue
contention, if a temporary table must be created in a subquery within
a transaction in a stored procedure, and if the subquery involves
references to a table for which locks have been acquired.
Note that the lock timeout will never trigger and a deadlock victim is
never chosen, presumably because the deadlock occurs entirely within
the same SPID.
Locking hints and transaction isolation level setting does not affect
the result. Note also that the exact same queries, executed as a TSQL
batch, do not deadlock; you must place them in a stored procedure.
Recovery mode for the database is SIMPLE, and the problem is portable
across databases; it can also be exhibited with MSDE/2000, and is
independent of whether or not the database server is the local machine
or not.
Has anyone else experienced this problem and/or know of a workaround,
other than those mentioned here?. It does look awfully like a bug with
SQL Server, since a single task should never be able to deadlock
itself, surely.
7  9224 
Andrew Mayo (aj****@my-deja.com) writes: A typical deadlocking scenario would be (assume insert_table is the
table into which some rows are being inserted)
begin transaction
insert insert_table(co l....) select (col....) from some_other_tabl e
/* this following query will deadlock and never complete */
select some_other_tabl e.col
from some_other_tabl e
where not exists (select *
from insert_table
where some_other_tabl e.col = insert_table.co l )
...
If the transaction is committed prior to executing the blocking query,
then it will not block (hardly surprising; if it did, there'd be major
problems with SQL Server!).
...
We conclude that there is some problem, possibly involving catalogue
contention, if a temporary table must be created in a subquery within
a transaction in a stored procedure, and if the subquery involves
references to a table for which locks have been acquired.
Without a reproducible scenarion, it is very difficult to comment. Of
course, a real mean person can get the above to deadlock itself: in the
SELECT call a UDF which calls xp_cmdshell that tries to retrieve a
row from insert_table. This will cause a big fat deadlock, that SQL
Server of course cannot detect. I would not really expect this to
the problem, though.
What I would recomment as a first shot is to use my routine aba_lockinfo, http://www.sommarskog.se/sqlutil/aba_lockinfo.html. This routine gives
you a good overview of what's going on in the server. In particular,
you can see if there is some blocking, and in such what you are blocked
by. You can also keep an eye at the cpu, io and memory columns to
determine wheter the process is working.
Another place to look is the SQL Server error log. Particularly I would
be interested if there is any talk of UMS scheduler.
Unless any of what I have set leads you to a revelation, I would recommend
you to open a case with Microsoft, as it appears to be too complex to
pursue the actual case over the newsgroups.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Erland Sommarskog <es****@sommars kog.se> wrote in message news:<Xn******* **************@ 127.0.0.1>... Without a reproducible scenarion, it is very difficult to comment. Of
course, a real mean person can get the above to deadlock itself: in the
SELECT call a UDF which calls xp_cmdshell that tries to retrieve a
row from insert_table. This will cause a big fat deadlock, that SQL
Server of course cannot detect. I would not really expect this to
the problem, though.
Thanks very much; that's a useful hint.
We found, after further exploration, that rephrasing the subqueries as
self joins did NOT resolve the problem, although a simple subquery
expressed as a single left outer join DID cause the SP to stop
blocking. However, more complex self-joins caused the problem to
reappear.
We concluded that the problem is likely triggered by the query plan
requiring a temp table to be dynamically created in order to either
satisfy the complex join condition or process the subquery. We have
now worked around the problem by taking the code out of a transaction
entirely and building to a temp table. We then serialise the SP at top
and bottom by means of an exclusive lock on the permanent table we are
rebuilding. This ensures that any caller of the SP will not regain
control until the work table it rebuilds is either verified up-to-date
or has been rebuilt. The downside is that in rare circumstances, two
simultaneous callers will both rebuilt temp tables, and then one of
these tasks will discover that the permanent work table was rebuilt
and discard their work. This will occur very rarely and is an
acceptable tradeoff.
So, the current hypothesis for this bug is that, within an SP
(a) Begin a transaction
(b) Perform an insert operation on empty table t which populates a
reasonable rowcount - say, 40,000 rows (note that in our tests, table
t had a PK but no other indexes or constraints, e.g foreign keys etc -
nor are identity columns involved)
(c) Within the same transaction, perform a second insert operation on
table t which involves a subquery or self join involving table t (plus
at least one other table), sufficiently complex to require the query
analyser to dynamically create a temp table as part of the execution
plan.
We believe that the SP will 'lock up' at step (c) and that this is
highly likely to be a catalogue serialisation issue, rather than
something like page lock or table lock collision.
If I get time, I will attempt to produce a canonical test case.
Andrew Mayo (aj****@my-deja.com) writes: (a) Begin a transaction
(b) Perform an insert operation on empty table t which populates a
reasonable rowcount - say, 40,000 rows (note that in our tests, table
t had a PK but no other indexes or constraints, e.g foreign keys etc -
nor are identity columns involved)
(c) Within the same transaction, perform a second insert operation on
table t which involves a subquery or self join involving table t (plus
at least one other table), sufficiently complex to require the query
analyser to dynamically create a temp table as part of the execution
plan.
All I can say is that this sounds highly unlikely.
As for the loopback possibility, it occurred to me that there are a
few more possibilies where this could creep in. You mentioned that the
queries were complex, and if they refer to views or functions the loopback
maybe hidden. Here are a couple a ways that you could haev a loopback:
* A function that calls xp_cmdshell to do some dynamic SQL. (Very poor
programming practice!)
* A function or a view that uses OPENQUERY to receive the result set
from a stored procedure. (Not very good programming practice either.)
* Your query accesses views on a remote server, which in their turn
refer back to tables on your own server.
I will have to admit that it would be very interesting to see the output
from a procedure like aba_lockinfo when you have this situation.
I would say that my main hypothesis is that the query simply takes a lot
time. But then again, I have not been at the site.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Erland Sommarskog <es****@sommars kog.se> wrote in message news:<Xn******* **************@ 127.0.0.1>... All I can say is that this sounds highly unlikely.
As for the loopback possibility, it occurred to me that there are a
few more possibilies where this could creep in. You mentioned that the
queries were complex, and if they refer to views or functions the loopback
maybe hidden. Here are a couple a ways that you could haev a loopback:
* A function that calls xp_cmdshell to do some dynamic SQL. (Very poor
programming practice!)
* A function or a view that uses OPENQUERY to receive the result set
from a stored procedure. (Not very good programming practice either.)
* Your query accesses views on a remote server, which in their turn
refer back to tables on your own server.
Well, although the data and tables are more complex than my simplified
example, they are not significantly more complex, and they do not do
any of the above. Also, if the queries are done out of a transaction
they complete in about 10 seconds and if done in the transaction, they
never complete.
However, I'm trying to create a canonical test case, so far
unsuccessfully, to pin down what the heck is going on, and will repost
if and when I can do this.
I am probably misunderstandin g/oversimplifying your scenario, however it
seems to me that
1) You do these two queries in a transaction
2) The insert table is empty, and you load all rows with the first
ftransaction - this will put write locks on all pages on the table
(making it unable to be read until the transaction doing the insert is
closed)
3) You then try to join to the table which is not possible because it is
locked by the previous insert
4) This appears to be confirmed by the fact that running the query in a 2nd
transaction works fine
5) If this is correct - then testing the transaction allowing dirty reads
(READ UNCOMMITTED) should allow it to work
If this is correct then you have a case of blocking locks, which is
normal/correct behaviour (though not obvious in this case)
Julian 8^)
"Andrew Mayo" <aj****@my-deja.com> wrote in message
news:2b******** *************** ***@posting.goo gle.com... Here's a really weird one for any SQL Server gurus out there...
We have observed (SQL Server 2000) scenarios where a stored procedure
which
(a) begins a transaction
(b) inserts some rows into a table
(c) re-queries another table using a subquery which references the
inserted table (correlated or not)
will deadlock itself at point (c), even when it is the only task on
the server.
[I use the term 'deadlock' because I can't think of anything else
appropriate, even though, as stated, this is the ONLY task executing,
so this isn't a classical deadlock situation]
A typical deadlocking scenario would be (assume insert_table is the
table into which some rows are being inserted)
begin transaction
insert insert_table(co l....) select (col....) from some_other_tabl e
/* this following query will deadlock and never complete */
select some_other_tabl e.col
from some_other_tabl e
where not exists (select *
from insert_table
where some_other_tabl e.col = insert_table.co l )
Whereas if the offending second query in the sequence is rewritten as
a join
e.g
select some_other_tabl e.col
from some_other_tabl e
left join insert_table
on some_other_tabl e.col = insert_table.co l
where insert_table.co l is null
the query will not deadlock.
If the subquery is an aggregate function, a deadlock will also not
occur.
If the transaction is committed prior to executing the blocking query,
then it will not block (hardly surprising; if it did, there'd be major
problems with SQL Server!).
Note that this is a canonical case of a much more complex SP, and that
simplified test cases often will not deadlock; you need a significant
amount of data, typically 30,000 rows or more to see the problem. The
blocking query is, in real life, used to drive a subsequent table
insert operation, but this is not relevant to the problem.
We conclude that there is some problem, possibly involving catalogue
contention, if a temporary table must be created in a subquery within
a transaction in a stored procedure, and if the subquery involves
references to a table for which locks have been acquired.
Note that the lock timeout will never trigger and a deadlock victim is
never chosen, presumably because the deadlock occurs entirely within
the same SPID.
Locking hints and transaction isolation level setting does not affect
the result. Note also that the exact same queries, executed as a TSQL
batch, do not deadlock; you must place them in a stored procedure.
Recovery mode for the database is SIMPLE, and the problem is portable
across databases; it can also be exhibited with MSDE/2000, and is
independent of whether or not the database server is the local machine
or not.
Has anyone else experienced this problem and/or know of a workaround,
other than those mentioned here?. It does look awfully like a bug with
SQL Server, since a single task should never be able to deadlock
itself, surely.
"julian_mui r" <ju*********@ya hho.com> wrote in message news:<42******* *************** *@ptn-nntp-reader01.plus.n et>... I am probably misunderstandin g/oversimplifying your scenario, however it
seems to me that
[snip] 5) If this is correct - then testing the transaction allowing dirty reads
(READ UNCOMMITTED) should allow it to work
If this is correct then you have a case of blocking locks, which is
normal/correct behaviour (though not obvious in this case)
I did consider this possibility. However, setting isolation level
hinting in either or both SQL statements did not change things.
I still strongly suspect catalogue contention, I have to say.
Certainly, the first insert statement will acquire a bunch of write
locks. Possibly these will even be escalated to a table-level lock. In
fact,you can explicitly specify such a lock hint without changing the
behaviour of the SP.(i.e it will still hang up)
Now the important point that maybe I didn't stress significantly
enough here is that this behaviour ONLY occurs in a STORED PROCEDURE.
If it were related to deadlocking issues, there is no reason why it
would be different if you executed it as part of a single TSQL batch,
within a transaction, since the exact same locks would be acquired.
Also, we should bear in mind that the locks you are acquiring are YOUR
OWN locks. Because of this, you should never be blocked - and, in
fact, you can do a transactional insert, then, without committing, do
a SELECT on the table you just inserted into quite successfully and
without blocking, as you might expect.
(whether you actually see anything in the select will of course depend
on whether you want committed reads or dirty reads, for example).
After all, if it didn't work that way, you'd begin a transaction and
then get blocked on your own locks - so, for example, two singleton
updates on the same row within a single transaction would fail because
the first update would acquire a write lock and by this theory, the
second update should be blocked by it.
Of course, this does not happen because, as I said, they are YOUR
locks and YOU are not blocked by them, only other processes. You can
do what you like; its YOUR transaction, and in this test scenario
there are no other database users to complicate matters.
Further testing currently confirms my working hypothesis; namely, that
a whole bunch of things need to happen
(a) The base table into which inserts are made needs to be truncated
prior to executing the query
(b) I *think* the query optimiser must then make a decision to
recompile the SP (or at least rebuild the execution plan) based on a
statistics change (need to check this area)
(c) The subquery or self-join in the second statement (that blocks)
must be sufficiently complex to require a temporary table to be
created in order to execute the query plan. A really simple self-join,
for instance, makes the problem go away, but a more complex join will
promptly bring it back.
As soon as I can produce a simple reproducible example I will post it.
Andrew Mayo (aj****@my-deja.com) writes: I still strongly suspect catalogue contention, I have to say.
And I still think this sounds unlikely.
Now the important point that maybe I didn't stress significantly
enough here is that this behaviour ONLY occurs in a STORED PROCEDURE.
If it were related to deadlocking issues, there is no reason why it
would be different if you executed it as part of a single TSQL batch,
within a transaction, since the exact same locks would be acquired.
For various reasons you can get different query plans when you use
a stored procedure and when you have a loose batch. The two chief
reasons for these possibilities:
1) Parameter sniffing. (If you SP has parameters the values of these
can affect the query plan.)
2) Different settings. (Recall that some settings are saved with the
stored procedure.)
As soon as I can produce a simple reproducible example I will post it.
Once you have it, I would be very interested in seeing it. However, I
fear that it might be a time-consuming task to construct a repro. And, as
I said before, it would be interesting to see lock and process information
from this situation.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Jarrod Morrison |
last post by:
Hi all
Im relatively new to using stored procedures and im not sure if it is
possible to do what I am trying to do so any help here is greatly
appreciated. I am using the variable @MachineName which is obviously the
local machine name mainly in this procedure. What is loop through from the
first character of the variable to the last and use this data in a select
statement. I have included the code below for what I have tried so far but I...
|
by: serge |
last post by:
I have an SP that is big, huge, 700-800 lines.
I am not an expert but I need to figure out every possible way that
I can improve the performance speed of this SP.
In the next couple of weeks I will work on preparing SQL statements
that will create the tables, insert sample record and run the SP.
I would hope people will look at my SP and give me any hints on how
I can better write the SP.
|
by: John |
last post by:
Hi everyone, I have a stored procedure which I use to query a table.
The first part of the stored procedure uses a cursor to update a temp table
whilst the second part of the query actually retrieves information from a
database table utilising information based on the temp table.
My problem is that when I run the procedure, the cursors status is output
and therefore becomes part of the result set. I really only want the
information...
|
by: Amy |
last post by:
I'm having trouble getting some data into a datagrid. It works fine
for the results of the first SELECT in the stored procedure, but not
therafter.
Here's the error I get:
"A field or property with the name 'contactUID' was not found on the
selected datasource"
Relevant code below - any help woudl be appreciated.
I have this sql stored procedure:
|
by: Jonathan Amsterdam |
last post by:
I think there's a slight design flaw in the Queue class that makes it
hard to avoid nested monitor deadlock. The problem is that the mutex
used by the Queue is not easy to change. You can then easily get
yourself into the following situation (nested monitor deadlock):
Say we have a class that contains a Queue and some other things. The
class's internals are protected by a mutex M. Initially the Queue is
empty. The only access to the...
| | |
by: gmadhava |
last post by:
Hi Everybody,
I am trying to update a column Percentage in a table named Critical Doctors with the a column named PercentTime from tblPercent table, Where the column Doctor matches with any DoctorId from tblPercent.
I am getting an error message for the following query.
update CriticalDoctors set Percentage =
(select PercentTime from tblPercent)
where CriticalDoctors.Doctor = (select DoctorId from tblPercent)
|
by: byahne |
last post by:
We just went live today with a production SQL Server 2005 database
running with our custom Java application. We are utilizing the jTDS
open source driver. We migrated our existing application which was
using InterBase over to SQL Server. To minimize the impact to our
code, we created a stored procedure which would allow us to manage our
primary key IDs (mimicing the InterBase Generator construct). Now
that we have 150+ users in the...
|
by: bbawa1 |
last post by:
I have the following stroed procedue. But whebnnI execute it it gives
me following errors.
Could you please tell me what is wrong
Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 43
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 47
Incorrect syntax near the keyword 'select'.
Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 52
Incorrect syntax near the keyword...
|
by: william.david.anderson |
last post by:
Hi there,
I have a newbie question regarding stored procedures and locking.
I'm trying to use a stored
procedure to perform a 'select for update' and return a cursor.
Below is a stripped down version
of the procedure:
CREATE PROCEDURE SELBTFLFORUPDATE()
LANGUAGE SQL
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
