Deafult NULL not working

I am using SQL Server 2000. I have a Column with DataType int and default value specified as (null). But, With Insert or Update if the column value is Blank, 0 is getting inserted instead of the desired NULL.

Thanks

Is there, by chance, a trigger on this table?|||

NO. There is no trigger on this Table.

|||Well... create a complete DDL script for this table and post it here - something must be there.

Also, how you make a insert / update - directly or via some kind of stored proc? There may be a preprocessing in there that you miss, for example.|||

What exactly do you mean by "column value is blank". If you are explicitly trying to force a blank into the field then yes, it is going to get assigned as zero:

create table dbo.testo
( rid int,
x int default (null)
)

insert into dbo.testo select 1, ' '

insert into dbo.testo (rid) select 1

select * from dbo.testo

/*
rid x
-- --
1 0
1 NULL
*/

If, however, you are wanting to insert a row and allow the default to occur you must do something similar to what I hilighted in red

If you want to UPDATE to the default value, you can use syntax something like this:

update dbo.testO
set x=default
where rid =1

|||

YES. The Column Value is getting evaluated to '' as the user did not enter anything for the field on the form. Is there any way '' can be evaluated to NULL instead of 0.

- vmrao

|||declare @.p1 varchar(255)

set @.p1 = ''

insert into Mytable (rid, myintcol)
select 10, nullif(@.p1, '')
|||Thanks. NULLIF worked.

Deafult data for Image field

Hi,

I am storing my upoaded images in sql server. However, if the user does not upload an image, a broken link is shown in the page.
All that shows up in the DB field is <Binary>.

Can we set this field to a default value?
Is it possible to view this binary data.

I would like a default blank gif to be returned if no image is uploaded.

Regards,
JBISNULL(yourField, @.Default) ? I rarely use the image field, so i'm not sure if this works.|||What if an image is removed at a later date? What would you like to display then?

My gut feeling is that it would be best to not handle this through the database, but through one of your business objects.

With the database, you can make use of the ISNULL, but you cannot use an image data type for a local variable, so KraGiE's suggestion won't work as written. I was messing around with the NorthWind database and came up with this sort of thing:

-- this is for testing, should have a default photo sitting in a table somewhere and that table should be used instead of the @.myTest table
DECLARE @.myTest table (DefaultPhoto image)
INSERT INTO @.myTest SELECT photo from employees WHERE employeeID = 1
SELECT
photo,
DefaultPhoto,
ISNULL(photo,DefaultPhoto) AS DisplayPhoto
FROM
employees
LEFT OUTER JOIN
@.myTest ON employees.photo IS NULL

I'm sure there are 100 better ways to do this :-)

Terri|||ISNULL(Photo, SELECT Photo From Employees Where EmployeeID = @.DefaultPhotoID)

:)

Deadlocks?

hey can i get idea about deadlocks?
how to avoide deadlock actually?
what are the different stretegies used for this technique?> Continue with latest book by Kalen Delaney.
Which one is this Dejan? Are you refering to "Inside SQL Server" ?
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:ugi5BmVbDHA.2632@.TK2MSFTNGP09.phx.gbl...
> Start with Books OnLine, searc for deadlock, there are quite a few topics.
> Continue with latest book by Kalen Delaney.
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "hrishikesh musale" <musaleh@.mahindrabt.com> wrote in message
> news:0a5c01c36d58$8a43e1c0$a501280a@.phx.gbl...
> > hey can i get idea about deadlocks?
> > how to avoide deadlock actually?
> > what are the different stretegies used for this technique?
>|||Kim's book was very good on locking in general, but pretty sparse when it
came to dealing with deadlocks.
Just my opinion.
Bob Castleman
SuccessWare Software
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:ugi5BmVbDHA.2632@.TK2MSFTNGP09.phx.gbl...
> Start with Books OnLine, searc for deadlock, there are quite a few topics.
> Continue with latest book by Kalen Delaney.
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "hrishikesh musale" <musaleh@.mahindrabt.com> wrote in message
> news:0a5c01c36d58$8a43e1c0$a501280a@.phx.gbl...
> > hey can i get idea about deadlocks?
> > how to avoide deadlock actually?
> > what are the different stretegies used for this technique?
>|||I should be more precise. It is "Hands-On SQL Server 2000 : Troubleshooting
Locking and Blocking" ebook, available at
http://www.shareit.com/product.html?cart=1&productid=183645&affiliateid=&languageid=1&cookies=1&backlink=http://www.netimpress.com/Default.asp?¤cies=USD.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Vinodk" <vinodk_sct@.hotmail.com> wrote in message
news:OKWfVIWbDHA.3768@.tk2msftngp13.phx.gbl...
> > Continue with latest book by Kalen Delaney.
> Which one is this Dejan? Are you refering to "Inside SQL Server" ?
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD
> http://www.extremeexperts.com
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:ugi5BmVbDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > Start with Books OnLine, searc for deadlock, there are quite a few
topics.
> > Continue with latest book by Kalen Delaney.
> >
> > --
> > Dejan Sarka, SQL Server MVP
> > FAQ from Neil & others at: http://www.sqlserverfaq.com
> > Please reply only to the newsgroups.
> > PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> > "hrishikesh musale" <musaleh@.mahindrabt.com> wrote in message
> > news:0a5c01c36d58$8a43e1c0$a501280a@.phx.gbl...
> > > hey can i get idea about deadlocks?
> > > how to avoide deadlock actually?
> > > what are the different stretegies used for this technique?
> >
> >
>|||Vinod,
I guess Dejan is referring to..
Hands-On SQL Server 2000 : Troubleshooting Locking and Blocking (ebook)
By Kalen Delaney
http://www.shareit.com/product.html?cart=1&productid=183645&affiliateid=&languageid=1&cookies=1&backlink=http://www.netimpress.com/Default.asp?¤cies=USD
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Vinodk" <vinodk_sct@.hotmail.com> wrote in message
news:OKWfVIWbDHA.3768@.tk2msftngp13.phx.gbl...
> > Continue with latest book by Kalen Delaney.
> Which one is this Dejan? Are you refering to "Inside SQL Server" ?
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD
> http://www.extremeexperts.com
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:ugi5BmVbDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > Start with Books OnLine, searc for deadlock, there are quite a few
topics.
> > Continue with latest book by Kalen Delaney.
> >
> > --
> > Dejan Sarka, SQL Server MVP
> > FAQ from Neil & others at: http://www.sqlserverfaq.com
> > Please reply only to the newsgroups.
> > PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> > "hrishikesh musale" <musaleh@.mahindrabt.com> wrote in message
> > news:0a5c01c36d58$8a43e1c0$a501280a@.phx.gbl...
> > > hey can i get idea about deadlocks?
> > > how to avoide deadlock actually?
> > > what are the different stretegies used for this technique?
> >
> >
>|||First of all I haven't read everything, for example I haven't read the last
book of Kalen.
But most documentation I have seen describe deadlocks which can be expected,
I haven't seen (or missed them) descriptions of deadlocks that are totally
unexpected.
We had a deadlock between one transaction and a single select statement not
run in a transaction. The statement selected only one row of one table, but
still deadlocked with the transaction. From a select you can not see which
locks are used during the transaction, because it is so fast. Trapping the
deadlock and seeing which locks where used was difficult because when the
deadlock is detected by sql-server the information (read locks) disappears.
Because we did not suspect the 'select' to cause any problems it
took a long time before we could determine the cause of the deadlock
and solve the problem. (The deadlock was difficult to produce to begin
with).
In the end, with the database we had then, I could recreate the deadlock at
will within the QA. Outside that database even with the same data I could
not recreate the problem.
So do not exclude simple select statements when hunting down the culprits of
deadlocks.
Ben Brugman
"hrishikesh musale" <musaleh@.mahindrabt.com> wrote in message
news:0a5c01c36d58$8a43e1c0$a501280a@.phx.gbl...
> hey can i get idea about deadlocks?
> how to avoide deadlock actually?
> what are the different stretegies used for this technique?|||Thankx Dinesh and Dejan ... Looks an good book to have ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:eBLD0fWbDHA.2960@.tk2msftngp13.phx.gbl...
> Vinod,
> I guess Dejan is referring to..
> Hands-On SQL Server 2000 : Troubleshooting Locking and Blocking (ebook)
> By Kalen Delaney
>
http://www.shareit.com/product.html?cart=1&productid=183645&affiliateid=&languageid=1&cookies=1&backlink=http://www.netimpress.com/Default.asp?¤cies=USD
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Vinodk" <vinodk_sct@.hotmail.com> wrote in message
> news:OKWfVIWbDHA.3768@.tk2msftngp13.phx.gbl...
> > > Continue with latest book by Kalen Delaney.
> >
> > Which one is this Dejan? Are you refering to "Inside SQL Server" ?
> >
> > --
> > HTH,
> > Vinod Kumar
> > MCSE, DBA, MCAD
> > http://www.extremeexperts.com
> >
> >
> > "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
in
> > message news:ugi5BmVbDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > Start with Books OnLine, searc for deadlock, there are quite a few
> topics.
> > > Continue with latest book by Kalen Delaney.
> > >
> > > --
> > > Dejan Sarka, SQL Server MVP
> > > FAQ from Neil & others at: http://www.sqlserverfaq.com
> > > Please reply only to the newsgroups.
> > > PASS - the definitive, global community
> > > for SQL Server professionals - http://www.sqlpass.org
> > >
> > > "hrishikesh musale" <musaleh@.mahindrabt.com> wrote in message
> > > news:0a5c01c36d58$8a43e1c0$a501280a@.phx.gbl...
> > > > hey can i get idea about deadlocks?
> > > > how to avoide deadlock actually?
> > > > what are the different stretegies used for this technique?
> > >
> > >
> >
> >
>sql

