deAttach database: processes

Showing posts with label processes. Show all posts

Thursday, March 29, 2012

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

Tuesday, March 27, 2012

Deadlocks & Transaction Isolation Level

If I understand correctly, when using a Read Committed isolation level, the
most common reason for a deadlock is because two processes update a set of
tables in different order. However, it seems that when using a Repeatable
Read isolation level, the odds of a deadlock increase significantly.
For example, open Management Studio and create two different connections
against the AdventureWorks database. In both connections execute the
following:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Begin Tran
SELECT EmployeeID From HumanResources.Employee Where EmployeeID = 1
Then, in the first connection execute the following (but do not commit the
transaction):
Update HumanResources.Employee Set MaritalStatus = 'M' Where EmployeeID = 1
In the second connection execute the exact same line. This will cause a
deadlock error in the second connection. We get a deadlock even though both
processes are performing the exact same action in the exact same order.
This example may not be the best but is my assumption correct that when
using Repeatable Read, the likelihood of a deadlock error is greater than
when using Read Committed?
Thanks, Amos.
> when using a Read Committed isolation level, the
> most common reason for a deadlock is because two processes
> update a set of tables in different order.
not exactly. 2 processses may update rows in only one table and still
clinch in a deadlock.
|||Hi Amos
Yes, your understanding is correct. Using a higher isolation level like
repeatable read has tradeoffs.
In read committed the locks on the SELECT would be released as soon as the
SELECT was finished. In repeatable read, the SELECT (shared) locks are not
released. The good news is that each transaction is guaranteed to read the
same data throughout the transaction. The bad news is there is a greater
chance of deadlock. Each connection has a shared lock on the row in the
Employee table, and wants an exclusive lock. Neither can get the exclusive
lock because the other has the shared lock, so you have deadlock.
One of the first suggestions we give to try to reduce deadlock is to reduce
your isolation level; in this case, bring it back to read committed.
Another solution here would be to use an UPDLOCK hint when you do the
select. Then the first process would get an update lock, not a shared lock,
and when the second process tried to get the update lock, it would be
blocked. The first process could then get the exclusive lock and do the
update operation, and finish the transaction. Then the second process could
get first the update lock, then the exclusive lock, and then finish, with no
deadlock occurring.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:%23$22cVEKGHA.3960@.TK2MSFTNGP09.phx.gbl...
> If I understand correctly, when using a Read Committed isolation level,
> the most common reason for a deadlock is because two processes update a
> set of tables in different order. However, it seems that when using a
> Repeatable Read isolation level, the odds of a deadlock increase
> significantly.
> For example, open Management Studio and create two different connections
> against the AdventureWorks database. In both connections execute the
> following:
> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
> Begin Tran
> SELECT EmployeeID From HumanResources.Employee Where EmployeeID = 1
>
> Then, in the first connection execute the following (but do not commit the
> transaction):
> Update HumanResources.Employee Set MaritalStatus = 'M' Where EmployeeID =
> 1
> In the second connection execute the exact same line. This will cause a
> deadlock error in the second connection. We get a deadlock even though
> both processes are performing the exact same action in the exact same
> order.
> This example may not be the best but is my assumption correct that when
> using Repeatable Read, the likelihood of a deadlock error is greater than
> when using Read Committed?
> Thanks, Amos.
>
>

Deadlocks & Transaction Isolation Level

