Deadlocks workaround?

Hi All,

I have read about deadlocks here on Google and I was surprised to read
that an update and a select on the same table could get into a
deadlock because of the table's index. The update and the select
access the index in opposite orders, thereby causing the deadlock.
This sounds to me as a bug in SQL Server!

My question is: Could you avoid this by reading the table with a
'select * from X(updlock)' before updating it? I mean: Would this
result in the update transaction setting a lock on the index rows
before accessing the data rows?

Merry Christmas!
/Fredrik Mllerlouis nguyen (louisducnguyen@.hotmail.com) writes:
> In the example you posted, I typically use a "set transaction" option.
> My understanding is that this would prevent all shared locks. What
> is your opinion (pros/cons) of this? Thanks, Louis.
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> BEGIN TRANSACTION
> SELECT @.id = coalesce(MAX(id), 0) + 1 FROM tbl
> INSERT tbl (id, ...) VALUES (@.id, ...)
> COMMIT TRANSACTION

This is very likely to cause deadlocks. The isolation level does not
affect the ability to get shared locks. It only affects what you can
see if you issue the same statement later in the query.

Try this:

CREATE TABLE tbl (id int NOT NULL)
go
DECLARE @.id int
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @.id = coalesce(MAX(id), 0) + 1 FROM tbl
WAITFOR DELAY '00:00:10'
INSERT tbl (id) VALUES (@.id)
COMMIT TRANSACTION

First create the table, then run the batch from two windows. You will
get a deadlock. Add "WITH (UPDLOCK)" after the table, and both
batches will succeed.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,

Wow. I learned my lesson. Thanks, Louis.|||Hi Erland and Louis,
The example you provided was very enlightening in showing the
difference between UPDLOCK and HOLDLOCK/SERIALIZABLE. Thank you!
As to the locking of index and data rows I might come back later with
an example illustrating the problem.
Regards
Fredriksql

Deadlocks on SELECT statements?

Can anyone explain to me, even hypothetically, how 2 SELECT statements on th
e
same table can cause a deadlock?
The output to trace flag 1204 indicates that deadlocking in occuring on 2
statments that look like this:
select count(*) from table_1 where ...
The where criteria is different for the 2 SELECTS.
Thanks in advance for any ideas or guesses!!
apfWhat isolation level are you using?
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:3E1F69F9-C6C7-4F61-A767-4BED27D3B26E@.microsoft.com...
> Can anyone explain to me, even hypothetically, how 2 SELECT statements on
> the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf|||The default - Read Committed|||Are you on the latest service pack? Any chance this is the cause:
http://support.microsoft.com/kb/293232/EN-US/
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:C9FC427B-D02E-4E08-AE5A-39DDD64166BB@.microsoft.com...
> The default - Read Committed|||Can you post the deadlock trace?
"apf" wrote:

> Can anyone explain to me, even hypothetically, how 2 SELECT statements on
the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf|||> Can anyone explain to me, even hypothetically, how 2 SELECT statements > o
n the same table can cause a deadlock?
depends on the isolation level
here you go, in QA run this:
create table a(m int, n int)
create unique clustered index au on a(m)
insert into a
select 1,2
union all
select 2,2
union all
select 3,1
go
begin transaction
select * from a with(updlock) where m=1
open another QA window and run
begin transaction
select * from a with(updlock) where m=3
return to window 1 and run
select * from a with(updlock) where m=3
return to window 3 and run
select * from a with(updlock) where m=1
wait a little bit and here you go
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 58) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.|||If you can also post the two actual select statements causing the deadlocks
.
Are there any indexes supporting the where clause and if so how selective ar
e
they?
"FredG" wrote:
[vbcol=seagreen]
> Can you post the deadlock trace?
>
> "apf" wrote:
>

Deadlocks on SELECT statements?

Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
same table can cause a deadlock?
The output to trace flag 1204 indicates that deadlocking in occuring on 2
statments that look like this:
select count(*) from table_1 where ...
The where criteria is different for the 2 SELECTS.
Thanks in advance for any ideas or guesses!!
apf
What isolation level are you using?
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:3E1F69F9-C6C7-4F61-A767-4BED27D3B26E@.microsoft.com...
> Can anyone explain to me, even hypothetically, how 2 SELECT statements on
> the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf
|||The default - Read Committed
|||Are you on the latest service pack? Any chance this is the cause:
http://support.microsoft.com/kb/293232/EN-US/
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:C9FC427B-D02E-4E08-AE5A-39DDD64166BB@.microsoft.com...
> The default - Read Committed
|||Can you post the deadlock trace?
"apf" wrote:

> Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf
|||> Can anyone explain to me, even hypothetically, how 2 SELECT statements > on the same table can cause a deadlock?
depends on the isolation level
here you go, in QA run this:
create table a(m int, n int)
create unique clustered index au on a(m)
insert into a
select 1,2
union all
select 2,2
union all
select 3,1
go
begin transaction
select * from a with(updlock) where m=1
open another QA window and run
begin transaction
select * from a with(updlock) where m=3
return to window 1 and run
select * from a with(updlock) where m=3
return to window 3 and run
select * from a with(updlock) where m=1
wait a little bit and here you go
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 58) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
|||If you can also post the two actual select statements causing the deadlocks.
Are there any indexes supporting the where clause and if so how selective are
they?
"FredG" wrote:
[vbcol=seagreen]
> Can you post the deadlock trace?
>
> "apf" wrote:

Deadlocks on SELECT statements?

Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
same table can cause a deadlock?
The output to trace flag 1204 indicates that deadlocking in occuring on 2
statments that look like this:
select count(*) from table_1 where ...
The where criteria is different for the 2 SELECTS.
Thanks in advance for any ideas or guesses!!
apfWhat isolation level are you using?
--
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:3E1F69F9-C6C7-4F61-A767-4BED27D3B26E@.microsoft.com...
> Can anyone explain to me, even hypothetically, how 2 SELECT statements on
> the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf|||The default - Read Committed|||Are you on the latest service pack? Any chance this is the cause:
http://support.microsoft.com/kb/293232/EN-US/
--
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:C9FC427B-D02E-4E08-AE5A-39DDD64166BB@.microsoft.com...
> The default - Read Committed|||Can you post the deadlock trace?
"apf" wrote:
> Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf|||> Can anyone explain to me, even hypothetically, how 2 SELECT statements > on the same table can cause a deadlock?
depends on the isolation level
here you go, in QA run this:
create table a(m int, n int)
create unique clustered index au on a(m)
insert into a
select 1,2
union all
select 2,2
union all
select 3,1
go
begin transaction
select * from a with(updlock) where m=1
open another QA window and run
begin transaction
select * from a with(updlock) where m=3
return to window 1 and run
select * from a with(updlock) where m=3
return to window 3 and run
select * from a with(updlock) where m=1
wait a little bit and here you go
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 58) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.|||If you can also post the two actual select statements causing the deadlocks.
Are there any indexes supporting the where clause and if so how selective are
they?
"FredG" wrote:
> Can you post the deadlock trace?
>
> "apf" wrote:
> > Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
> > same table can cause a deadlock?
> >
> > The output to trace flag 1204 indicates that deadlocking in occuring on 2
> > statments that look like this:
> >
> > select count(*) from table_1 where ...
> >
> > The where criteria is different for the 2 SELECTS.
> >
> > Thanks in advance for any ideas or guesses!!
> >
> > apf

Deadlock within select?

My SQL Server has kicked out a deadlocked process, which should only be
running a select statement, though there is another select on one of
the tables in the WHERE clause (see code below). Can anyone tell me
whether this is possible, or is it that my system, which is re-using
connections, is trying to complete an earlier statement? I've looked
through the system and think I'm committing all transactions.