Deadlocks....What is going on?

We have background processes that constantly insert into and update
table A. When we do selects from table A from our end user
application (we have many selects that hit this table), we quite
frequently get deadlocks. Is it not safe to select from a table that
is being updated? Surely we do not have to put (updlock) hint on
all of our queries... Is thier not some way to control this from the
update/insert routines (as opposed to chaning all of our selects)
TIA
A few tips that may help:
On your INSERT and UPDATE routines do the following:
1. Make the transactions as fast as possible. For example, do your data
scrubbing and cleaning and error checking first, then issue the transaction
portion.
2. Use the tables and views in the same order (if possible) within those
routines. This will make other parts of your application wait to acquire
locks and should lessen the deadlocks.
3. If you know that your update is going to affect a significant portion of
the table, you may wish to consider a table lock hint in the query itself.
This would keep your SELECTs from even beginning to view the table while it
was under a large and lengthy update.
If you don't mind your SELECTS looking at data that is under modification,
you may wish to set your ANSI Transaction Isolation Level to READ
UNCOMMITTED. This will allow for dirty reads and so forth. You wouldn't
have to modify all of your SELECT queries, just SET your session for READ
UNCOMMITTED.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Tommy" <talfano@.ncpsolutions.com> wrote in message
news:86ecf3f7.0410051431.54114c50@.posting.google.c om...
> We have background processes that constantly insert into and update
> table A. When we do selects from table A from our end user
> application (we have many selects that hit this table), we quite
> frequently get deadlocks. Is it not safe to select from a table that
> is being updated? Surely we do not have to put (updlock) hint on
> all of our queries... Is thier not some way to control this from the
> update/insert routines (as opposed to chaning all of our selects)
> TIA
|||your update uses not the same index on the table as the
select does. so you access data in different directions
which could cause a deadlock.
two choices:
- either put a NOLOCk hint on the select
- use the same index for the where-clause on the select &
update

>--Original Message--
>A few tips that may help:
>On your INSERT and UPDATE routines do the following:
>1. Make the transactions as fast as possible. For
example, do your data
>scrubbing and cleaning and error checking first, then
issue the transaction
>portion.
>2. Use the tables and views in the same order (if
possible) within those
>routines. This will make other parts of your application
wait to acquire
>locks and should lessen the deadlocks.
>3. If you know that your update is going to affect a
significant portion of
>the table, you may wish to consider a table lock hint in
the query itself.
>This would keep your SELECTs from even beginning to view
the table while it
>was under a large and lengthy update.
>If you don't mind your SELECTS looking at data that is
under modification,
>you may wish to set your ANSI Transaction Isolation Level
to READ
>UNCOMMITTED. This will allow for dirty reads and so
forth. You wouldn't
>have to modify all of your SELECT queries, just SET your
session for READ[vbcol=seagreen]
>UNCOMMITTED.
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>"Tommy" <talfano@.ncpsolutions.com> wrote in message
>news:86ecf3f7.0410051431.54114c50@.posting.google. com...
into and update[vbcol=seagreen]
user[vbcol=seagreen]
we quite[vbcol=seagreen]
from a table that[vbcol=seagreen]
(updlock) hint on[vbcol=seagreen]
this from the[vbcol=seagreen]
our selects)
>
>.
>
|||Rick,
Thanks for your suggestions. We are trying these out right now. I
don't think locking the entire table is an option, but can try dirty
reads, as the transactions should not take that long to complete.
Thanks again,
Tommy
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message news:<#lVO4KzqEHA.3840@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> A few tips that may help:
> On your INSERT and UPDATE routines do the following:
> 1. Make the transactions as fast as possible. For example, do your data
> scrubbing and cleaning and error checking first, then issue the transaction
> portion.
> 2. Use the tables and views in the same order (if possible) within those
> routines. This will make other parts of your application wait to acquire
> locks and should lessen the deadlocks.
> 3. If you know that your update is going to affect a significant portion of
> the table, you may wish to consider a table lock hint in the query itself.
> This would keep your SELECTs from even beginning to view the table while it
> was under a large and lengthy update.
> If you don't mind your SELECTS looking at data that is under modification,
> you may wish to set your ANSI Transaction Isolation Level to READ
> UNCOMMITTED. This will allow for dirty reads and so forth. You wouldn't
> have to modify all of your SELECT queries, just SET your session for READ
> UNCOMMITTED.
>
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Tommy" <talfano@.ncpsolutions.com> wrote in message
> news:86ecf3f7.0410051431.54114c50@.posting.google.c om...

Deadlocks....What is going on?

We have background processes that constantly insert into and update
table A. When we do selects from table A from our end user
application (we have many selects that hit this table), we quite
frequently get deadlocks. Is it not safe to select from a table that
is being updated? Surely we do not have to put (updlock) hint on
all of our queries... Is thier not some way to control this from the
update/insert routines (as opposed to chaning all of our selects)
TIAA few tips that may help:
On your INSERT and UPDATE routines do the following:
1. Make the transactions as fast as possible. For example, do your data
scrubbing and cleaning and error checking first, then issue the transaction
portion.
2. Use the tables and views in the same order (if possible) within those
routines. This will make other parts of your application wait to acquire
locks and should lessen the deadlocks.
3. If you know that your update is going to affect a significant portion of
the table, you may wish to consider a table lock hint in the query itself.
This would keep your SELECTs from even beginning to view the table while it
was under a large and lengthy update.
If you don't mind your SELECTS looking at data that is under modification,
you may wish to set your ANSI Transaction Isolation Level to READ
UNCOMMITTED. This will allow for dirty reads and so forth. You wouldn't
have to modify all of your SELECT queries, just SET your session for READ
UNCOMMITTED.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Tommy" <talfano@.ncpsolutions.com> wrote in message
news:86ecf3f7.0410051431.54114c50@.posting.google.com...
> We have background processes that constantly insert into and update
> table A. When we do selects from table A from our end user
> application (we have many selects that hit this table), we quite
> frequently get deadlocks. Is it not safe to select from a table that
> is being updated? Surely we do not have to put (updlock) hint on
> all of our queries... Is thier not some way to control this from the
> update/insert routines (as opposed to chaning all of our selects)
> TIA|||your update uses not the same index on the table as the
select does. so you access data in different directions
which could cause a deadlock.
two choices:
- either put a NOLOCk hint on the select
- use the same index for the where-clause on the select &
update
>--Original Message--
>A few tips that may help:
>On your INSERT and UPDATE routines do the following:
>1. Make the transactions as fast as possible. For
example, do your data
>scrubbing and cleaning and error checking first, then
issue the transaction
>portion.
>2. Use the tables and views in the same order (if
possible) within those
>routines. This will make other parts of your application
wait to acquire
>locks and should lessen the deadlocks.
>3. If you know that your update is going to affect a
significant portion of
>the table, you may wish to consider a table lock hint in
the query itself.
>This would keep your SELECTs from even beginning to view
the table while it
>was under a large and lengthy update.
>If you don't mind your SELECTS looking at data that is
under modification,
>you may wish to set your ANSI Transaction Isolation Level
to READ
>UNCOMMITTED. This will allow for dirty reads and so
forth. You wouldn't
>have to modify all of your SELECT queries, just SET your
session for READ
>UNCOMMITTED.
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>"Tommy" <talfano@.ncpsolutions.com> wrote in message
>news:86ecf3f7.0410051431.54114c50@.posting.google.com...
>> We have background processes that constantly insert
into and update
>> table A. When we do selects from table A from our end
user
>> application (we have many selects that hit this table),
we quite
>> frequently get deadlocks. Is it not safe to select
from a table that
>> is being updated? Surely we do not have to put
(updlock) hint on
>> all of our queries... Is thier not some way to control
this from the
>> update/insert routines (as opposed to chaning all of
our selects)
>> TIA
>
>.
>|||Rick,
Thanks for your suggestions. We are trying these out right now. I
don't think locking the entire table is an option, but can try dirty
reads, as the transactions should not take that long to complete.
Thanks again,
Tommy
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message news:<#lVO4KzqEHA.3840@.TK2MSFTNGP10.phx.gbl>...
> A few tips that may help:
> On your INSERT and UPDATE routines do the following:
> 1. Make the transactions as fast as possible. For example, do your data
> scrubbing and cleaning and error checking first, then issue the transaction
> portion.
> 2. Use the tables and views in the same order (if possible) within those
> routines. This will make other parts of your application wait to acquire
> locks and should lessen the deadlocks.
> 3. If you know that your update is going to affect a significant portion of
> the table, you may wish to consider a table lock hint in the query itself.
> This would keep your SELECTs from even beginning to view the table while it
> was under a large and lengthy update.
> If you don't mind your SELECTS looking at data that is under modification,
> you may wish to set your ANSI Transaction Isolation Level to READ
> UNCOMMITTED. This will allow for dirty reads and so forth. You wouldn't
> have to modify all of your SELECT queries, just SET your session for READ
> UNCOMMITTED.
>
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Tommy" <talfano@.ncpsolutions.com> wrote in message
> news:86ecf3f7.0410051431.54114c50@.posting.google.com...
> > We have background processes that constantly insert into and update
> > table A. When we do selects from table A from our end user
> > application (we have many selects that hit this table), we quite
> > frequently get deadlocks. Is it not safe to select from a table that
> > is being updated? Surely we do not have to put (updlock) hint on
> > all of our queries... Is thier not some way to control this from the
> > update/insert routines (as opposed to chaning all of our selects)
> >
> > TIA