If I understand correctly, when using a Read Committed isolation level, the
most common reason for a deadlock is because two processes update a set of
tables in different order. However, it seems that when using a Repeatable
Read isolation level, the odds of a deadlock increase significantly.
For example, open Management Studio and create two different connections
against the AdventureWorks database. In both connections execute the
following:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Begin Tran
SELECT EmployeeID From HumanResources.Employee Where EmployeeID = 1
Then, in the first connection execute the following (but do not commit the
transaction):
Update HumanResources.Employee Set MaritalStatus = 'M' Where EmployeeID = 1
In the second connection execute the exact same line. This will cause a
deadlock error in the second connection. We get a deadlock even though both
processes are performing the exact same action in the exact same order.
This example may not be the best but is my assumption correct that when
using Repeatable Read, the likelihood of a deadlock error is greater than
when using Read Committed?
Thanks, Amos.> when using a Read Committed isolation level, the
> most common reason for a deadlock is because two processes
> update a set of tables in different order.
not exactly. 2 processses may update rows in only one table and still
clinch in a deadlock.|||Hi Amos
Yes, your understanding is correct. Using a higher isolation level like
repeatable read has tradeoffs.
In read committed the locks on the SELECT would be released as soon as the
SELECT was finished. In repeatable read, the SELECT (shared) locks are not
released. The good news is that each transaction is guaranteed to read the
same data throughout the transaction. The bad news is there is a greater
chance of deadlock. Each connection has a shared lock on the row in the
Employee table, and wants an exclusive lock. Neither can get the exclusive
lock because the other has the shared lock, so you have deadlock.
One of the first suggestions we give to try to reduce deadlock is to reduce
your isolation level; in this case, bring it back to read committed.
Another solution here would be to use an UPDLOCK hint when you do the
select. Then the first process would get an update lock, not a shared lock,
and when the second process tried to get the update lock, it would be
blocked. The first process could then get the exclusive lock and do the
update operation, and finish the transaction. Then the second process could
get first the update lock, then the exclusive lock, and then finish, with no
deadlock occurring.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:%23$22cVEKGHA.3960@.TK2MSFTNGP09.phx.gbl...
> If I understand correctly, when using a Read Committed isolation level,
> the most common reason for a deadlock is because two processes update a
> set of tables in different order. However, it seems that when using a
> Repeatable Read isolation level, the odds of a deadlock increase
> significantly.
> For example, open Management Studio and create two different connections
> against the AdventureWorks database. In both connections execute the
> following:
> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
> Begin Tran
> SELECT EmployeeID From HumanResources.Employee Where EmployeeID = 1
>
> Then, in the first connection execute the following (but do not commit the
> transaction):
> Update HumanResources.Employee Set MaritalStatus = 'M' Where EmployeeID => 1
> In the second connection execute the exact same line. This will cause a
> deadlock error in the second connection. We get a deadlock even though
> both processes are performing the exact same action in the exact same
> order.
> This example may not be the best but is my assumption correct that when
> using Repeatable Read, the likelihood of a deadlock error is greater than
> when using Read Committed?
> Thanks, Amos.
>
>

Deadlocks & Transaction Isolation Level

If I understand correctly, when using a Read Committed isolation level, the
most common reason for a deadlock is because two processes update a set of
tables in different order. However, it seems that when using a Repeatable
Read isolation level, the odds of a deadlock increase significantly.
For example, open Management Studio and create two different connections
against the AdventureWorks database. In both connections execute the
following:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Begin Tran
SELECT EmployeeID From HumanResources.Employee Where EmployeeID = 1
Then, in the first connection execute the following (but do not commit the
transaction):
Update HumanResources.Employee Set MaritalStatus = 'M' Where EmployeeID = 1
In the second connection execute the exact same line. This will cause a
deadlock error in the second connection. We get a deadlock even though both
processes are performing the exact same action in the exact same order.
This example may not be the best but is my assumption correct that when
using Repeatable Read, the likelihood of a deadlock error is greater than
when using Read Committed?
Thanks, Amos.> when using a Read Committed isolation level, the
> most common reason for a deadlock is because two processes
> update a set of tables in different order.
not exactly. 2 processses may update rows in only one table and still
clinch in a deadlock.|||Hi Amos
Yes, your understanding is correct. Using a higher isolation level like
repeatable read has tradeoffs.
In read committed the locks on the SELECT would be released as soon as the
SELECT was finished. In repeatable read, the SELECT (shared) locks are not
released. The good news is that each transaction is guaranteed to read the
same data throughout the transaction. The bad news is there is a greater
chance of deadlock. Each connection has a shared lock on the row in the
Employee table, and wants an exclusive lock. Neither can get the exclusive
lock because the other has the shared lock, so you have deadlock.
One of the first suggestions we give to try to reduce deadlock is to reduce
your isolation level; in this case, bring it back to read committed.
Another solution here would be to use an UPDLOCK hint when you do the
select. Then the first process would get an update lock, not a shared lock,
and when the second process tried to get the update lock, it would be
blocked. The first process could then get the exclusive lock and do the
update operation, and finish the transaction. Then the second process could
get first the update lock, then the exclusive lock, and then finish, with no
deadlock occurring.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:%23$22cVEKGHA.3960@.TK2MSFTNGP09.phx.gbl...
> If I understand correctly, when using a Read Committed isolation level,
> the most common reason for a deadlock is because two processes update a
> set of tables in different order. However, it seems that when using a
> Repeatable Read isolation level, the odds of a deadlock increase
> significantly.
> For example, open Management Studio and create two different connections
> against the AdventureWorks database. In both connections execute the
> following:
> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
> Begin Tran
> SELECT EmployeeID From HumanResources.Employee Where EmployeeID = 1
>
> Then, in the first connection execute the following (but do not commit the
> transaction):
> Update HumanResources.Employee Set MaritalStatus = 'M' Where EmployeeID =
> 1
> In the second connection execute the exact same line. This will cause a
> deadlock error in the second connection. We get a deadlock even though
> both processes are performing the exact same action in the exact same
> order.
> This example may not be the best but is my assumption correct that when
> using Repeatable Read, the likelihood of a deadlock error is greater than
> when using Read Committed?
> Thanks, Amos.
>
>