The query I'm running (simplified, I don't use daft names like 'table1'
or 'date_col', honest :) is:

SELECT table1.*, table2.*, table3.*
FROM table1, table2, table3
WHERE table2.col1 = table1.col1 AND table3.col1 = table1.col2
AND table1.col3 = 'xyz'
AND (table1.date_col >= '2006-02-01' OR table1.date_col IN
(SELECT date_col FROM table1 WHERE (col4 = 'A' OR col4 = 'B') AND col3
= 'xyz'))
ORDER BY table1.date_col

Thanks

J(jw_guildford@.yahoo.co.uk) writes:
> My SQL Server has kicked out a deadlocked process, which should only be
> running a select statement, though there is another select on one of
> the tables in the WHERE clause (see code below). Can anyone tell me
> whether this is possible, or is it that my system, which is re-using
> connections, is trying to complete an earlier statement? I've looked
> through the system and think I'm committing all transactions.

Presumably, there was an insert/update/delete operation that your SELECT
clashed with.

Have you looked at the deadlock trace?

If you don't have deadlock trace enabled, open Enterprise Manager, and
edit the startup parameters to include "-T 1204 -T 3605", and restart
the server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland, I've added those parameters, I'm afraid I don't have a
way of reproducing it at the moment so I'll just have to wait until it
happens again.

J

Deadlock on SQL SELECT statement

I have inherited the maintenance of a product which includes the snipet
of code below. Every 10 seconds the code is executed. It is causing a
deadlock in some instances, but I am undable to reproduce the problem
on my machine. The "PC" table contains a list of PCs seen on a
network, so isn't very large. Since I dont have much background in
database programming, I was wondering if there is some simple answer to
the deadlock issue...but from reading on deadlocks, there rarely seems
to be a simple solution.
// ****************************************
// Find PCs to restart
CString strQuery;
strQuery.Format ("select _ID from PC where (_FLAGS & 4) > 0 and
_RESTART > %s and _RESTART <= %s", PrepareSQLDate((CTime)0),
PrepareSQLDate(CTime::GetCurrentTime()))
;
try
{
for (CRecordSet rs(this, strQuery); !rs.IsEOF() ; rs.MoveNext())
{
list.Add(rs.GetColInt(0));
}
rs.Close();
}
catch (CDBException * e)
{
HandleException (e, strQuery);
}
return list.GetCount();
// ****************************************
**
Thanks in advance.In message <1138983056.041276.84650@.g47g2000cwa.googlegroups.com>,
bigcoops@.hotmail.com writes
>network, so isn't very large. Since I dont have much background in
>database programming, I was wondering if there is some simple answer to
>the deadlock issue...but from reading on deadlocks, there rarely seems
>to be a simple solution.
You may want to give Thread Validator a whirl.
http://www.softwareverify.com
Stephen
--
Stephen Kellett
Object Media Limited http://www.objmedia.demon.co.uk/software.html
Computer Consultancy, Software Development
Windows C++, Java, Assembler, Performance Analysis, Troubleshooting|||Try this:
select _ID from PC WITH (NOLOCK) ... and so forth
HTH,
Tom Dacon
Dacon Software Consulting
<bigcoops@.hotmail.com> wrote in message
news:1138983056.041276.84650@.g47g2000cwa.googlegroups.com...
>I have inherited the maintenance of a product which includes the snipet
> of code below. Every 10 seconds the code is executed. It is causing a
> deadlock in some instances, but I am undable to reproduce the problem
> on my machine. The "PC" table contains a list of PCs seen on a
> network, so isn't very large. Since I dont have much background in
> database programming, I was wondering if there is some simple answer to
> the deadlock issue...but from reading on deadlocks, there rarely seems
> to be a simple solution.
> // ****************************************
> // Find PCs to restart
> CString strQuery;
> strQuery.Format ("select _ID from PC where (_FLAGS & 4) > 0 and
> _RESTART > %s and _RESTART <= %s", PrepareSQLDate((CTime)0),
> PrepareSQLDate(CTime::GetCurrentTime()))
;
> try
> {
> for (CRecordSet rs(this, strQuery); !rs.IsEOF() ; rs.MoveNext())
> {
> list.Add(rs.GetColInt(0));
> }
> rs.Close();
> }
> catch (CDBException * e)
> {
> HandleException (e, strQuery);
> }
> return list.GetCount();
> // ****************************************
**
> Thanks in advance.
>|||Doesn't NOLOCK have the potential of getting dirty data?
Since the 10 second timer is set after the code above is executed, is
it possible the CRecordSet::Close() method did not close properly and
is holding a lock on the table? So when the next timer goes off the
deadlock occurs.
Thanks,
bigcoops|||It appears that this is not the place where deadlocks are occurring.
There is another SELECT statement, "select _NAME from PC where _ID =
....", and I suspect all other statements accessing the PC table will
cause a deadlock. Has anyone seen a similar issue where access to a
table will cause a deadlock?|||In addition to the deadlocks, there are now "Timeout expired (S1T00)"
errors occuring, which is more than likely a releated issue.

Deadlock on communication buffer, Thread

In our environment, a large number of users are
simultaneously running same select query against database.
The query involves around 7 tables and one view that has
millions of rows. In summary, the query is resource
intensive. Sometimes this query fails with the error
message
"Your transaction has been chosen as a victim of deadlock
on Communication Buffer,Thread" .
My question is why should there be a deadlock involved in
a SELECT statement. Deadlock should happen in
update/insert statements that use transactions.
Any insights on this will be helpful.
Thanks.there is an article at BOL that can helpl with this issue.. TROUBLESHOOTING
DEADLOCKS
One thing is the communication buffer in your error message, the bol sugests
that this can be related to query paralelism
Try to start the DBCC TRACEON(1204) to isolate the deadlock cause.
HTH
--
Wandenkolk T. Neto
MCDBA , MCSE
Fundação Abrinq
www.fundabrinq.org.br
"Vinod" <vinoddua2000@.yahoo.com> escreveu na mensagem
news:01c301c3674e$6b278d10$a501280a@.phx.gbl...
> In our environment, a large number of users are
> simultaneously running same select query against database.
> The query involves around 7 tables and one view that has
> millions of rows. In summary, the query is resource
> intensive. Sometimes this query fails with the error
> message
> "Your transaction has been chosen as a victim of deadlock
> on Communication Buffer,Thread" .
> My question is why should there be a deadlock involved in
> a SELECT statement. Deadlock should happen in
> update/insert statements that use transactions.
> Any insights on this will be helpful.
> Thanks.

deadlock on a single Select not in a transaction

