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 involving parallelism

We're experiencing a large number of deadlocks since we began running
SQL Server 2000 Enterprise Edition SP3 on a Dell 6650 with hyper
threading intel processors. We don't have the same problem on Dell
6650's w/o the hyper threading. If I turn off the parallel query
processing option the deadlocks stop. I've tried all of the suggestions
from the Microsoft Knowledge Base under the following link -

http://support.microsoft.com/?kbid=837983

The only suggestion that actually yielded results was turning off
parallel query processing but I don't want to give up what should be a
performance advantage if it wasn't for the deadlocks. Query tuning and
index tuning hasn't helped. Any suggestions? I haven't applied SP4
yet. I'm wondering if anyone has seen the same problem resolved with
SP4.

*** Sent via Developersdex http://www.developersdex.com ***T Dubya (timber_toes@.bigfoot.com) writes:
> We're experiencing a large number of deadlocks since we began running
> SQL Server 2000 Enterprise Edition SP3 on a Dell 6650 with hyper
> threading intel processors. We don't have the same problem on Dell
> 6650's w/o the hyper threading. If I turn off the parallel query
> processing option the deadlocks stop. I've tried all of the suggestions
> from the Microsoft Knowledge Base under the following link -

A general recommendation is to change "max degree of parallelism" to
the number of physical processors. Whether this will help your parallelism
deadlocks, I don't know, but you should make that configuration anyway.

As it was explained to me, HT processors creates that extra CPU by
giving it idle cycles from the first processor. But if you have a
parallel query, those idle cycles are not really there, and you get
a serialization of the processing.

If that does not, try tracking down the query/ies that have this
problem, and add "OPTION (MAXDOP 1)" to these queries, to turn off
parallelism for these queries.

> I haven't applied SP4 yet. I'm wondering if anyone has seen the same
> problem resolved with SP4.

I have no idea if that will help, but some general notes on SP4:

SP4 is here: http://www.microsoft.com/sql/downloads/2000/sp4.mspx.
Please observe the note about AWE. The note is out of date, since
there actually is a fix for the AWE problem; just follow the link
in the note.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the suggestion. I'll give it a try.
I found a "Best Practices" note in my Microsoft SQL Server 2000
Administrators Pocket Consultant on page 38 that recommends not
assigning the higher numbered processors (5,6,7, and 8) to the SQL
Server. It goes on to explain that Windows assigns deferred process
calls associated with network interface cards to the highest numbered
processors. If the system has two NICs, for example, the calls would be
directed to CPUs 7 and 8. Even though the default installation made
processors 0 through 7 available to the SQL Server it sounds like the
recommendation is to only make 0 through 3 available. What do you
think? Perhaps this would have the same effect as only assigning 4
processors for parallel execution of queries.

*** Sent via Developersdex http://www.developersdex.com ***|||T Dubya (timber_toes@.bigfoot.com) writes:
> Thanks for the suggestion. I'll give it a try.
> I found a "Best Practices" note in my Microsoft SQL Server 2000
> Administrators Pocket Consultant on page 38 that recommends not
> assigning the higher numbered processors (5,6,7, and 8) to the SQL
> Server. It goes on to explain that Windows assigns deferred process
> calls associated with network interface cards to the highest numbered
> processors. If the system has two NICs, for example, the calls would be
> directed to CPUs 7 and 8. Even though the default installation made
> processors 0 through 7 available to the SQL Server it sounds like the
> recommendation is to only make 0 through 3 available. What do you
> think? Perhaps this would have the same effect as only assigning 4
> processors for parallel execution of queries.

I will have to admit that the discussion went over my head here. If CPU:s
0-3 are the "default CPU" of each physical processor, this seems like
a good choice. I will have to admit that I don't know how processors
are numbered in a multi-processor HT box.

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

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

your problem is SQL Server 2000 SP3 - SP3 is not hiperthread aware
which means that if your query is parallelized into several worker
threads, these threads might end-up running concurrently on the
same physical processor, which means 2 threads running on 1 physical
processor due to Hyperthreading. I know that there have been made some
changes in build 818, and SP4, especially regarding HT and NUMA -
what you basically sohuld do is test your situation with build 818 or
SP4,
or turn off hyperthreading. Test, but be aware that Hyperthreading
is only giving you maybe 10% extra performance if you're lucky,
whereas
your parallisme within SQL Server can give you enormous amounts of
performance gains. Its no secret that Intel made hyperthreading since
the extra thread could run Antivirus software while the CPU was more
a less idle in some of their components. Running SQL Server 2000 with
hyperthreading can give you some headaches, try running on the latest
build
or turn of hyperthreading.sql