Deadlocks, why?

We have a problem with a table giving us deadlock issues and we can't
figure out why.

It's a table we write to fairly often perhaps 50 times a minute. And
also do a select of 200 rows at a time from 4 servers every 5 minutes or so.

We are only keeping 48 hours worth of rows in the table which averages
at 30000 a day on a busy day.

This table has 1 PK and 2 FKs plus one TEXT column which does not
participate in the WHERE clause.

We are using binded variables.

We have applied the latest patch to SQL2003 server running on
Windows2003. The patch is supposed to resolve deadlock issues.

Anyone have any advice on how to alleviate this problem.

ThanksDon Vaillancourt (donv@.webimpact.com) writes:
> We have a problem with a table giving us deadlock issues and we can't
> figure out why.
> It's a table we write to fairly often perhaps 50 times a minute. And
> also do a select of 200 rows at a time from 4 servers every 5 minutes or
> so.
> We are only keeping 48 hours worth of rows in the table which averages
> at 30000 a day on a busy day.
> This table has 1 PK and 2 FKs plus one TEXT column which does not
> participate in the WHERE clause.
> We are using binded variables.
> We have applied the latest patch to SQL2003 server running on
> Windows2003. The patch is supposed to resolve deadlock issues.
> Anyone have any advice on how to alleviate this problem.

I'm afraid that there is not enough information your post to make it
possible to give solutions.

Except one: if it is acceptable that one of the process is always
is the victim, make this process emit SET DEADLOCK_PRIORITY LOW.
We have done this in quite a few places in our system. Background
processes don't scream so much about deadlocks as users do.

But if that is not an option, I can only suggest methods to get more
information.

First, have you enabled deadlock trace on your server and looked at
the output? To enable deadlock trace, use Enterprise Manager to add
these two startup options: -T 1204 -T 3605.

Once you have the deadlock output, try to narrow down exactly which
queries that collide. Once you have the queries, you could post them
together with the table definitions (including indexes!). Or you could
post the deadlock traces (which is not very easy to interpret).

--
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|||Oh, I know which queries are involved and which ones are usually the
victims.

But thanks for the trace idea.

We haven't been able to replicate the deadlock issue in-house ass of
yet, but I will certainly keep those options in mind and use them.

Thank you

Erland Sommarskog wrote:
> Don Vaillancourt (donv@.webimpact.com) writes:
>> We have a problem with a table giving us deadlock issues and we can't
>> figure out why.
>>
>> It's a table we write to fairly often perhaps 50 times a minute. And
>> also do a select of 200 rows at a time from 4 servers every 5 minutes or
>> so.
>>
>> We are only keeping 48 hours worth of rows in the table which averages
>> at 30000 a day on a busy day.
>>
>> This table has 1 PK and 2 FKs plus one TEXT column which does not
>> participate in the WHERE clause.
>>
>> We are using binded variables.
>>
>> We have applied the latest patch to SQL2003 server running on
>> Windows2003. The patch is supposed to resolve deadlock issues.
>>
>> Anyone have any advice on how to alleviate this problem.
> I'm afraid that there is not enough information your post to make it
> possible to give solutions.
> Except one: if it is acceptable that one of the process is always
> is the victim, make this process emit SET DEADLOCK_PRIORITY LOW.
> We have done this in quite a few places in our system. Background
> processes don't scream so much about deadlocks as users do.
> But if that is not an option, I can only suggest methods to get more
> information.
> First, have you enabled deadlock trace on your server and looked at
> the output? To enable deadlock trace, use Enterprise Manager to add
> these two startup options: -T 1204 -T 3605.
> Once you have the deadlock output, try to narrow down exactly which
> queries that collide. Once you have the queries, you could post them
> together with the table definitions (including indexes!). Or you could
> post the deadlock traces (which is not very easy to interpret).|||Don Vaillancourt (donv@.webimpact.com) writes:
> Oh, I know which queries are involved and which ones are usually the
> victims.

OK. With table definitions and indexes and the queries, it's possible
that we can spot some potential problems. Without them it's going to
be hard. :-)

--
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|||Don Vaillancourt wrote:
> Oh, I know which queries are involved and which ones are usually the
> victims.
> But thanks for the trace idea.
> We haven't been able to replicate the deadlock issue in-house ass of
> yet, but I will certainly keep those options in mind and use them.

Oh - you meant -that- kind of deadlock. Try some dried plums <g>.|||Hi Don

It could be that you will never be able to replicate the deadlock if your
hardware/environment is exactly the same. If you have not run
sp_blocker_pss80 you may want to try it
http://support.microsoft.com/defaul...kb;en-us;271509

John

"Don Vaillancourt" <donv@.webimpact.com> wrote in message
news:bWVxf.9204$43.7861@.nnrp.ca.mci.com!nnrp1.uune t.ca...
> Oh, I know which queries are involved and which ones are usually the
> victims.
> But thanks for the trace idea.
> We haven't been able to replicate the deadlock issue in-house ass of yet,
> but I will certainly keep those options in mind and use them.
> Thank you
>
> Erland Sommarskog wrote:
>> Don Vaillancourt (donv@.webimpact.com) writes:
>>> We have a problem with a table giving us deadlock issues and we can't
>>> figure out why.
>>>
>>> It's a table we write to fairly often perhaps 50 times a minute. And
>>> also do a select of 200 rows at a time from 4 servers every 5 minutes or
>>> so.
>>> We are only keeping 48 hours worth of rows in the table which averages
>>> at 30000 a day on a busy day.
>>>
>>> This table has 1 PK and 2 FKs plus one TEXT column which does not
>>> participate in the WHERE clause.
>>>
>>> We are using binded variables.
>>>
>>> We have applied the latest patch to SQL2003 server running on
>>> Windows2003. The patch is supposed to resolve deadlock issues.
>>>
>>> Anyone have any advice on how to alleviate this problem.
>>
>> I'm afraid that there is not enough information your post to make it
>> possible to give solutions.
>>
>> Except one: if it is acceptable that one of the process is always
>> is the victim, make this process emit SET DEADLOCK_PRIORITY LOW.
>> We have done this in quite a few places in our system. Background
>> processes don't scream so much about deadlocks as users do.
>>
>> But if that is not an option, I can only suggest methods to get more
>> information.
>>
>> First, have you enabled deadlock trace on your server and looked at
>> the output? To enable deadlock trace, use Enterprise Manager to add
>> these two startup options: -T 1204 -T 3605.
>>
>> Once you have the deadlock output, try to narrow down exactly which
>> queries that collide. Once you have the queries, you could post them
>> together with the table definitions (including indexes!). Or you could
>> post the deadlock traces (which is not very easy to interpret).
>