Hi,
We have a web application developped in asp.net (I think it's not relevant,
but well, it's so you know)... Yesterday, we received the following message
"Transaction (Process ID 69) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction. "
The thing is, the query was a simple select with inner joins between 3
tables (like select fields from table1 inner join table2... inner join
table3...). This command is not in a transaction, so the deadlock seems
impossible. And moreover, the deadlock occurs on the DataReader.Read() not
on the Command.ExecuteReader(...).
Can someone explain why the deadlock can have occured and what could be the
cause and solution to it? Can it be a bug in SQL Server or in the .net
framework (I really doubt about it)?
thanks
ThunderMusic
What you're seeing is not concerning a Transaction as you are imagining it.
In essence, every operation performed by SQL Server is a transaction.
However, a Transaction (capital T) is a grouping of transactions (or
operations) into a single atomic unit which either fails or succeeds as a
whole. In this case, the reference is to to a (small t) transaction, which
is the operation of your query.
The exception occurs when 2 transactions (or processes) are trying to access
the same database object (such as a row in a table) at the same time. Each
process tries to get a lock on the object, and only one can. The other is
therefore killed. Here's a good article about this, and how to deal with it.
Notice that the most common tactic is simply to try again:
http://www.sql-server-performance.com/deadlocks.asp
HTH,
Kevin Spencer
Microsoft MVP
Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries:
http://www.miradyne.net
"ThunderMusic" <NoSpAmdanlatathotmaildotcom@.NoSpAm.com> wrote in message
news:ewkig6PiHHA.2028@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We have a web application developped in asp.net (I think it's not
> relevant, but well, it's so you know)... Yesterday, we received the
> following message "Transaction (Process ID 69) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction. " The thing is, the query was a simple select with
> inner joins between 3 tables (like select fields from table1 inner join
> table2... inner join table3...). This command is not in a transaction, so
> the deadlock seems impossible. And moreover, the deadlock occurs on the
> DataReader.Read() not on the Command.ExecuteReader(...).
> Can someone explain why the deadlock can have occured and what could be
> the cause and solution to it? Can it be a bug in SQL Server or in the .net
> framework (I really doubt about it)?
> thanks
> ThunderMusic
>
|||You might enjoy reading the three articles starting at:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
The bottom line is that a select is a single statement transaction that can
hold locks, and a conflicting desire for locks is what causes deadlocks.
(The Deadly Embrace, where each process is holding something the other
process wants.)
RLF
"ThunderMusic" <NoSpAmdanlatathotmaildotcom@.NoSpAm.com> wrote in message
news:ewkig6PiHHA.2028@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We have a web application developped in asp.net (I think it's not
> relevant, but well, it's so you know)... Yesterday, we received the
> following message "Transaction (Process ID 69) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction. " The thing is, the query was a simple select with
> inner joins between 3 tables (like select fields from table1 inner join
> table2... inner join table3...). This command is not in a transaction, so
> the deadlock seems impossible. And moreover, the deadlock occurs on the
> DataReader.Read() not on the Command.ExecuteReader(...).
> Can someone explain why the deadlock can have occured and what could be
> the cause and solution to it? Can it be a bug in SQL Server or in the .net
> framework (I really doubt about it)?
> thanks
> ThunderMusic
>
|||Kevin Spencer wrote:
...
> The exception occurs when 2 transactions (or processes) are trying to access
> the same database object (such as a row in a table) at the same time. Each
> process tries to get a lock on the object, and only one can. The other is
> therefore killed. Here's a good article about this, and how to deal with it.
> Notice that the most common tactic is simply to try again:
...
It's bit simplified, what you describe is not a deadlock situation. One
transaction will happily wait for lock to be released. Typical deadlock
situation is:
Transaction1 holds lock on A
Transcation2 holds lock on B
Transaction1 wants lock on B
Transcation2 wants lock on A
This cannot be resolved by waiting, so one transaction has to be killed.
Just a clarification.
Regards,
Goran

Deadlock on a select query, possible?

I have a simple select query that selects data from a view. I consistently get a deadlock exception when running this query:

Server: Msg 1205, Level 13, State 2, Line 1
Transaction (Process ID #) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

The view is a simple select statement that has WITH (NOLOCK) as a hint on all tables. I thought I understand how deadlocks worked, two threads are holding a lock and request the other's item. How does a read uncommitted select statement participate in this?

If I look at the other processes in the current activity and run sql profiler, there is no other activity and no existing locks at the time the statement is run.

Can anyone explain this? Or should I bounce my server and hope it never happens again?

Thanks,

DaveAlthough the (NOLOCK) hint is puzzling, it is possible to deadlock on a single SELECT. I replicated this behavior about 12 years ago, but can't remember the specific scenario other than it was happening on a wide table (I think there was one row to a page, 2K pages) and the deadlock happened on the index.

To see where the deadlock is occuring, you may want to try:
Connection 1:
DBCC TraceOn(1204)
go

Begin Tran
Select foo From v_bar WITH (NOLOCK)
go

Connection 2 (immediately after executing 1)
Exec sp_lock
go

Check the errorlog to see the results of the trace and read up on Troubleshooting Deadlocks in SQL BOL.

Good luck.

deadlock between select (shared) and update (intent exclusive)

I regularly have deadlocks on my sql-server 2000. Using the 1204 trace
I got the following info about the problem:
Wait-for graph
Node:1
PAG: 7:1:251381 CleanCnt:2 Mode: S Flags: 0x2
Grant List 0::
Owner:0x2c959e00 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:61
ECID:3
Requested By:
ResType:LockOwner Stype:'OR' Mode: IX SPID:72 ECID:0 Ec0x50871568)
Value:0x76375e60 Cost0/5580)
Node:2
PAG: 7:1:230822 CleanCnt:2 Mode: IX Flags: 0x2
Grant List 3::
Owner:0x4bba49e0 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:72
ECID:0
SPID: 72 ECID: 0 Statement Type: INSERT Line #: 1
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:61 ECID:3 Ec0x2D8EA0C0)
Value:0x75df1780 Cost0/0)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:61 ECID:3 Ec0x2D8EA0C0)
Value:0x75df1780 Cost0/0)
As I understand it, one statement owns an IX-lock and requests another
one while another statement owns a shared-lock and requests another
one. I know, I should always access tables in the same order but it's
too late for this now.
How can I tell the select statement to read the last commited data and
not to lock anything? IMHO we did not give any lock-hints with our
statements so the default lock levels should be used. Does it make
sense that a select blocks an update?Update: it is not a select and an UPDATE but a select count and an
insert.|||
> As I understand it, one statement owns an IX-lock and requests another
> one while another statement owns a shared-lock and requests another
> one. I know, I should always access tables in the same order but it's
> too late for this now.
> How can I tell the select statement to read the last commited data and
> not to lock anything? IMHO we did not give any lock-hints with our
> statements so the default lock levels should be used. Does it make
> sense that a select blocks an update?
>
I do not pretend to understand your locking situation.
And although I thought in the past that a select should Not be partner
in a deadlock. This proved to be wrong.
My situation.
Update transaction (standard isolation), two updates on one single row.
The select was a very simple select which resulted in a single row of a
single table.
The combination could result in a deadlock.
The probable cause of 'my' problem.
Both updates used different where clauses, which resulted in the same row,
but
resulted in different locking situations.
In this situation you can NOT tel to read the last commited data, because
that is
locked at the moment. In SQL-server 2005 you can opt for snapshot isolation,
where the last commited data is read. So with snapshot isolation reads do
not block
write and writes do not block reads.
Be carefull with snapshot isolation because this does not implement
serializability.
Good luck with your situation,
If you have more information please post it here,
If you have more questions please post it here.
ben brugman|||mhuhn.de@.gmail.com,
This feature has been implemented in SQL Server 2005 (Snapshot Isolation).
If you are using 2000 and do not want to change the order in which you
access your tables, consider using a table_hint in your "select" statement,
specifically ROWLOCK based on the info you posted (the lock seems to be at
the page level). See BOL for more info.
AMB
"mhuhn.de@.gmail.com" wrote:

> Update: it is not a select and an UPDATE but a select count and an
> insert.
>|||Thanks for answering. Anyway, 2005 is not an option because our
solution is already used from lots of customers. Do you think a ROWLOCK
makes sense if I do a select count? If the where-clause in the select
count includes the updated row, I'll have the same problem, right?
Furthermore, it will slow down my selects!?|||As I wrote in my other mail :
I do not pretend to understand your locking situation.
But I doubt very much that a ROWLOCK in the select will
solve the problem. The select (without a rowlock) is allready
waiting for another process to finish, this waiting can (I think)
not be solved by using a ROWLOCK, the rowlock will
(probably) prevent the other process on locking on the read
process.
A (row)lock in the update might claim enough resources that
the select is not capable of applying a lock which can stop the
update. So the update can finish after which the select can finish.
ben brugman
<mhuhn.de@.gmail.com> wrote in message
news:1147960900.879319.169740@.j55g2000cwa.googlegroups.com...
> Thanks for answering. Anyway, 2005 is not an option because our
> solution is already used from lots of customers. Do you think a ROWLOCK
> makes sense if I do a select count? If the where-clause in the select
> count includes the updated row, I'll have the same problem, right?
> Furthermore, it will slow down my selects!?
>

Deadlock between a transactional request and a non transaction joi

Hello,
I have a deadlock situation that seems odd to me :
process A :
BEGIN TRANSACTION
process A :
SELECT MAX(ID_IDENTIFIANT) + 1 as MAX
FROM IDENTIFIANT
WITH (TABLOCKX, HOLDLOCK)
=> process A locks table IDENTIFIANT
process B :
exec sp_executesql
N'SELECT DISTINCT(SA.ID_SS_ALARME), SO.NOM_SOCIETE, S.NOM_SITE,
I.NOM_IDENTIFIANT, SA.BLOQUANTE, A.TYPE_ACTIVATION, SA.VAL_ACTIV_DATE,
SA.VAL_ACTIV_COMPTEUR, M.LIBELLE_MESSAGE, A.PERIODICITE
FROM ALARME A, IDENTIFIANT I, SITE S, COMPTEUR C, MESSAGE M, SOCIETE SO,
SOUS_ALARME SA
WHERE A.ID_ALARME = SA.ID_ALARME AND SA.ACTIVE = 1 AND I.ID_IDENTIFIANT =
A.ID_IDENTIFIANT AND S.ID_SITE = I.ID_SITE AND M.ID_MESSAGE = SA.ID_MESSAGE
AND S.ID_SOCIETE = SO.ID_SOCIETE AND (( A.TYPE_ACTIVATION = ''D'' AND
SA.VAL_ACTIV_DATE < @.dateCourante) OR (A.TYPE_ACTIVATION = ''C'' AND
C.ID_PRODUIT = A.ID_PRODUIT AND C.ID_IDENTIFIANT = A.ID_IDENTIFIANT AND
C.VALEUR_COMPTEUR>SA.VAL_ACTIV_COMPTEUR)) AND SO.NO_GROUPE = 1
ORDER BY SA.ID_SS_ALARME',N'@.dateCourante
datetime',@.dateCourante=''2007-05-24 17:50:01:593''
=> process B waits because table IDENTIFIANT is locked
process A:
exec sp_executesql
N'SELECT SITE.ID_SITE AS ID
FROM SITE
WITH (TABLOCKX, HOLDLOCK)
WHERE ID_SOCIETE = @.idSociete',N'@.idSociete int',@.idSociete=2
=> deadlock
It looks as if process B locked table SITE although no transaction is open
on process B.
I'm using SQLSERVER 2005 EXPRESS SP2.
Can anyone explain to me the raison of the deadlock situation ?
Olivier GIL
LAFON SA
On May 25, 1:09 pm, Olivier GIL <o...@.newsgroup.nospam> wrote:
> Hello,
> I have a deadlock situation that seems odd to me :
> process A :
> BEGIN TRANSACTION
> process A :
> SELECT MAX(ID_IDENTIFIANT) + 1 as MAX
> FROM IDENTIFIANT
> WITH (TABLOCKX, HOLDLOCK)
> => process A locks table IDENTIFIANT
> process B :
> exec sp_executesql
> N'SELECT DISTINCT(SA.ID_SS_ALARME), SO.NOM_SOCIETE, S.NOM_SITE,
> I.NOM_IDENTIFIANT, SA.BLOQUANTE, A.TYPE_ACTIVATION, SA.VAL_ACTIV_DATE,
> SA.VAL_ACTIV_COMPTEUR, M.LIBELLE_MESSAGE, A.PERIODICITE
> FROM ALARME A, IDENTIFIANT I, SITE S, COMPTEUR C, MESSAGE M, SOCIETE SO,
> SOUS_ALARME SA
> WHERE A.ID_ALARME = SA.ID_ALARME AND SA.ACTIVE = 1 AND I.ID_IDENTIFIANT =
> A.ID_IDENTIFIANT AND S.ID_SITE = I.ID_SITE AND M.ID_MESSAGE = SA.ID_MESSAGE
> AND S.ID_SOCIETE = SO.ID_SOCIETE AND (( A.TYPE_ACTIVATION = ''D'' AND
> SA.VAL_ACTIV_DATE < @.dateCourante) OR (A.TYPE_ACTIVATION = ''C'' AND
> C.ID_PRODUIT = A.ID_PRODUIT AND C.ID_IDENTIFIANT = A.ID_IDENTIFIANT AND
> C.VALEUR_COMPTEUR>SA.VAL_ACTIV_COMPTEUR)) AND SO.NO_GROUPE = 1
> ORDER BY SA.ID_SS_ALARME',N'@.dateCourante
> datetime',@.dateCourante=''2007-05-24 17:50:01:593''
> => process B waits because table IDENTIFIANT is locked
> process A:
> exec sp_executesql
> N'SELECT SITE.ID_SITE AS ID
> FROM SITE
> WITH (TABLOCKX, HOLDLOCK)
> WHERE ID_SOCIETE = @.idSociete',N'@.idSociete int',@.idSociete=2
> => deadlock
> It looks as if process B locked table SITE although no transaction is open
> on process B.
> I'm using SQLSERVER 2005 EXPRESS SP2.
> Can anyone explain to me the raison of the deadlock situation ?
> --
> Olivier GIL
> LAFON SA
My opinion is
First Process MAX is completed by Process A
Process B which is waiting for Process A acuquires Share lock on
Table IDENTIFIANT
Process B share lock and subsequent Process A lock on table is
incompatible ,
as Process A can not acquire XLOCK on a shared lock table
In process B try table IDENTIFIANT WITH (nolock ) hint
|||Hello,
Process B should not set a shared locked, because it has not opened any
transaction.
Olivier GIL
LAFON SA
"M A Srinivas" wrote:

> On May 25, 1:09 pm, Olivier GIL <o...@.newsgroup.nospam> wrote:
> My opinion is
> First Process MAX is completed by Process A
> Process B which is waiting for Process A acuquires Share lock on
> Table IDENTIFIANT
> Process B share lock and subsequent Process A lock on table is
> incompatible ,
> as Process A can not acquire XLOCK on a shared lock table
> In process B try table IDENTIFIANT WITH (nolock ) hint
>
|||On May 25, 2:19 pm, Olivier GIL <o...@.newsgroup.nospam> wrote:
> Hello,
> Process B should not set a shared locked, because it has not opened any
> transaction.
> --
> Olivier GIL
> LAFON SA
>
> "M A Srinivas" wrote:
>
>
>
>
>
>
> - Show quoted text -
Whether Process B in a transaction OR Not it will acquire SHARE lock
on a ROW/PAGE/TABLE .
|||Hi Olivier,
Per my analysis, in this case, a block may be caused but dead lock should
not be caused since when B get a shared lock, A cannot lock the table until
B finishes the query.
To track the root cause, I recommend that you enable the trace flags -T1204
and -T3605 to the startup parameters and then restart your SQL Server.
Once the issue reoccurs, please post the error logs here or mail it to me
(changliw_at_microsoft_dot_com) for further research.
If you have any other questions or concerns, please feel free to let me
know.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi Oliver,
Just a kind reminder that I have not received your response. Please feel
free to post back at your convenience if you need further assistance.
Have a great day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi Oliver,
Just a kind reminder that I have not received your response. Please feel
free to post back at your convenience if you need further assistance.
Have a great day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Deadlock between a transactional request and a non transaction joi