DEADLOCKS

Hi:
I have a .net web application and I am getting a lot of deadlocks. My
system processes a lot of real-time transactions daily. In addition ,
there are like 3 DTS packages running. The problem is that I am getting a
lot of deadlock and based on the history of the pakages I would say that is
happening because the packages take the control and kill the processes that
are tying to run at the same time.
Is there a way to try to reduce this dead-lock assigning especific features
to the packages.
ThanksI don't think there are any issues specific to DTS packages that cause
deadlocking. A package could be excuting almost anything, so provide more
details about what type of selects / updates / inserts the package is
performing and also the nature of the transactions that are being blocked.
Read up in Books Online about "set transaction isolation level". Consider
using a lower isolation level, like read uncommitted. Basically, this allows
a process to read data currently locked by another transaction. Just
understand what "dirty reads", "nonrepeatable reads", etc. are and whether
or not they would present a significant problem in the specific case of your
queries.
Also, for what it's worth, here are my bookmarks for SQL Server deadlocking:
Deadlocking
http://msdn.microsoft.com/library/d... />
a_8i93.asp
INF: Analyzing and Avoiding Deadlocks in SQL Server
http://support.microsoft.com/defaul...kb;en-us;169960
Tracing Deadlocks
http://www.sqlservercentral.com/col...ngdeadlocks.asp
Minimizing Deadlocks
http://msdn.microsoft.com/library/d... />
a_3hdf.asp
SQL Server technical bulletin - How to resolve a deadlock
http://support.microsoft.com/defaul...kb;en-us;832524
"Gina Hernandez" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:%23OfWoX19FHA.4004@.TK2MSFTNGP14.phx.gbl...
> Hi:
> I have a .net web application and I am getting a lot of deadlocks. My
> system processes a lot of real-time transactions daily. In addition ,
> there are like 3 DTS packages running. The problem is that I am getting
> a lot of deadlock and based on the history of the pakages I would say that
> is happening because the packages take the control and kill the processes
> that are tying to run at the same time.
> Is there a way to try to reduce this dead-lock assigning especific
> features to the packages.
> Thanks
>

Sunday, March 25, 2012

deadlock victim

h
im the below output from profiler..processes r getting deadlock and most of the time the below processes spid is causing blocking..but im unable to get procedure associated with that...
RPC Event 0 sp_executesql;
wot is sp_executesql;1 ?There are some trace falgs you can turn on to help resolve deadlocking
problems.
DBCC TRACEON (1204, 3605,-1)
will output info into your SQL Server ErrorLog.
--
HTH
Ryan Waight, MCDBA, MCSE
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:B1BF5BC8-8889-4054-9FDD-1FE3B52418B9@.microsoft.com...
> hi
> im the below output from profiler..processes r getting deadlock and most
of the time the below processes spid is causing blocking..but im unable to
get procedure associated with that...
> RPC Event 0 sp_executesql;1
> wot is sp_executesql;1 ?
>|||h
thnks for yr reply..but i failied to identify which the procedure associated with this...RPC Event 0 sp_executesql;
this "RPC Event 0 sp_executesql;1" im in getting profiler as deadlock processes