Deadlocks, severity level, and HRESULT

I am coding a large VB project, using Win2k, SQL Server 2000 SP1,
COM+, and VB6SP5. We have been having some problems with transactions
not being rolled back when an error was raised, and we have traced it
to the following situation. I'm looking for strategies of error
handling for this situation.
Sometimes (most times?) when there is a deadlock, Err.Number (HRESULT)
is 80004005 (-2147467259), but SOMETIMES IT IS 0 (that is, no error).
So because my code was not told about the error, it kept on
processing, but the problem is, THE DTC TRANSACTION HAS BEEN ABORTED,
so all further updates are not in a transaction. Usually DTC wakes up
sooner or later, but by then it might be too late - I may have made
updates thinking I was in a transaction when I was not. These updates
cannot be rolled back, and my database is left in an inconsistent
state. (FYI, the error I get from DTC are 8509 in the profiler,
reflected as 3704 in VB, if it is detected during processing. If it
is not detected until the root object is exiting, I see 8004e002.)
In particular, so far we have seen only ONE section of code where it
sometimes returns 0. All other areas of the code so far have returned
80004005. Also, in the place that returns 0, it only SOMETIMES
returns 0. Other times, it returns 80004005. There does not seem to
be any rhyme or reason to when it returns 0 and when it returns
80004005 (not that I've discovered yet, though). In all cases, the
error information in the profiler trace either:
Error: 1205, Severity: 13, State: 8
or
Error: 1205, Severity: 13, State: 50
There doesn't seem to be any correlation between the state=8 or the
state=50 errors.
All the error handling samples in MSDN use the "on error goto" trick,
but obviously that doesn't work if Err.Number = 0. Is it safe to just
check cn.Errors? Would there ever be any "informational" or "warning"
errors in there that I really should just leave alone and not report
an error? Is Deadlock the only one that could return 0 that really
should be aborted?
Thanks for any strategies you might know about!
Christine Wolak -- Senior Programmer
www.axiom-corp.comkaligrrl@.yahoo.com (Christine) wrote in message news:<d6363ccb.0309120837.1fc9d3e2@.posting.google.com>...
> I am coding a large VB project, using Win2k, SQL Server 2000 SP1,
> COM+, and VB6SP5. We have been having some problems with transactions
> not being rolled back when an error was raised
Okay, we have the answer. SET NOCOUNT ON in your stored procedure.
Always. In any stored procedure that you will be calling from a
client. Here is a link to a KB article:
http://support.microsoft.com/?kbid=240882 . How it can be "by design"
that an error gets raised and is not reported back to the caller, I
don't know, but apparently Microsoft does not plan to address this
issue any time soon. This article also says "a SQL Server trace may
reveal excessive attentions and rollbacks". We saw NONE of that.
NOTHING excessive - only EXPECTED stuff.
In our particular case, we had a deadlock earlier in the process,
during a stored procedure call. But since we were not told about it
(via Err.Number), our code kept processing. We did not have really
"unexpected" problems, because the stored procedure was a "fastpath",
and if for any reason the fastpath cannot be taken, then the code
takes the long path. And the stored procedure return code of 0 just
happens to indicate that the long path was necessary. So the code
just merrily went along, continuing to process.
Which for some reason DTC/COM+/MTS/whatever, let it keep doing. But
it's no longer in a transaction, and any updates at this point are
COMMITTED. At some point later, DTC would wake up and say "hey,
you're not supposed to still be doing stuff!" At this point, we might
see 3704, or 8004d00a (-2147168246), or, if it got all the way to the
very end and is exiting the root object, 8004e002 (-2147164158).
We had several stored procedures that work similarly, and only one had
the problem, and even then not every single time. The difference
would be the amount of informational messages passed back to the
client (DONE_IN_PROC messages).
So just take it from me - always use SET NOCOUNT ON in all stored
procedures called from VB/C++ except for the specific parts of data
that you want returned to the caller, and realize that, if you don't,
you could at any point stop receiving errors from SQL Server.
Christine Wolak -- www.axiom-corp.com

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 when fetching

Hi!
We are developing cgi applications, using ODBC and (among else) MSSQL.
Now we are experiencing deadlocks on MSSQL only, and the problem is -
they occur not when the query is submitted, but when rows are fetched.
We don't use transactions with several statements, so basically every
query/update/insert is in its own transaction. This is because we need
to be compatible with transaction less database systems. Because of
this, we never encounter deadlocks either, except on MSSQL. It seems
that sometimes a select gets in the way of an update in progress or
something, and the select transaction is terminated. We handle this in
our app by just re-issuing the query if a deadlock was detected on query
submittal. However, when the deadlock occurs not when the query is sent,
but when the records are fetched, handling the event becomes much
harder. We'd rather have the query fail right away, than in the middle
of fetching rows.
Any ideas how to accomplish this, using either query hints, session
settings, etc?
Regards,
Erik KnudsenIt sounds like you are using a server side cursor. Have you tried using a
client side cursor instead?
Andrew J. Kelly SQL MVP
"Erik Knudsen" <erikk@.c2i.net> wrote in message
news:eHl7B$YHFHA.3076@.tk2msftngp13.phx.gbl...
> Hi!
> We are developing cgi applications, using ODBC and (among else) MSSQL.
> Now we are experiencing deadlocks on MSSQL only, and the problem is - they
> occur not when the query is submitted, but when rows are fetched.
> We don't use transactions with several statements, so basically every
> query/update/insert is in its own transaction. This is because we need to
> be compatible with transaction less database systems. Because of this, we
> never encounter deadlocks either, except on MSSQL. It seems that sometimes
> a select gets in the way of an update in progress or something, and the
> select transaction is terminated. We handle this in our app by just
> re-issuing the query if a deadlock was detected on query submittal.
> However, when the deadlock occurs not when the query is sent, but when the
> records are fetched, handling the event becomes much harder. We'd rather
> have the query fail right away, than in the middle of fetching rows.
> Any ideas how to accomplish this, using either query hints, session
> settings, etc?
>
> Regards,
> Erik Knudsen|||Erik
http://www.sql-server-performance.com/deadlocks.asp
"Erik Knudsen" <erikk@.c2i.net> wrote in message
news:eHl7B$YHFHA.3076@.tk2msftngp13.phx.gbl...
> Hi!
> We are developing cgi applications, using ODBC and (among else) MSSQL.
> Now we are experiencing deadlocks on MSSQL only, and the problem is -
> they occur not when the query is submitted, but when rows are fetched.
> We don't use transactions with several statements, so basically every
> query/update/insert is in its own transaction. This is because we need
> to be compatible with transaction less database systems. Because of
> this, we never encounter deadlocks either, except on MSSQL. It seems
> that sometimes a select gets in the way of an update in progress or
> something, and the select transaction is terminated. We handle this in
> our app by just re-issuing the query if a deadlock was detected on query
> submittal. However, when the deadlock occurs not when the query is sent,
> but when the records are fetched, handling the event becomes much
> harder. We'd rather have the query fail right away, than in the middle
> of fetching rows.
> Any ideas how to accomplish this, using either query hints, session
> settings, etc?
>
> Regards,
> Erik Knudsen|||Andrew J. Kelly wrote:

> It sounds like you are using a server side cursor. Have you tried using a
> client side cursor instead?
We basically use (ODBC):
--
1. SQLAllocStmt()
2. SQLExecDirect()
3. SQLFetch() (repetedly)
4. SQLFreeStmt()
--
We don't explicitly use cursors, and we don't want to because some of
our supported DBMS's don't support it.
I'd like to force the deadlock to happen at SQLExecDirect() or not at
all. I guess that would involve setting some stricter (not as
optimistic) locking policy or something. Any ideas?
Regards,
Erik Knudsen|||I am not familiar with programming at the base ODBC level so I may be
incorrect here but it seems to me if you are doing a Fetch you are using a
cursor. You can run profiler to see exactly what kinds of statements are
being executed on sql servers end. I am willing to bet you will see
sp_cursorpreparem sp_cursorfetch etc. and that usually indicates a server
side cursor. What isolation level are you running under? Make sure
something is not setting it to serializable and don't assume it is not.
Again you can check with profiler to see if there is a different isolation
level being set.
Andrew J. Kelly SQL MVP
"Erik Knudsen" <erikk@.c2i.net> wrote in message
news:%23oe2mrZHFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Andrew J. Kelly wrote:
>
> We basically use (ODBC):
> --
> 1. SQLAllocStmt()
> 2. SQLExecDirect()
> 3. SQLFetch() (repetedly)
> 4. SQLFreeStmt()
> --
> We don't explicitly use cursors, and we don't want to because some of our
> supported DBMS's don't support it.
> I'd like to force the deadlock to happen at SQLExecDirect() or not at all.
> I guess that would involve setting some stricter (not as optimistic)
> locking policy or something. Any ideas?
>
> Regards,
> Erik Knudsen|||Erik Knudsen wrote:
> Andrew J. Kelly wrote:
>
> We basically use (ODBC):
> --
> 1. SQLAllocStmt()
> 2. SQLExecDirect()
> 3. SQLFetch() (repetedly)
> 4. SQLFreeStmt()
> --
> We don't explicitly use cursors, and we don't want to because some of
> our supported DBMS's don't support it.
> I'd like to force the deadlock to happen at SQLExecDirect() or not at
> all. I guess that would involve setting some stricter (not as
> optimistic) locking policy or something. Any ideas?
>
> Regards,
> Erik Knudsen
Not sure if this is related, but I believe SQLAllocStmt() has been
replaced with SQLAllocHandle(). In addition SQLFreeStmt() is not
recommended in ODBC 3.0 and later (replaced with SQLFreeHandle I think).
But as Andrew mentioned, check your cursor type. probably fastest to use
a forward-only, read-only, firehose cursor with ODBC. These types of
"cursors" are not cursors in the strictest sense. They are analogous to
SQL Server generating a result set (as it normally does) and pulling
that information into the application using ODBC.
David Gugick
Imceda Software
www.imceda.com

Deadlocks slowing down the server

Hello,
We are doing a year-end Inventory and I have only 3 users (data-entry clerks
)
trying to insert into the same table, more or less working at the same time
from their computers.
The insert is a very short statement with very basic 3 columns.
As soon as they started entering the data, deadlock errors started poping
once in a while.
They use a front-end Web Application that traps any errors that are
encountered duringn DML operation and displays the message to the user.
Given that SQL Server 2000 can handle thousands of transactions at peak time
s
without any problems, I am surprised and curious what wrong I could be doing
even for only 3 users to be using the system properly.
Since doing Inventory is a tidious task and a lot of entry is needed within
a
short time, I would appreciate a suggestion as due to the locks, the server
performance degrades drastically.
If I kill some locked transactions in EM, it improves.....
Any ideas ?
Message posted via http://www.droptable.comHi
A badly designed application can deadlock with even just 2 users. I have
looked after applications with 1000's of simultaneous users, but the DB was
architected correctly, and the code was also implemented correctly, we never
had deadlocks.
If the basic rules are not followed on how to build multi-user and scalable
applications, you are going to have trouble.
http://msdn2.microsoft.com/en-us/library/ms191242.aspx
http://msdn.microsoft.com/library/e...con_7a_3hdf.asp
http://support.microsoft.com/defaul...kb;en-us;169960
http://www.sql-server-performance.com/blocking.asp
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:591ee915763c1@.uwe...
> Hello,
> We are doing a year-end Inventory and I have only 3 users (data-entry
> clerks)
> trying to insert into the same table, more or less working at the same
> time
> from their computers.
> The insert is a very short statement with very basic 3 columns.
> As soon as they started entering the data, deadlock errors started poping
> once in a while.
> They use a front-end Web Application that traps any errors that are
> encountered duringn DML operation and displays the message to the user.
> Given that SQL Server 2000 can handle thousands of transactions at peak
> times
> without any problems, I am surprised and curious what wrong I could be
> doing
> even for only 3 users to be using the system properly.
> Since doing Inventory is a tidious task and a lot of entry is needed
> within a
> short time, I would appreciate a suggestion as due to the locks, the
> server
> performance degrades drastically.
> If I kill some locked transactions in EM, it improves.....
> Any ideas ?
> --
> Message posted via http://www.droptable.com|||Thanks Mike,
Lots of reading to do......
I am using ColdFusion and wrapping the insert statement within a
<cftransaction> block to make it behave as one single batch.
Nothing fancy...very simple but I guess I'll have to read more on the
articles.
In pseudocode, I'm doing this:
<transaction>
.....<query>
...............INSERT INTO Table_Name (column_list) VALUES (value_list
)
.....<query>
<transaction>
I'll have to see what more I can do to change this logic...
Message posted via http://www.droptable.com|||Sameer wrote on Tue, 20 Dec 2005 15:39:39 GMT:

> Thanks Mike,
> Lots of reading to do......
> I am using ColdFusion and wrapping the insert statement within a
> <cftransaction> block to make it behave as one single batch.
> Nothing fancy...very simple but I guess I'll have to read more on the
> articles.
> In pseudocode, I'm doing this:
> <transaction>
> .....<query>
> ...............INSERT INTO Table_Name (column_list) VALUES
> (value_list)
> .....<query>
> <transaction>
> I'll have to see what more I can do to change this logic...
>
A single statement like that in it's own transaction shouldn't cause
deadlocks, should it? Are there any triggers on the table, if so that's
where I'd look. There is a DBCC TRACE option you can enable to write
deadlock information into the SQL Server logs, it might help you locate the
source of the problem.
Dan|||Hi Daniel,
Yes, there is a trigger on that table and fires after every INSERT or UPDATE
.
This is the root of the delay I think and I'm looking into it right now.
Thanks for pointing it out.
Message posted via http://www.droptable.com|||OK...I modified the trigger and now everything seems to be working fine eve
n
when they are all entering at the same time.
After reading the articles, should I still make the following changes ?
1. Use a lower Isolation Level
2. Set READ_COMMITTED_SNAPSHOT to ON
3. Set ALLOW_SNAPSHOT_ISOLATION to ON
Can someone shed some insight on how to do that and what consequences will I
face ?
Thanks.
Message posted via http://www.droptable.com|||are we talking SQL2000 or 2005 ?
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:592053207623d@.uwe...
> OK...I modified the trigger and now everything seems to be working fine
> even
> when they are all entering at the same time.
> After reading the articles, should I still make the following changes ?
> 1. Use a lower Isolation Level
> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
> Can someone shed some insight on how to do that and what consequences will
> I
> face ?
> Thanks.
> --
> Message posted via http://www.droptable.com|||> 1. Use a lower Isolation Level
What isolation level are you using? Read Committed should be fine for most
applications. If you are using Serializable you should rethink it.

> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
These are 2005 features only. It looks like you are on 2000.
Andrew J. Kelly SQL MVP
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:592053207623d@.uwe...
> OK...I modified the trigger and now everything seems to be working fine
> even
> when they are all entering at the same time.
> After reading the articles, should I still make the following changes ?
> 1. Use a lower Isolation Level
> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
> Can someone shed some insight on how to do that and what consequences will
> I
> face ?
> Thanks.
> --
> Message posted via http://www.droptable.com|||SQL 2000 SP4 with Win2K Advanced Server 2000
Message posted via http://www.droptable.com|||Since I don't know how to set an Isolation Level, my guess is that it is
using the default one.
How do you check the setting ?
How do you set it ?
Message posted via http://www.droptable.com

Deadlocks slowing down the server

Hello,
We are doing a year-end Inventory and I have only 3 users (data-entry clerks)
trying to insert into the same table, more or less working at the same time
from their computers.
The insert is a very short statement with very basic 3 columns.
As soon as they started entering the data, deadlock errors started poping
once in a while.
They use a front-end Web Application that traps any errors that are
encountered duringn DML operation and displays the message to the user.
Given that SQL Server 2000 can handle thousands of transactions at peak times
without any problems, I am surprised and curious what wrong I could be doing
even for only 3 users to be using the system properly.
Since doing Inventory is a tidious task and a lot of entry is needed within a
short time, I would appreciate a suggestion as due to the locks, the server
performance degrades drastically.
If I kill some locked transactions in EM, it improves.....
Any ideas ?
Message posted via http://www.droptable.com
Hi
A badly designed application can deadlock with even just 2 users. I have
looked after applications with 1000's of simultaneous users, but the DB was
architected correctly, and the code was also implemented correctly, we never
had deadlocks.
If the basic rules are not followed on how to build multi-user and scalable
applications, you are going to have trouble.
http://msdn2.microsoft.com/en-us/library/ms191242.aspx
http://msdn.microsoft.com/library/en...on_7a_3hdf.asp
http://support.microsoft.com/default...b;en-us;169960
http://www.sql-server-performance.com/blocking.asp
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:591ee915763c1@.uwe...
> Hello,
> We are doing a year-end Inventory and I have only 3 users (data-entry
> clerks)
> trying to insert into the same table, more or less working at the same
> time
> from their computers.
> The insert is a very short statement with very basic 3 columns.
> As soon as they started entering the data, deadlock errors started poping
> once in a while.
> They use a front-end Web Application that traps any errors that are
> encountered duringn DML operation and displays the message to the user.
> Given that SQL Server 2000 can handle thousands of transactions at peak
> times
> without any problems, I am surprised and curious what wrong I could be
> doing
> even for only 3 users to be using the system properly.
> Since doing Inventory is a tidious task and a lot of entry is needed
> within a
> short time, I would appreciate a suggestion as due to the locks, the
> server
> performance degrades drastically.
> If I kill some locked transactions in EM, it improves.....
> Any ideas ?
> --
> Message posted via http://www.droptable.com
|||Thanks Mike,
Lots of reading to do......
I am using ColdFusion and wrapping the insert statement within a
<cftransaction> block to make it behave as one single batch.
Nothing fancy...very simple but I guess I'll have to read more on the
articles.
In pseudocode, I'm doing this:
<transaction>
......<query>
................INSERT INTO Table_Name (column_list) VALUES (value_list)
......<query>
<transaction>
I'll have to see what more I can do to change this logic...
Message posted via http://www.droptable.com
|||Sameer wrote on Tue, 20 Dec 2005 15:39:39 GMT:

> Thanks Mike,
> Lots of reading to do......
> I am using ColdFusion and wrapping the insert statement within a
> <cftransaction> block to make it behave as one single batch.
> Nothing fancy...very simple but I guess I'll have to read more on the
> articles.
> In pseudocode, I'm doing this:
> <transaction>
> .....<query>
> ...............INSERT INTO Table_Name (column_list) VALUES
> (value_list)
> .....<query>
> <transaction>
> I'll have to see what more I can do to change this logic...
>
A single statement like that in it's own transaction shouldn't cause
deadlocks, should it? Are there any triggers on the table, if so that's
where I'd look. There is a DBCC TRACE option you can enable to write
deadlock information into the SQL Server logs, it might help you locate the
source of the problem.
Dan
|||Hi Daniel,
Yes, there is a trigger on that table and fires after every INSERT or UPDATE.
This is the root of the delay I think and I'm looking into it right now.
Thanks for pointing it out.
Message posted via http://www.droptable.com
|||OK...I modified the trigger and now everything seems to be working fine even
when they are all entering at the same time.
After reading the articles, should I still make the following changes ?
1. Use a lower Isolation Level
2. Set READ_COMMITTED_SNAPSHOT to ON
3. Set ALLOW_SNAPSHOT_ISOLATION to ON
Can someone shed some insight on how to do that and what consequences will I
face ?
Thanks.
Message posted via http://www.droptable.com
|||are we talking SQL2000 or 2005 ?
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:592053207623d@.uwe...
> OK...I modified the trigger and now everything seems to be working fine
> even
> when they are all entering at the same time.
> After reading the articles, should I still make the following changes ?
> 1. Use a lower Isolation Level
> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
> Can someone shed some insight on how to do that and what consequences will
> I
> face ?
> Thanks.
> --
> Message posted via http://www.droptable.com
|||> 1. Use a lower Isolation Level
What isolation level are you using? Read Committed should be fine for most
applications. If you are using Serializable you should rethink it.

> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
These are 2005 features only. It looks like you are on 2000.
Andrew J. Kelly SQL MVP
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:592053207623d@.uwe...
> OK...I modified the trigger and now everything seems to be working fine
> even
> when they are all entering at the same time.
> After reading the articles, should I still make the following changes ?
> 1. Use a lower Isolation Level
> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
> Can someone shed some insight on how to do that and what consequences will
> I
> face ?
> Thanks.
> --
> Message posted via http://www.droptable.com
|||SQL 2000 SP4 with Win2K Advanced Server 2000
Message posted via http://www.droptable.com
|||Since I don't know how to set an Isolation Level, my guess is that it is
using the default one.
How do you check the setting ?
How do you set it ?
Message posted via http://www.droptable.com

Deadlocks slowing down the server

Hello,
We are doing a year-end Inventory and I have only 3 users (data-entry clerks)
trying to insert into the same table, more or less working at the same time
from their computers.
The insert is a very short statement with very basic 3 columns.
As soon as they started entering the data, deadlock errors started poping
once in a while.
They use a front-end Web Application that traps any errors that are
encountered duringn DML operation and displays the message to the user.
Given that SQL Server 2000 can handle thousands of transactions at peak times
without any problems, I am surprised and curious what wrong I could be doing
even for only 3 users to be using the system properly.
Since doing Inventory is a tidious task and a lot of entry is needed within a
short time, I would appreciate a suggestion as due to the locks, the server
performance degrades drastically.
If I kill some locked transactions in EM, it improves.....
Any ideas ?
--
Message posted via http://www.sqlmonster.comHi
A badly designed application can deadlock with even just 2 users. I have
looked after applications with 1000's of simultaneous users, but the DB was
architected correctly, and the code was also implemented correctly, we never
had deadlocks.
If the basic rules are not followed on how to build multi-user and scalable
applications, you are going to have trouble.
http://msdn2.microsoft.com/en-us/library/ms191242.aspx
http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_7a_3hdf.asp
http://support.microsoft.com/default.aspx?scid=kb;en-us;169960
http://www.sql-server-performance.com/blocking.asp
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:591ee915763c1@.uwe...
> Hello,
> We are doing a year-end Inventory and I have only 3 users (data-entry
> clerks)
> trying to insert into the same table, more or less working at the same
> time
> from their computers.
> The insert is a very short statement with very basic 3 columns.
> As soon as they started entering the data, deadlock errors started poping
> once in a while.
> They use a front-end Web Application that traps any errors that are
> encountered duringn DML operation and displays the message to the user.
> Given that SQL Server 2000 can handle thousands of transactions at peak
> times
> without any problems, I am surprised and curious what wrong I could be
> doing
> even for only 3 users to be using the system properly.
> Since doing Inventory is a tidious task and a lot of entry is needed
> within a
> short time, I would appreciate a suggestion as due to the locks, the
> server
> performance degrades drastically.
> If I kill some locked transactions in EM, it improves.....
> Any ideas ?
> --
> Message posted via http://www.sqlmonster.com|||Thanks Mike,
Lots of reading to do......
I am using ColdFusion and wrapping the insert statement within a
<cftransaction> block to make it behave as one single batch.
Nothing fancy...very simple but I guess I'll have to read more on the
articles.
In pseudocode, I'm doing this:
<transaction>
.....<query>
...............INSERT INTO Table_Name (column_list) VALUES (value_list)
.....<query>
<transaction>
I'll have to see what more I can do to change this logic...
--
Message posted via http://www.sqlmonster.com|||Sameer wrote on Tue, 20 Dec 2005 15:39:39 GMT:
> Thanks Mike,
> Lots of reading to do......
> I am using ColdFusion and wrapping the insert statement within a
> <cftransaction> block to make it behave as one single batch.
> Nothing fancy...very simple but I guess I'll have to read more on the
> articles.
> In pseudocode, I'm doing this:
> <transaction>
> .....<query>
> ...............INSERT INTO Table_Name (column_list) VALUES
> (value_list)
> .....<query>
> <transaction>
> I'll have to see what more I can do to change this logic...
>
A single statement like that in it's own transaction shouldn't cause
deadlocks, should it? Are there any triggers on the table, if so that's
where I'd look. There is a DBCC TRACE option you can enable to write
deadlock information into the SQL Server logs, it might help you locate the
source of the problem.
Dan|||Hi Daniel,
Yes, there is a trigger on that table and fires after every INSERT or UPDATE.
This is the root of the delay I think and I'm looking into it right now.
Thanks for pointing it out.
--
Message posted via http://www.sqlmonster.com|||OK...I modified the trigger and now everything seems to be working fine even
when they are all entering at the same time.
After reading the articles, should I still make the following changes ?
1. Use a lower Isolation Level
2. Set READ_COMMITTED_SNAPSHOT to ON
3. Set ALLOW_SNAPSHOT_ISOLATION to ON
Can someone shed some insight on how to do that and what consequences will I
face ?
Thanks.
--
Message posted via http://www.sqlmonster.com|||are we talking SQL2000 or 2005 ?
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:592053207623d@.uwe...
> OK...I modified the trigger and now everything seems to be working fine
> even
> when they are all entering at the same time.
> After reading the articles, should I still make the following changes ?
> 1. Use a lower Isolation Level
> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
> Can someone shed some insight on how to do that and what consequences will
> I
> face ?
> Thanks.
> --
> Message posted via http://www.sqlmonster.com|||> 1. Use a lower Isolation Level
What isolation level are you using? Read Committed should be fine for most
applications. If you are using Serializable you should rethink it.
> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
These are 2005 features only. It looks like you are on 2000.
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:592053207623d@.uwe...
> OK...I modified the trigger and now everything seems to be working fine
> even
> when they are all entering at the same time.
> After reading the articles, should I still make the following changes ?
> 1. Use a lower Isolation Level
> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
> Can someone shed some insight on how to do that and what consequences will
> I
> face ?
> Thanks.
> --
> Message posted via http://www.sqlmonster.com|||SQL 2000 SP4 with Win2K Advanced Server 2000
--
Message posted via http://www.sqlmonster.com|||Since I don't know how to set an Isolation Level, my guess is that it is
using the default one.
How do you check the setting ?
How do you set it ?
--
Message posted via http://www.sqlmonster.com|||It really depends on how you are connecting to SQL Server. The easiest way
to see what isolation levels a specific connection is using is to run a
profiler trace. The Existing connection event will show the current
Isolation level and if the app or the driver changes it batch completed will
show the statement. You usually set it with SET TRANSACTION ISOLATION LEVEL
xxxx. See BooksOnLine for more details.
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:5920cb73e8dd5@.uwe...
> Since I don't know how to set an Isolation Level, my guess is that it is
> using the default one.
> How do you check the setting ?
> How do you set it ?
> --
> Message posted via http://www.sqlmonster.com|||Thank you so much all....I highly appreciate your advises :)
--
Message posted via http://www.sqlmonster.com

Deadlocks same time Full DB backup

I have a cluster SQL Server 2000 Enterprise with SP3A
that has a full database backup that runs nightly at
12:15 AM.
The database system has functioning properly for about
six months.
Three consective dailys in a row I have received a
deadlock at the same as the full database is running
(12:15 AM). The full database executes for about 2
minutes. Thats about the same length of time as the
deadlock. This deadlock information comes from the DBCC
Traceon (1204) setting.
What could cause this problem?
Thank You,
FrankCan you post what U get in the SQL server errorlog this way we can see the
sequence of events leading to the deadlock issue..I guess that SQL server
service is starting with the -T1204 -T3605
to ensure that this info is sent to the error log.
--
Olu Adedeji
"Frank" <anonymous@.discussions.microsoft.com> wrote in message
news:4fd801c4000e$53839150$a501280a@.phx.gbl...
> I have a cluster SQL Server 2000 Enterprise with SP3A
> that has a full database backup that runs nightly at
> 12:15 AM.
> The database system has functioning properly for about
> six months.
> Three consective dailys in a row I have received a
> deadlock at the same as the full database is running
> (12:15 AM). The full database executes for about 2
> minutes. Thats about the same length of time as the
> deadlock. This deadlock information comes from the DBCC
> Traceon (1204) setting.
>
> What could cause this problem?
> Thank You,
> Frank|||My 1st guess is that you are experiencing IO Saturation.
Is your backup occuring on a separate volume than your data ?
Greg Jackson
PDX, Oregonsql

Deadlocks same time Full DB backup

I have a cluster SQL Server 2000 Enterprise with SP3A
that has a full database backup that runs nightly at
12:15 AM.
The database system has functioning properly for about
six months.
Three consective dailys in a row I have received a
deadlock at the same as the full database is running
(12:15 AM). The full database executes for about 2
minutes. Thats about the same length of time as the
deadlock. This deadlock information comes from the DBCC
Traceon (1204) setting.
What could cause this problem?
Thank You,
FrankCan you post what U get in the SQL server errorlog this way we can see the
sequence of events leading to the deadlock issue..I guess that SQL server
service is starting with the -T1204 -T3605
to ensure that this info is sent to the error log.
Olu Adedeji
"Frank" <anonymous@.discussions.microsoft.com> wrote in message
news:4fd801c4000e$53839150$a501280a@.phx.gbl...
> I have a cluster SQL Server 2000 Enterprise with SP3A
> that has a full database backup that runs nightly at
> 12:15 AM.
> The database system has functioning properly for about
> six months.
> Three consective dailys in a row I have received a
> deadlock at the same as the full database is running
> (12:15 AM). The full database executes for about 2
> minutes. Thats about the same length of time as the
> deadlock. This deadlock information comes from the DBCC
> Traceon (1204) setting.
>
> What could cause this problem?
> Thank You,
> Frank|||My 1st guess is that you are experiencing IO Saturation.
Is your backup occuring on a separate volume than your data ?
Greg Jackson
PDX, Oregon

Deadlocks problems

Hi everybody,
I have deadlocks problems, somebody could tell me how is the best way to
handle concurrency'
Thanks in advance,
RICHARD_SQLRichard (Richard@.discussions.microsoft.com) writes:
> I have deadlocks problems, somebody could tell me how is the best way to
> handle concurrency'
A very broad question, and far too broad to answer in a newsgroup.
Books Online has a section on troubleshooting deadlook. In Books Online for
SQL 2000, it's under Troubleshooting->Server and Database Troubleshooting->
Troubleshooting Deadlocks.
In Books Online for SQL 2005, the URL is
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2018a963-2f83-492e-ae53-45af
e65025f7.htm
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|||http://sql-server-performance.com/deadlocks.asp
Madhivanan
Erland Sommarskog wrote:
> Richard (Richard@.discussions.microsoft.com) writes:
> A very broad question, and far too broad to answer in a newsgroup.
> Books Online has a section on troubleshooting deadlook. In Books Online fo
r
> SQL 2000, it's under Troubleshooting->Server and Database Troubleshooting-
> Troubleshooting Deadlocks.
> In Books Online for SQL 2005, the URL is
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2018a963-2f83-492e-ae53-45
afe65025f7.htm
>
> --
> 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

Deadlocks problem when database files growing

We are running a very busy SQL Server 2000 Enterprise in the cluster "passive active" environment. Hundreds of transactions are going through and all of them are
logged in to a special database we've created. For an each real transaction we are getting around 10 records inserted in to this database.
I found that whenever the database grows its files, especially the log file, we're getting a lot of deadlocks which we are able to resolve only by failing over to another node.

Any suggestions would be appreciated.

Thanks,

DanHowdy

Is this a recent problem of a long term issue?
Sounds like an application design issue....I doubt he growing logfiles would cause the problem - they would just be a symptom of how busy the system is. Also, if the logs grow really quickly, its possible the app is holding open tables etc too long and then causing the deadlocks. Shorter tansactuions may help. I have used locking hint TABLOCKX to get around a lot of problems, but it MAY NOT be the best solution for you. Sounds very application specific.........

I assume you have plenty of disk space for the TEMPDB and the database files?

More info / background would be useful.

Cheers,

SG

Deadlocks on sysobjects

Hi,
We've been getting some deadlocks on our production box.
I've turned on the 3605 and 1204 flags and I've found an error in the
errorlog:
I think the key lines I'm concerned about are these:
Process 1 -> PAG: 7:1:784316 CleanCnt:2 Mode: IX Flags:
0x2
Process 2 -> PAG: 7:1:812678 CleanCnt:2 Mode: SIU Flags:
0x2
This appears to be locks on the sysobjects table in my database?
Both processes appear to be running this commands:
Input Buf: RPC Event: sp_prepexec;1
Does anyone have an idea where I can go from here? I was assuming I'd be
getting a lock on a user table making it easier to debug.
The only major change recently, is a view that joins 3 tables together
partitioned on date.
Any ideas appreciated.
Nick
Hi Nick
You may want to post the full details for each node. Alternatively you may
want to see if sp_blocker_pss80 produces clearer output
http://support.microsoft.com/kb/271509/EN-US/
John
"Nick Dawson" wrote:

> Hi,
> We've been getting some deadlocks on our production box.
> I've turned on the 3605 and 1204 flags and I've found an error in the
> errorlog:
> I think the key lines I'm concerned about are these:
> Process 1 -> PAG: 7:1:784316 CleanCnt:2 Mode: IX Flags:
> 0x2
> Process 2 -> PAG: 7:1:812678 CleanCnt:2 Mode: SIU Flags:
> 0x2
> This appears to be locks on the sysobjects table in my database?
> Both processes appear to be running this commands:
> Input Buf: RPC Event: sp_prepexec;1
> Does anyone have an idea where I can go from here? I was assuming I'd be
> getting a lock on a user table making it easier to debug.
> The only major change recently, is a view that joins 3 tables together
> partitioned on date.
> Any ideas appreciated.
> Nick
>
>

Deadlocks on sysobjects

Hi,
We've been getting some deadlocks on our production box.
I've turned on the 3605 and 1204 flags and I've found an error in the
errorlog:
I think the key lines I'm concerned about are these:
Process 1 -> PAG: 7:1:784316 CleanCnt:2 Mode: IX Flags:
0x2
Process 2 -> PAG: 7:1:812678 CleanCnt:2 Mode: SIU Flags:
0x2
This appears to be locks on the sysobjects table in my database?
Both processes appear to be running this commands:
Input Buf: RPC Event: sp_prepexec;1
Does anyone have an idea where I can go from here? I was assuming I'd be
getting a lock on a user table making it easier to debug.
The only major change recently, is a view that joins 3 tables together
partitioned on date.
Any ideas appreciated.
NickHi Nick
You may want to post the full details for each node. Alternatively you may
want to see if sp_blocker_pss80 produces clearer output
http://support.microsoft.com/kb/271509/EN-US/
John
"Nick Dawson" wrote:
> Hi,
> We've been getting some deadlocks on our production box.
> I've turned on the 3605 and 1204 flags and I've found an error in the
> errorlog:
> I think the key lines I'm concerned about are these:
> Process 1 -> PAG: 7:1:784316 CleanCnt:2 Mode: IX Flags:
> 0x2
> Process 2 -> PAG: 7:1:812678 CleanCnt:2 Mode: SIU Flags:
> 0x2
> This appears to be locks on the sysobjects table in my database?
> Both processes appear to be running this commands:
> Input Buf: RPC Event: sp_prepexec;1
> Does anyone have an idea where I can go from here? I was assuming I'd be
> getting a lock on a user table making it easier to debug.
> The only major change recently, is a view that joins 3 tables together
> partitioned on date.
> Any ideas appreciated.
> Nick
>
>sql

Deadlocks on sysobjects

Hi,
We've been getting some deadlocks on our production box.
I've turned on the 3605 and 1204 flags and I've found an error in the
errorlog:
I think the key lines I'm concerned about are these:
Process 1 -> PAG: 7:1:784316 CleanCnt:2 Mode: IX Flags:
0x2
Process 2 -> PAG: 7:1:812678 CleanCnt:2 Mode: SIU Flags:
0x2
This appears to be locks on the sysobjects table in my database?
Both processes appear to be running this commands:
Input Buf: RPC Event: sp_prepexec;1
Does anyone have an idea where I can go from here? I was assuming I'd be
getting a lock on a user table making it easier to debug.
The only major change recently, is a view that joins 3 tables together
partitioned on date.
Any ideas appreciated.
NickHi Nick
You may want to post the full details for each node. Alternatively you may
want to see if sp_blocker_pss80 produces clearer output
http://support.microsoft.com/kb/271509/EN-US/
John
"Nick Dawson" wrote:

> Hi,
> We've been getting some deadlocks on our production box.
> I've turned on the 3605 and 1204 flags and I've found an error in the
> errorlog:
> I think the key lines I'm concerned about are these:
> Process 1 -> PAG: 7:1:784316 CleanCnt:2 Mode: IX Flags:
> 0x2
> Process 2 -> PAG: 7:1:812678 CleanCnt:2 Mode: SIU Flags
:
> 0x2
> This appears to be locks on the sysobjects table in my database?
> Both processes appear to be running this commands:
> Input Buf: RPC Event: sp_prepexec;1
> Does anyone have an idea where I can go from here? I was assuming I'd be
> getting a lock on a user table making it easier to debug.
> The only major change recently, is a view that joins 3 tables together
> partitioned on date.
> Any ideas appreciated.
> Nick
>
>

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

Deadlocks on Queries

Hi,
I have a situation where I have inherited a system that is being
stress tested at the moment. The main table used in the DB has a
number of indexes. The problem I am having is that under load, I keep
getting deadlocks on iwhat appears to be the indexes on the tables.
More specifically a row appears to be inserted / updated, and another
select query is being locked out when trying to query that table.
I haven't touched fill factors (they are still the default zero). Any
pointers / reading material would be appreciated.
Check if your query includes BEGIN TRAN and COMMIT TRAN at proper place
"Spondishy" wrote:

> Hi,
> I have a situation where I have inherited a system that is being
> stress tested at the moment. The main table used in the DB has a
> number of indexes. The problem I am having is that under load, I keep
> getting deadlocks on iwhat appears to be the indexes on the tables.
> More specifically a row appears to be inserted / updated, and another
> select query is being locked out when trying to query that table.
> I haven't touched fill factors (they are still the default zero). Any
> pointers / reading material would be appreciated.
>
sql

Deadlocks on Queries

Hi,
I have a situation where I have inherited a system that is being
stress tested at the moment. The main table used in the DB has a
number of indexes. The problem I am having is that under load, I keep
getting deadlocks on iwhat appears to be the indexes on the tables.
More specifically a row appears to be inserted / updated, and another
select query is being locked out when trying to query that table.
I haven't touched fill factors (they are still the default zero). Any
pointers / reading material would be appreciated.Check if your query includes BEGIN TRAN and COMMIT TRAN at proper place
"Spondishy" wrote:
> Hi,
> I have a situation where I have inherited a system that is being
> stress tested at the moment. The main table used in the DB has a
> number of indexes. The problem I am having is that under load, I keep
> getting deadlocks on iwhat appears to be the indexes on the tables.
> More specifically a row appears to be inserted / updated, and another
> select query is being locked out when trying to query that table.
> I haven't touched fill factors (they are still the default zero). Any
> pointers / reading material would be appreciated.
>

Deadlocks on Queries

Hi,
I have a situation where I have inherited a system that is being
stress tested at the moment. The main table used in the DB has a
number of indexes. The problem I am having is that under load, I keep
getting deadlocks on iwhat appears to be the indexes on the tables.
More specifically a row appears to be inserted / updated, and another
select query is being locked out when trying to query that table.
I haven't touched fill factors (they are still the default zero). Any
pointers / reading material would be appreciated.Check if your query includes BEGIN TRAN and COMMIT TRAN at proper place
"Spondishy" wrote:

> Hi,
> I have a situation where I have inherited a system that is being
> stress tested at the moment. The main table used in the DB has a
> number of indexes. The problem I am having is that under load, I keep
> getting deadlocks on iwhat appears to be the indexes on the tables.
> More specifically a row appears to be inserted / updated, and another
> select query is being locked out when trying to query that table.
> I haven't touched fill factors (they are still the default zero). Any
> pointers / reading material would be appreciated.
>

deadlocks not resolved by SQL Server

I am under the impression that SQL Server would automatically detect a
deadlock and roll back a participating transaction. However, I have had a
few cases where automatic resolution did not occur. Instead, I had to go in
and manually kill the transaction that is causing a deadlock.
Is there anyway to avoid this? I don't want to have to manually kill a
transaction to unwind a deadlock if that's at all possible.
Thanks in advanceHi
It sounds like you have prolonged blocking rather than a deadlock, in which
case your query should timeout. Make sure that your application has
overridden the default timeouts and requested to wait indefinitely.
You should also investigate if you application has long running transactions
that have not been correctly committed/rolled back or if poor indexing is
affecting performance.
John
"C.W." wrote:
> I am under the impression that SQL Server would automatically detect a
> deadlock and roll back a participating transaction. However, I have had a
> few cases where automatic resolution did not occur. Instead, I had to go in
> and manually kill the transaction that is causing a deadlock.
> Is there anyway to avoid this? I don't want to have to manually kill a
> transaction to unwind a deadlock if that's at all possible.
> Thanks in advance
>
>