Hello,
I have a deadlock situation that seems odd to me :
process A :
BEGIN TRANSACTION
process A :
SELECT MAX(ID_IDENTIFIANT) + 1 as MAX
FROM IDENTIFIANT
WITH (TABLOCKX, HOLDLOCK)
=> process A locks table IDENTIFIANT
process B :
exec sp_executesql
N'SELECT DISTINCT(SA.ID_SS_ALARME), SO.NOM_SOCIETE, S.NOM_SITE,
I.NOM_IDENTIFIANT, SA.BLOQUANTE, A.TYPE_ACTIVATION, SA.VAL_ACTIV_DATE,
SA.VAL_ACTIV_COMPTEUR, M.LIBELLE_MESSAGE, A.PERIODICITE
FROM ALARME A, IDENTIFIANT I, SITE S, COMPTEUR C, MESSAGE M, SOCIETE SO,
SOUS_ALARME SA
WHERE A.ID_ALARME = SA.ID_ALARME AND SA.ACTIVE = 1 AND I.ID_IDENTIFIANT = A.ID_IDENTIFIANT AND S.ID_SITE = I.ID_SITE AND M.ID_MESSAGE = SA.ID_MESSAGE
AND S.ID_SOCIETE = SO.ID_SOCIETE AND (( A.TYPE_ACTIVATION = ''D'' AND
SA.VAL_ACTIV_DATE < @.dateCourante) OR (A.TYPE_ACTIVATION = ''C'' AND
C.ID_PRODUIT = A.ID_PRODUIT AND C.ID_IDENTIFIANT = A.ID_IDENTIFIANT AND
C.VALEUR_COMPTEUR>SA.VAL_ACTIV_COMPTEUR)) AND SO.NO_GROUPE = 1
ORDER BY SA.ID_SS_ALARME',N'@.dateCourante
datetime',@.dateCourante=''2007-05-24 17:50:01:593''
=> process B waits because table IDENTIFIANT is locked
process A:
exec sp_executesql
N'SELECT SITE.ID_SITE AS ID
FROM SITE
WITH (TABLOCKX, HOLDLOCK)
WHERE ID_SOCIETE = @.idSociete',N'@.idSociete int',@.idSociete=2
=> deadlock
It looks as if process B locked table SITE although no transaction is open
on process B.
I'm using SQLSERVER 2005 EXPRESS SP2.
Can anyone explain to me the raison of the deadlock situation ?
--
Olivier GIL
LAFON SAOn May 25, 1:09 pm, Olivier GIL <o...@.newsgroup.nospam> wrote:
> Hello,
> I have a deadlock situation that seems odd to me :
> process A :
> BEGIN TRANSACTION
> process A :
> SELECT MAX(ID_IDENTIFIANT) + 1 as MAX
> FROM IDENTIFIANT
> WITH (TABLOCKX, HOLDLOCK)
> => process A locks table IDENTIFIANT
> process B :
> exec sp_executesql
> N'SELECT DISTINCT(SA.ID_SS_ALARME), SO.NOM_SOCIETE, S.NOM_SITE,
> I.NOM_IDENTIFIANT, SA.BLOQUANTE, A.TYPE_ACTIVATION, SA.VAL_ACTIV_DATE,
> SA.VAL_ACTIV_COMPTEUR, M.LIBELLE_MESSAGE, A.PERIODICITE
> FROM ALARME A, IDENTIFIANT I, SITE S, COMPTEUR C, MESSAGE M, SOCIETE SO,
> SOUS_ALARME SA
> WHERE A.ID_ALARME = SA.ID_ALARME AND SA.ACTIVE = 1 AND I.ID_IDENTIFIANT => A.ID_IDENTIFIANT AND S.ID_SITE = I.ID_SITE AND M.ID_MESSAGE = SA.ID_MESSAGE
> AND S.ID_SOCIETE = SO.ID_SOCIETE AND (( A.TYPE_ACTIVATION = ''D'' AND
> SA.VAL_ACTIV_DATE < @.dateCourante) OR (A.TYPE_ACTIVATION = ''C'' AND
> C.ID_PRODUIT = A.ID_PRODUIT AND C.ID_IDENTIFIANT = A.ID_IDENTIFIANT AND
> C.VALEUR_COMPTEUR>SA.VAL_ACTIV_COMPTEUR)) AND SO.NO_GROUPE = 1
> ORDER BY SA.ID_SS_ALARME',N'@.dateCourante
> datetime',@.dateCourante=''2007-05-24 17:50:01:593''
> => process B waits because table IDENTIFIANT is locked
> process A:
> exec sp_executesql
> N'SELECT SITE.ID_SITE AS ID
> FROM SITE
> WITH (TABLOCKX, HOLDLOCK)
> WHERE ID_SOCIETE = @.idSociete',N'@.idSociete int',@.idSociete=2
> => deadlock
> It looks as if process B locked table SITE although no transaction is open
> on process B.
> I'm using SQLSERVER 2005 EXPRESS SP2.
> Can anyone explain to me the raison of the deadlock situation ?
> --
> Olivier GIL
> LAFON SA
My opinion is
First Process MAX is completed by Process A
Process B which is waiting for Process A acuquires Share lock on
Table IDENTIFIANT
Process B share lock and subsequent Process A lock on table is
incompatible ,
as Process A can not acquire XLOCK on a shared lock table
In process B try table IDENTIFIANT WITH (nolock ) hint|||Hello,
Process B should not set a shared locked, because it has not opened any
transaction.
--
Olivier GIL
LAFON SA
"M A Srinivas" wrote:
> On May 25, 1:09 pm, Olivier GIL <o...@.newsgroup.nospam> wrote:
> > Hello,
> >
> > I have a deadlock situation that seems odd to me :
> >
> > process A :
> > BEGIN TRANSACTION
> >
> > process A :
> > SELECT MAX(ID_IDENTIFIANT) + 1 as MAX
> > FROM IDENTIFIANT
> > WITH (TABLOCKX, HOLDLOCK)
> > => process A locks table IDENTIFIANT
> >
> > process B :
> > exec sp_executesql
> > N'SELECT DISTINCT(SA.ID_SS_ALARME), SO.NOM_SOCIETE, S.NOM_SITE,
> > I.NOM_IDENTIFIANT, SA.BLOQUANTE, A.TYPE_ACTIVATION, SA.VAL_ACTIV_DATE,
> > SA.VAL_ACTIV_COMPTEUR, M.LIBELLE_MESSAGE, A.PERIODICITE
> > FROM ALARME A, IDENTIFIANT I, SITE S, COMPTEUR C, MESSAGE M, SOCIETE SO,
> > SOUS_ALARME SA
> > WHERE A.ID_ALARME = SA.ID_ALARME AND SA.ACTIVE = 1 AND I.ID_IDENTIFIANT => > A.ID_IDENTIFIANT AND S.ID_SITE = I.ID_SITE AND M.ID_MESSAGE = SA.ID_MESSAGE
> > AND S.ID_SOCIETE = SO.ID_SOCIETE AND (( A.TYPE_ACTIVATION = ''D'' AND
> > SA.VAL_ACTIV_DATE < @.dateCourante) OR (A.TYPE_ACTIVATION = ''C'' AND
> > C.ID_PRODUIT = A.ID_PRODUIT AND C.ID_IDENTIFIANT = A.ID_IDENTIFIANT AND
> > C.VALEUR_COMPTEUR>SA.VAL_ACTIV_COMPTEUR)) AND SO.NO_GROUPE = 1
> > ORDER BY SA.ID_SS_ALARME',N'@.dateCourante
> > datetime',@.dateCourante=''2007-05-24 17:50:01:593''
> > => process B waits because table IDENTIFIANT is locked
> >
> > process A:
> > exec sp_executesql
> > N'SELECT SITE.ID_SITE AS ID
> > FROM SITE
> > WITH (TABLOCKX, HOLDLOCK)
> > WHERE ID_SOCIETE = @.idSociete',N'@.idSociete int',@.idSociete=2
> > => deadlock
> >
> > It looks as if process B locked table SITE although no transaction is open
> > on process B.
> >
> > I'm using SQLSERVER 2005 EXPRESS SP2.
> >
> > Can anyone explain to me the raison of the deadlock situation ?
> >
> > --
> > Olivier GIL
> > LAFON SA
> My opinion is
> First Process MAX is completed by Process A
> Process B which is waiting for Process A acuquires Share lock on
> Table IDENTIFIANT
> Process B share lock and subsequent Process A lock on table is
> incompatible ,
> as Process A can not acquire XLOCK on a shared lock table
> In process B try table IDENTIFIANT WITH (nolock ) hint
>|||On May 25, 2:19 pm, Olivier GIL <o...@.newsgroup.nospam> wrote:
> Hello,
> Process B should not set a shared locked, because it has not opened any
> transaction.
> --
> Olivier GIL
> LAFON SA
>
> "M A Srinivas" wrote:
> > On May 25, 1:09 pm, Olivier GIL <o...@.newsgroup.nospam> wrote:
> > > Hello,
> > > I have a deadlock situation that seems odd to me :
> > > process A :
> > > BEGIN TRANSACTION
> > > process A :
> > > SELECT MAX(ID_IDENTIFIANT) + 1 as MAX
> > > FROM IDENTIFIANT
> > > WITH (TABLOCKX, HOLDLOCK)
> > > => process A locks table IDENTIFIANT
> > > process B :
> > > exec sp_executesql
> > > N'SELECT DISTINCT(SA.ID_SS_ALARME), SO.NOM_SOCIETE, S.NOM_SITE,
> > > I.NOM_IDENTIFIANT, SA.BLOQUANTE, A.TYPE_ACTIVATION, SA.VAL_ACTIV_DATE,
> > > SA.VAL_ACTIV_COMPTEUR, M.LIBELLE_MESSAGE, A.PERIODICITE
> > > FROM ALARME A, IDENTIFIANT I, SITE S, COMPTEUR C, MESSAGE M, SOCIETE SO,
> > > SOUS_ALARME SA
> > > WHERE A.ID_ALARME = SA.ID_ALARME AND SA.ACTIVE = 1 AND I.ID_IDENTIFIANT => > > A.ID_IDENTIFIANT AND S.ID_SITE = I.ID_SITE AND M.ID_MESSAGE = SA.ID_MESSAGE
> > > AND S.ID_SOCIETE = SO.ID_SOCIETE AND (( A.TYPE_ACTIVATION = ''D'' AND
> > > SA.VAL_ACTIV_DATE < @.dateCourante) OR (A.TYPE_ACTIVATION = ''C'' AND
> > > C.ID_PRODUIT = A.ID_PRODUIT AND C.ID_IDENTIFIANT = A.ID_IDENTIFIANT AND
> > > C.VALEUR_COMPTEUR>SA.VAL_ACTIV_COMPTEUR)) AND SO.NO_GROUPE = 1
> > > ORDER BY SA.ID_SS_ALARME',N'@.dateCourante
> > > datetime',@.dateCourante=''2007-05-24 17:50:01:593''
> > > => process B waits because table IDENTIFIANT is locked
> > > process A:
> > > exec sp_executesql
> > > N'SELECT SITE.ID_SITE AS ID
> > > FROM SITE
> > > WITH (TABLOCKX, HOLDLOCK)
> > > WHERE ID_SOCIETE = @.idSociete',N'@.idSociete int',@.idSociete=2
> > > => deadlock
> > > It looks as if process B locked table SITE although no transaction is open
> > > on process B.
> > > I'm using SQLSERVER 2005 EXPRESS SP2.
> > > Can anyone explain to me the raison of the deadlock situation ?
> > > --
> > > Olivier GIL
> > > LAFON SA
> > My opinion is
> > First Process MAX is completed by Process A
> > Process B which is waiting for Process A acuquires Share lock on
> > Table IDENTIFIANT
> > Process B share lock and subsequent Process A lock on table is
> > incompatible ,
> > as Process A can not acquire XLOCK on a shared lock table
> > In process B try table IDENTIFIANT WITH (nolock ) hint- Hide quoted text -
> - Show quoted text -
Whether Process B in a transaction OR Not it will acquire SHARE lock
on a ROW/PAGE/TABLE .|||Hi Olivier,
Per my analysis, in this case, a block may be caused but dead lock should
not be caused since when B get a shared lock, A cannot lock the table until
B finishes the query.
To track the root cause, I recommend that you enable the trace flags -T1204
and -T3605 to the startup parameters and then restart your SQL Server.
Once the issue reoccurs, please post the error logs here or mail it to me
(changliw_at_microsoft_dot_com) for further research.
If you have any other questions or concerns, please feel free to let me
know.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Oliver,
Just a kind reminder that I have not received your response. Please feel
free to post back at your convenience if you need further assistance.
Have a great day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Oliver,
Just a kind reminder that I have not received your response. Please feel
free to post back at your convenience if you need further assistance.
Have a great day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Deadlock between a transactional request and a non transaction joi