Deadlocks

I have in a table with some 2000 records with a Document ID which is a Unique Seq Number and details with a Status Flag. Multiple Users access the Table. When a user accesses a particular Document ID the Status flag changes from "N" to "W". Once he finishes working with data on that particular Document ID the Status Changes to "C". When another user tries to pickup a Record the next record in the Seq with the Status as "N" will get fetched. I use a Stored Procedure to assign a Document id to a user and update the Record/status details in the Table. I have used the No lock clause in the SP while retrieving a particular Document ID. It was working fine till sometime. Now it has started giving the following error

Error: Run-time error '-2147467259(80004005)'

[Microsoft][ODBC SQL Server Driver][SQL Server]Your transaction(process ID#127)was deadlocked with another process and as been chosen as the deadlock victim. Return your transaction.

Some one please Help me on how to go abt the problem. Thanks in advance.
Regards
Dinesh1. Run sp_recompile 'object_name' for all objects used.
2. Post it all. SP+DDL

Good luck !

Deadlock victim (-2147467259)

Error number: -2147467259
Error description: Transaction (Process ID xxx) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
SQLState = 40001, Native Error = 1205.
When SQLServer reports this, it would be REALLY helpful if it also provided
the following information:
The SQL that was associated with this Process ID
The SQL that was associated with the Process ID that caused the deadlock
(but was not terminated).
This would greatly improve a developer's ability to debug this issue!
Ideally, released in a patch for SQLServer 2000....
This is a suggestion to Microsoft (vote for this if you agree)
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/communitie...qlserver.serverTry to turn trace flag 1204 on: DBCC TRACEON(1204).
That will cause SQL Server to write an extended info on every deadlock
situation to SQL Server error log. Hopefully, that's what you want
"Griff" <Griff@.discussions.microsoft.com> wrote in message
news:CB2097F1-E3C0-49B8-BF62-533A51932BAD@.microsoft.com...
> Error number: -2147467259
> Error description: Transaction (Process ID xxx) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
> SQLState = 40001, Native Error = 1205.
> When SQLServer reports this, it would be REALLY helpful if it also
> provided
> the following information:
> The SQL that was associated with this Process ID
> The SQL that was associated with the Process ID that caused the deadlock
> (but was not terminated).
> This would greatly improve a developer's ability to debug this issue!
> Ideally, released in a patch for SQLServer 2000....

Deadlock victim (-2147467259)

Error number: -2147467259
Error description: Transaction (Process ID xxx) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
SQLState = 40001, Native Error = 1205.
When SQLServer reports this, it would be REALLY helpful if it also provided
the following information:
The SQL that was associated with this Process ID
The SQL that was associated with the Process ID that caused the deadlock
(but was not terminated).
This would greatly improve a developer's ability to debug this issue!
Ideally, released in a patch for SQLServer 2000....
This is a suggestion to Microsoft (vote for this if you agree)
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/communities...lserver.server
Try to turn trace flag 1204 on: DBCC TRACEON(1204).
That will cause SQL Server to write an extended info on every deadlock
situation to SQL Server error log. Hopefully, that's what you want
"Griff" <Griff@.discussions.microsoft.com> wrote in message
news:CB2097F1-E3C0-49B8-BF62-533A51932BAD@.microsoft.com...
> Error number: -2147467259
> Error description: Transaction (Process ID xxx) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
> SQLState = 40001, Native Error = 1205.
> When SQLServer reports this, it would be REALLY helpful if it also
> provided
> the following information:
> The SQL that was associated with this Process ID
> The SQL that was associated with the Process ID that caused the deadlock
> (but was not terminated).
> This would greatly improve a developer's ability to debug this issue!
> Ideally, released in a patch for SQLServer 2000....

Deadlock victim (-2147467259)

Error number: -2147467259
Error description: Transaction (Process ID xxx) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
SQLState = 40001, Native Error = 1205.
When SQLServer reports this, it would be REALLY helpful if it also provided
the following information:
The SQL that was associated with this Process ID
The SQL that was associated with the Process ID that caused the deadlock
(but was not terminated).
This would greatly improve a developer's ability to debug this issue!
Ideally, released in a patch for SQLServer 2000....
This is a suggestion to Microsoft (vote for this if you agree)
--
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?mid=cb2097f1-e3c0-49b8-bf62-533a51932bad&dg=microsoft.public.sqlserver.serverTry to turn trace flag 1204 on: DBCC TRACEON(1204).
That will cause SQL Server to write an extended info on every deadlock
situation to SQL Server error log. Hopefully, that's what you want
"Griff" <Griff@.discussions.microsoft.com> wrote in message
news:CB2097F1-E3C0-49B8-BF62-533A51932BAD@.microsoft.com...
> Error number: -2147467259
> Error description: Transaction (Process ID xxx) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
> SQLState = 40001, Native Error = 1205.
> When SQLServer reports this, it would be REALLY helpful if it also
> provided
> the following information:
> The SQL that was associated with this Process ID
> The SQL that was associated with the Process ID that caused the deadlock
> (but was not terminated).
> This would greatly improve a developer's ability to debug this issue!
> Ideally, released in a patch for SQLServer 2000....sql

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

In a high traffic environment, deadlocks eventually occur as number of data processes increase. How can deadlocks be avoided, minimized and resolved. Please kindly provide scenario examples and samples of T-SQL code. Thanks much.

Check out this previous thread on deadlocks:http://forums.asp.net/862097/ShowPost.aspx. You might find something in there to help you, in particular this link:Tips for Reducing SQL Server Deadlocks.|||In your previous post, you said:
These 3 suggestions have all but eliminated deadlocking for me:
-- Keep transactions as short as possible
-- Reduce lock time
-- Consider using the NOLOCK hint
Did a full combination of the above eliminate deadlocking or not?
Please advise how to implement the 2nd and third suggestions with sample T-SQL code.
Thanks much.
|||

thuhue wrote:

In your previous post, you said:
These 3 suggestions have all but eliminated deadlocking for me:
-- Keep transactions as short as possible
-- Reduce lock time
-- Consider using the NOLOCK hint
Did a full combination of the above eliminate deadlocking or not?
Please advise how to implement the 2nd and third suggestions with sample T-SQL code.
Thanks much.

For my particular situation, yes, the above eliminated the deadlockproblems we were experiencing. #1 and #2 were for me the samething -- using transactions as late as possible and finishing them asearly as possible reduced the lock time and went a long way to reducingcontention. For the NOLOCK hint, this is how you use it:

SELECT someColumns FROM myTable WITH (NOLOCK) WHERE someCondition

See also:SQL Server Lock Contention Tamed: The Joys Of NOLOCK and ROWLOCK.

dbms_utility.get_time equivalent in sql server

Hi,
I need to convert the following oracle syntax to sql server:
timevar number;
timevar := dbms_utility.get_time;
This prints : 50989467
I aware of the function getDate() or current_timestamp but they display only the dates and if I convert them to float then the results are not same. Any pointers will be appreciated.
Srik

Hi,

I believe the dbms_utility.get_time routine is used mainly for timing loops. It returns the # of 100ths of a second since "some arbitrary epoch". Therefore, it doesn't have much meaning outside of marking elapsed time.

The SQL Server datetime data type has a resolution of 3.33 milliseconds. You can use the DATEDIFF() built-in function to return the elapsed # of milliseconds between two datetime values. Therefore, it may be something you can use in a way that is similar to Oracle's dbms_utility.get_time routine. Please note that the return type of the DATEDIFF() built-in function is a 32-bit signed integer, which means the the maximum possible elapsed time in millisecond units is equivalent to 24 days, 20 hours, 31 minutes and 23.647 seconds. Anything beyond that will cause an arithmetic overflow.

Here is a sample usage.

declare @.now datetime, @.then datetime
select @.then = getdate()
waitfor delay '000:00:02'
select @.now = getdate()
select datediff(millisecond, @.then, @.now)

Let us know if this answers your question and works for you.

Clifford Dibble
Program Manager, SQL Server

|||Thanks a lot for your response: I have a procedure which stores the dbms_utility.get_time into a localvariable and passes this variable to another procedure. For eg:
timevar number;
timevar := dbms_utility.get_time;
myproc(timevar);

any equivalent syntax will be useful.|||As far as I know, there is nothing directly equivalent to dbms_utility.get_timedbms_utility.getime in T-SQL. You will need to synthesize it using the technique shown in the prior post or you will need to cast the datetime bits using an expression like this:

select cast(cast(getdate() as binary(8)) as bigint)

Regards,
Clifford Dibble
Program Manager, SQL Server