Wednesday, March 21, 2012

deadlock on a single table but multiple processes

Hi! We have a third party application that calls same stored procedure
simultaneously (around 10 spids). We are seeing hundreds of deadlocks.
Deadlock trace shows both spids are running exactly same statement within
the procedure. Depending upon input parameter the statement does either
insert or update. But the deadlock trace shows that deadlock happens when
both are running update statements. Multiple thread supposed to update same
table but different rows (at most couple of rows).
The object (key) they are deadlocking on is a non clustered index used to
search data for update. Update statement doesn't modify any column that
belongs to this non clustered index. Database is running on default
(read_commited) mode and Its sql 2000 SP4. I haven't seen "begin tran" in
the stored procedrue, so I assume that the statement is not a part of
explicit transaction.
Questions:
1. Why sql server is using update lock (And not the shared lock) on the non
clustered index which used to search the data. The update statement doesn't
modify this non clustered index. In below statement Index id 5 is on
position_id, security_alias and long_short_indicator.
2. Why deadlock and not just blocking? What is a fix for this?
Below is the update_statement that both SPID are running:
UPDATE CA
SET CANCEL_STATUS = 'Y',
UPDATE_SOURCE = @.in_update_source,
UPDATE_DATE = GETDATE()
from CASH.DBO.CASH_ACTIVITY CA (index(IND_CASH_ACT_SPD1))
WHERE POSITION_ID = @.nTargetPositionId
AND SECURITY_ALIAS = @.in_security_alias
AND LONG_SHORT_INDICATOR = 'L'
AND SOURCE_SECURITY_ALIAS = @.in_source_security_alias
AND SOURCE_LONG_SHORT_IND = @.in_source_long_short_ind
AND STAR_TAG25 = @.in_event_id
AND CASH_BAL_INST = @.in_event_sequence
AND CANCEL_FLAG = 'N'
AND REFLEXIVE_FLOW = 'Y'
Below is output of deadlock trace:
Deadlock encountered ... Printing deadlock information
2007-12-20 07:54:11.27 spid1
2007-12-20 07:54:11.27 spid1 Wait-for graph
2007-12-20 07:54:11.27 spid1
2007-12-20 07:54:11.27 spid1 Node:1
2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (5d01ef3a25c6)
CleanCnt:2 Mode: X Flags: 0x0
2007-12-20 07:54:11.27 spid1 Grant List 3::
2007-12-20 07:54:11.27 spid1 Owner:0x3dfb4480 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:589 ECID:0
2007-12-20 07:54:11.27 spid1 SPID: 589 ECID: 0 Statement Type: UPDATE
Line #: 42
2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
PACE_MASTER..INSERT_CASH_ACTIVITY;1
2007-12-20 07:54:11.27 spid1 Requested By:
2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
SPID:470 ECID:0 Ec

0x72B99520) Value:0xb61fa660 Cost

0/7280)
2007-12-20 07:54:11.27 spid1
2007-12-20 07:54:11.27 spid1 Node:2
2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (d5013fde36a9)
CleanCnt:2 Mode: U Flags: 0x0
2007-12-20 07:54:11.27 spid1 Grant List 2::
2007-12-20 07:54:11.27 spid1 Owner:0xb69c32c0 Mode: U Flg:0x0
Ref:0 Life:00000001 SPID:470 ECID:0
2007-12-20 07:54:11.27 spid1 SPID: 470 ECID: 0 Statement Type: UPDATE
Line #: 42
2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
PACE_MASTER..INSERT_CASH_ACTIVITY;1
2007-12-20 07:54:11.27 spid1 Requested By:
2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
SPID:589 ECID:0 Ec