Hello,
I have a deadlock situation that seems odd to me :
process A :
BEGIN TRANSACTION
process A :
SELECT MAX(ID_IDENTIFIANT) + 1 as MAX
FROM IDENTIFIANT
WITH (TABLOCKX, HOLDLOCK)
=> process A locks table IDENTIFIANT
process B :
exec sp_executesql
N'SELECT DISTINCT(SA.ID_SS_ALARME), SO.NOM_SOCIETE, S.NOM_SITE,
I.NOM_IDENTIFIANT, SA.BLOQUANTE, A.TYPE_ACTIVATION, SA.VAL_ACTIV_DATE,
SA.VAL_ACTIV_COMPTEUR, M.LIBELLE_MESSAGE, A.PERIODICITE
FROM ALARME A, IDENTIFIANT I, SITE S, COMPTEUR C, MESSAGE M, SOCIETE SO,
SOUS_ALARME SA
WHERE A.ID_ALARME = SA.ID_ALARME AND SA.ACTIVE = 1 AND I.ID_IDENTIFIANT =
A.ID_IDENTIFIANT AND S.ID_SITE = I.ID_SITE AND M.ID_MESSAGE = SA.ID_MESSAGE
AND S.ID_SOCIETE = SO.ID_SOCIETE AND (( A.TYPE_ACTIVATION = ''D'' AND
SA.VAL_ACTIV_DATE < @.dateCourante) OR (A.TYPE_ACTIVATION = ''C'' AND
C.ID_PRODUIT = A.ID_PRODUIT AND C.ID_IDENTIFIANT = A.ID_IDENTIFIANT AND
C.VALEUR_COMPTEUR>SA.VAL_ACTIV_COMPTEUR)) AND SO.NO_GROUPE = 1
ORDER BY SA.ID_SS_ALARME',N'@.dateCourante
datetime',@.dateCourante=''2007-05-24 17:50:01:593''
=> process B waits because table IDENTIFIANT is locked
process A:
exec sp_executesql
N'SELECT SITE.ID_SITE AS ID
FROM SITE
WITH (TABLOCKX, HOLDLOCK)
WHERE ID_SOCIETE = @.idSociete',N'@.idSociete int',@.idSociete=2
=> deadlock
It looks as if process B locked table SITE although no transaction is open
on process B.
I'm using SQLSERVER 2005 EXPRESS SP2.
Can anyone explain to me the raison of the deadlock situation ?
Olivier GIL
LAFON SAOn May 25, 1:09 pm, Olivier GIL <o...@.newsgroup.nospam> wrote:
> Hello,
> I have a deadlock situation that seems odd to me :
> process A :
> BEGIN TRANSACTION
> process A :
> SELECT MAX(ID_IDENTIFIANT) + 1 as MAX
> FROM IDENTIFIANT
> WITH (TABLOCKX, HOLDLOCK)
> => process A locks table IDENTIFIANT
> process B :
> exec sp_executesql
> N'SELECT DISTINCT(SA.ID_SS_ALARME), SO.NOM_SOCIETE, S.NOM_SITE,
> I.NOM_IDENTIFIANT, SA.BLOQUANTE, A.TYPE_ACTIVATION, SA.VAL_ACTIV_DATE,
> SA.VAL_ACTIV_COMPTEUR, M.LIBELLE_MESSAGE, A.PERIODICITE
> FROM ALARME A, IDENTIFIANT I, SITE S, COMPTEUR C, MESSAGE M, SOCIETE SO,
> SOUS_ALARME SA
> WHERE A.ID_ALARME = SA.ID_ALARME AND SA.ACTIVE = 1 AND I.ID_IDENTIFIANT =
> A.ID_IDENTIFIANT AND S.ID_SITE = I.ID_SITE AND M.ID_MESSAGE = SA.ID_MESSAG
E
> AND S.ID_SOCIETE = SO.ID_SOCIETE AND (( A.TYPE_ACTIVATION = ''D'' AND
> SA.VAL_ACTIV_DATE < @.dateCourante) OR (A.TYPE_ACTIVATION = ''C'' AND
> C.ID_PRODUIT = A.ID_PRODUIT AND C.ID_IDENTIFIANT = A.ID_IDENTIFIANT AND
> C.VALEUR_COMPTEUR>SA.VAL_ACTIV_COMPTEUR)) AND SO.NO_GROUPE = 1
> ORDER BY SA.ID_SS_ALARME',N'@.dateCourante
> datetime',@.dateCourante=''2007-05-24 17:50:01:593''
> => process B waits because table IDENTIFIANT is locked
> process A:
> exec sp_executesql
> N'SELECT SITE.ID_SITE AS ID
> FROM SITE
> WITH (TABLOCKX, HOLDLOCK)
> WHERE ID_SOCIETE = @.idSociete',N'@.idSociete int',@.idSociete=2
> => deadlock
> It looks as if process B locked table SITE although no transaction is open
> on process B.
> I'm using SQLSERVER 2005 EXPRESS SP2.
> Can anyone explain to me the raison of the deadlock situation ?
> --
> Olivier GIL
> LAFON SA
My opinion is
First Process MAX is completed by Process A
Process B which is waiting for Process A acuquires Share lock on
Table IDENTIFIANT
Process B share lock and subsequent Process A lock on table is
incompatible ,
as Process A can not acquire XLOCK on a shared lock table
In process B try table IDENTIFIANT WITH (nolock ) hint

DBPROCESS is dead

I have a user that is losing his database connection
after about 6 minutes or so with error:
Database error code: 10025
Database error message: Select error: Possible network
error: Write to SQL server failed. General network error.
The same user access another application on the same
server, different database, with no problems and the same
application used by another user who is having no
problems.
Any ideas what could be going on?
Thanks,
Angelina
Hi,
Execute the SQL Profiler for that user (set the filter) and
check what is happening.
This can be because of slow network as well (in his PC ).
Thanks
Hari
MCDBA
"Angelina" <anonymous@.discussions.microsoft.com> wrote in message
news:16d5701c448ab$4f0ee440$a601280a@.phx.gbl...
> I have a user that is losing his database connection
> after about 6 minutes or so with error:
> Database error code: 10025
> Database error message: Select error: Possible network
> error: Write to SQL server failed. General network error.
> The same user access another application on the same
> server, different database, with no problems and the same
> application used by another user who is having no
> problems.
> Any ideas what could be going on?
> Thanks,
> Angelina
|||Could be a firewall or VPN setting that automatically closes the connection
when it is idle. 300 seconds is a common default setting. I know that is
five minutes, but 1 minute of activity and 5 minutes of inactivity = 1 dead
connection.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Angelina" <anonymous@.discussions.microsoft.com> wrote in message
news:16d5701c448ab$4f0ee440$a601280a@.phx.gbl...
> I have a user that is losing his database connection
> after about 6 minutes or so with error:
> Database error code: 10025
> Database error message: Select error: Possible network
> error: Write to SQL server failed. General network error.
> The same user access another application on the same
> server, different database, with no problems and the same
> application used by another user who is having no
> problems.
> Any ideas what could be going on?
> Thanks,
> Angelina

DBPROCESS is dead

I have a user that is losing his database connection
after about 6 minutes or so with error:
Database error code: 10025
Database error message: Select error: Possible network
error: Write to SQL server failed. General network error.
The same user access another application on the same
server, different database, with no problems and the same
application used by another user who is having no
problems.
Any ideas what could be going on'
Thanks,
AngelinaHi,
Execute the SQL Profiler for that user (set the filter) and
check what is happening.
This can be because of slow network as well (in his PC ).
Thanks
Hari
MCDBA
"Angelina" <anonymous@.discussions.microsoft.com> wrote in message
news:16d5701c448ab$4f0ee440$a601280a@.phx
.gbl...
> I have a user that is losing his database connection
> after about 6 minutes or so with error:
> Database error code: 10025
> Database error message: Select error: Possible network
> error: Write to SQL server failed. General network error.
> The same user access another application on the same
> server, different database, with no problems and the same
> application used by another user who is having no
> problems.
> Any ideas what could be going on'
> Thanks,
> Angelina|||Could be a firewall or VPN setting that automatically closes the connection
when it is idle. 300 seconds is a common default setting. I know that is
five minutes, but 1 minute of activity and 5 minutes of inactivity = 1 dead
connection.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Angelina" <anonymous@.discussions.microsoft.com> wrote in message
news:16d5701c448ab$4f0ee440$a601280a@.phx
.gbl...
> I have a user that is losing his database connection
> after about 6 minutes or so with error:
> Database error code: 10025
> Database error message: Select error: Possible network
> error: Write to SQL server failed. General network error.
> The same user access another application on the same
> server, different database, with no problems and the same
> application used by another user who is having no
> problems.
> Any ideas what could be going on'
> Thanks,
> Angelina

dbo prefix

When I try to do a remote query as follow
select * from server1.retail..customers
the query fails, but if ran the same query with dbo
select * from server1.retail.dbo.customers, then it works.
Why is this happening, I thought dbo. or .. was the same.
Thanks in advanceTom,
>> Why is this happening, I thought dbo. or .. was the same.
No.If you login to SQL Server using the login 'tom' and then you say select
* from server1.retail..customers, it looks for 'customers' which is created
by the objectowner 'tom'.Normally, all database objects should be created by
'dbo' in order to avoid this confusion.Even otherwise, its a good practice
to prefix the objectowner name explicitly as in select * from dbo.customers.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"tom" <tom@.hotmail.com> wrote in message
news:0d9401c36e47$15161d10$a501280a@.phx.gbl...
> When I try to do a remote query as follow
> select * from server1.retail..customers
> the query fails, but if ran the same query with dbo
> select * from server1.retail.dbo.customers, then it works.
> Why is this happening, I thought dbo. or .. was the same.
> Thanks in advance
>|||I am sa on the server and all the objects are owned by dbo.
thanks for you help
>--Original Message--
>Tom,
>> Why is this happening, I thought dbo. or .. was the
same.
>No.If you login to SQL Server using the login 'tom' and
then you say select
>* from server1.retail..customers, it looks
for 'customers' which is created
>by the objectowner 'tom'.Normally, all database objects
should be created by
>'dbo' in order to avoid this confusion.Even otherwise,
its a good practice
>to prefix the objectowner name explicitly as in select *
from dbo.customers.
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"tom" <tom@.hotmail.com> wrote in message
>news:0d9401c36e47$15161d10$a501280a@.phx.gbl...
>> When I try to do a remote query as follow
>> select * from server1.retail..customers
>> the query fails, but if ran the same query with dbo
>> select * from server1.retail.dbo.customers, then it
works.
>> Why is this happening, I thought dbo. or .. was the
same.
>> Thanks in advance
>>
>
>.
>|||Tom,
Its because you are querying a remote server.Always use fully qualified
names when working with objects on linked servers including the object owner
name,in this case, 'dbo'.In linked servers, there is no support for implicit
resolution of .. to the dbo owner name for tables .
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"tom" <tom@.hotmail.com> wrote in message
news:011e01c36e49$c1123610$a301280a@.phx.gbl...
> I am sa on the server and all the objects are owned by dbo.
> thanks for you help
>
> >--Original Message--
> >Tom,
> >
> >> Why is this happening, I thought dbo. or .. was the
> same.
> >
> >No.If you login to SQL Server using the login 'tom' and
> then you say select
> >* from server1.retail..customers, it looks
> for 'customers' which is created
> >by the objectowner 'tom'.Normally, all database objects
> should be created by
> >'dbo' in order to avoid this confusion.Even otherwise,
> its a good practice
> >to prefix the objectowner name explicitly as in select *
> from dbo.customers.
> >
> >--
> >Dinesh.
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"tom" <tom@.hotmail.com> wrote in message
> >news:0d9401c36e47$15161d10$a501280a@.phx.gbl...
> >> When I try to do a remote query as follow
> >>
> >> select * from server1.retail..customers
> >>
> >> the query fails, but if ran the same query with dbo
> >>
> >> select * from server1.retail.dbo.customers, then it
> works.
> >>
> >> Why is this happening, I thought dbo. or .. was the
> same.
> >>
> >> Thanks in advance
> >>
> >>
> >
> >
> >.
> >

dbmssocn bug?

sometimes, if I connect DNS name for localhost running sql2000, (go to the
router and back) at a simple "SELECT * FROM table" statament I get a long
delay (application hangs for 10-20 second), and drops the connection.
When I change the datasource to "netbios" name, the same statament works
without trouble.
where is the bug? In dbmssocn? in ADO events (I use them...)?
cursorlocations? Why only sometimes?
What did you mean use DNS name and NetBIOS name ?
Are you connecting from a client PC? Does it work with Query Analyser ? Does
it timeout all the time.
"SRINGER Zoltn" <kecskemetisrac@.free-mail.hu> wrote in message
news:O7hVZOiBFHA.3840@.tk2msftngp13.phx.gbl...
> sometimes, if I connect DNS name for localhost running sql2000, (go to the
> router and back) at a simple "SELECT * FROM table" statament I get a long
> delay (application hangs for 10-20 second), and drops the connection.
> When I change the datasource to "netbios" name, the same statament works
> without trouble.
> where is the bug? In dbmssocn? in ADO events (I use them...)?
> cursorlocations? Why only sometimes?
>
|||another relevant information:
if I change cursorlocation to adUseClient, works...
... but I need serverside cursor especially via internet!
|||From the earlier posts, it sounds like you are could just
have some DNS issues in your network. Have you checked the
event logs for such? Have you tried an alias using the IP
address?
-Sue
On Mon, 31 Jan 2005 16:13:33 +0100, "SRINGER Zoltn"
<kecskemetisrac@.free-mail.hu> wrote:

>another relevant information:
>if I change cursorlocation to adUseClient, works...
>
>... but I need serverside cursor especially via internet!
>

dbmssocn bug?

sometimes, if I connect DNS name for localhost running sql2000, (go to the
router and back) at a simple "SELECT * FROM table" statament I get a long
delay (application hangs for 10-20 second), and drops the connection.
When I change the datasource to "netbios" name, the same statament works
without trouble.
where is the bug? In dbmssocn? in ADO events (I use them...)?
cursorlocations? Why only sometimes?What did you mean use DNS name and NetBIOS name ?
Are you connecting from a client PC? Does it work with Query Analyser ? Does
it timeout all the time.
"SRINGER Zoltn" <kecskemetisrac@.free-mail.hu> wrote in message
news:O7hVZOiBFHA.3840@.tk2msftngp13.phx.gbl...
> sometimes, if I connect DNS name for localhost running sql2000, (go to the
> router and back) at a simple "SELECT * FROM table" statament I get a long
> delay (application hangs for 10-20 second), and drops the connection.
> When I change the datasource to "netbios" name, the same statament works
> without trouble.
> where is the bug? In dbmssocn? in ADO events (I use them...)?
> cursorlocations? Why only sometimes?
>|||another relevant information:
if I change cursorlocation to adUseClient, works...
... but I need serverside cursor especially via internet!|||From the earlier posts, it sounds like you are could just
have some DNS issues in your network. Have you checked the
event logs for such? Have you tried an alias using the IP
address?
-Sue
On Mon, 31 Jan 2005 16:13:33 +0100, "SRINGER Zoltn"
<kecskemetisrac@.free-mail.hu> wrote:

>another relevant information:
>if I change cursorlocation to adUseClient, works...
>
>... but I need serverside cursor especially via internet!
>

DBMail

Is it possible to embed a image datatype into a EMail message using sp_Send_DBMail?

For example, my query would select a saved print screen image held in a SQL table as datatype image. I would prefer not to attach this image but rather have it print in the message section.

Thanks in advance.Hopefully you mean xp_sendmail

Sends a message and a query result set attachment to the specified recipients.

Syntax
xp_sendmail {[@.recipients =] 'recipients [;...n]'}
[,[@.message =] 'message']
[,[@.query =] 'query']
[,[@.attachments =] 'attachments [;...n]']
[,[@.copy_recipients =] 'copy_recipients [;...n]'
[,[@.blind_copy_recipients =] 'blind_copy_recipients [;...n]'
[,[@.subject =] 'subject']
[,[@.type =] 'type']
[,[@.attach_results =] 'attach_value']
[,[@.no_output =] 'output_value']
[,[@.no_header =] 'header_value']
[,[@.width =] width]
[,[@.separator =] 'separator']
[,[@.echo_error =] 'echo_value']
[,[@.set_user =] 'user']
[,[@.dbuse =] 'database']

U Should be able to
[,[@.attachments =] 'attachments [;...n]']
easy enough

But to embed it as a BLOB into emails & not as an attachment is an interesting yet Scary Question

I Hope U are'nt a Spammer !!

lol

GW|||Why not use VB or ASP in this case to build the case and insert the messages in that form. You can blend sendmail with those programs too.