Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Crazy Deadlocks

3 posts in General Discussion Last posting was on 2011-03-11 14:49:53.0Z
RGS Posted on 2011-03-10 19:18:55.0Z
Sender: 6bf9.4d750e00.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Crazy Deadlocks
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d79241f.108a.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 10 Mar 2011 11:18:55 -0800
X-Trace: forums-1-dub 1299784735 10.22.241.41 (10 Mar 2011 11:18:55 -0800)
X-Original-Trace: 10 Mar 2011 11:18:55 -0800, 10.22.241.41
Lines: 126
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30036
Article PK: 79266

Hi!

I have problems with two reports of deadlocks:

1) DEADLOCK ON TABLE ca_diviendo_tmp

The report is:

***
Deadlock Id 1460: Process (Familyid 0, Spid 1649, Suid 90)
was executing a SELECT command in the procedure
'sp_comision_fng' at line 169 at nested level 10.
Deadlock Id 1460: Process 1649 was involved in application
'EXECOB'.
Deadlock Id 1460: Process 1649 was involved on host name
'FNABOGPROD1'.
Deadlock Id 1460: Process 1649 was involved in
multi-database transaction '$user_transaction'.
SQL Text:
Executing procedure: sp_comision_fng
Subordinate SQL Text: select @w_saldo_cap_mn =
isnull(@w_saldo_cap_mn, 0) * cot_valor
from ca_cotizacion_tmp,
ca_dividendo_tmp
where cot_fecha_proc = dit_fecha_ini
and dit_dividendo = @i_dividendo
and cot_moneda = @w_moneda_op
and dit_operacion = @i_operacionca

Deadlock Id 1460: Process (Familyid 0, Spid 1953, Suid 90)
was executing a SELECT command in the procedure
'sp_comision_fng' at line 169 at nested level 10.
Deadlock Id 1460: Process 1953 was involved in application
'EXECOB'.
Deadlock Id 1460: Process 1953 was involved on host name
'FNABOGPROD1'.
Deadlock Id 1460: Process 1953 was involved in
multi-database transaction '$user_transaction'.
SQL Text:
Executing procedure: sp_comision_fng
Subordinate SQL Text: select @w_saldo_cap_mn =
isnull(@w_saldo_cap_mn, 0) * cot_valor
from ca_cotizacion_tmp,
ca_dividendo_tmp
where cot_fecha_proc = dit_fecha_ini
and dit_dividendo = @i_dividendo
and cot_moneda = @w_moneda_op
and dit_operacion = @i_operacionca

Deadlock Id 1460: Process (Familyid 0, Spid 1953) was
waiting for a 'shared row' lock on row 19 page 668 of the
'ca_dividendo_tmp' table in database 'cob_cartera' but
process (Familyid 0, Spid 1649) already held a 'exclusive
row' lock on it.
Deadlock Id 1460: Process (Familyid 0, Spid 1649) was
waiting for a 'shared row' lock on row 10 page 668 of the
'ca_dividendo_tmp' table in database 'cob_cartera' but
process (Familyid 0, Spid 1953) already held a 'exclusive
row' lock on it.
***

Table ca_dividendo_tmp has a DATAROWS lock and it has a
unique index for fields: dit_operacion, dit_dividendo

I don't understand the report, both processes are executing
a SELECT command (shared lock), but report says "exclusive
lock" ???


2) DEADLOCK ON TABLE ca_rubro_op

The report says:

Deadlock Id 1461: Process (Familyid 0, Spid 1201, Suid 132)
was executing a UPDATE command in the procedure
'sp_correccion_monetaria' at line 809 at nested level 4.
Deadlock Id 1461: Process 1201 was involved in application
'w3wp.exe'.
Deadlock Id 1461: Process 1201 was involved on host name ''.
Deadlock Id 1461: Process 1201 was involved in
multi-database transaction '$user_transaction'.
SQL Text:
Executing procedure: sp_correccion_monetaria
Subordinate SQL Text: update ca_rubro_op
set ro_fecha_crm = @i_fecha_proceso
where ro_operacion = @i_operacionca
and ro_concepto = @w_concepto


Deadlock Id 1461: Process (Familyid 0, Spid 1389, Suid 130)
was executing a UPDATE command in the procedure
'sp_correccion_monetaria' at line 809 at nested level 5.
Deadlock Id 1461: Process 1389 was involved in application
'COBISTS'.
Deadlock Id 1461: Process 1389 was involved on host name
'FNABOGWASR'.
Deadlock Id 1461: Process 1389 was involved in
multi-database transaction '$user_transaction'.
SQL Text:
Executing procedure: sp_correccion_monetaria
Subordinate SQL Text: update ca_rubro_op
set ro_fecha_crm = @i_fecha_proceso
where ro_operacion = @i_operacionca
and ro_concepto = @w_concepto


Deadlock Id 1461: Process (Familyid 0, Spid 1389) was
waiting for a 'exclusive row' lock on row 2 page 66315660 of
the 'ca_rubro_op' table in database 'cob_cartera' but
process (Familyid 0, Spid 1201) already held a 'shared row'
lock on it.
Deadlock Id 1461: Process (Familyid 0, Spid 1201) was
waiting for a 'exclusive row' lock on row 2 page 66315660 of
the 'ca_rubro_op' table in database 'cob_cartera' but
process (Familyid 0, Spid 1389) already held a 'shared row'
lock on it.


Table ca_rubro_op has a DATAROWS LOCK and it has a unique
index for fields: ro_operacion, ro_concepto

Reports says: "lock on row 2 page 66315660" for both
processes, it means that both processes are updating the
same record (ro_operacion,ro_concepto)?

Thanks!!!


Bret Halford Posted on 2011-03-10 23:46:16.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.15) Gecko/20110303 Thunderbird/3.1.9
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Crazy Deadlocks
References: <4d79241f.108a.1681692777@sybase.com>
In-Reply-To: <4d79241f.108a.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d7962c8$1@forums-1-dub>
Date: 10 Mar 2011 15:46:16 -0800
X-Trace: forums-1-dub 1299800776 10.22.241.152 (10 Mar 2011 15:46:16 -0800)
X-Original-Trace: 10 Mar 2011 15:46:16 -0800, vip152.sybase.com
Lines: 37
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30037
Article PK: 79265


On 3/10/2011 12:18 PM, RGS wrote:

> I don't understand the report, both processes are executing
> a SELECT command (shared lock), but report says "exclusive
> lock" ???
>

The process is currently executing a select, but could
have executed an update or delete earlier in a multi-statement
transaction.

Things can also read a little weird when there are exclusive
lock requests from some process being blocked by a shared
lock. To avoid what is called "starving writer syndrome",
ASE only allows a couple of of subsequent shared lock requests
to bypass the blocked exclusive lock requests. After that
additional shared lock requests start blocking on the pending
exclusive lock request. Although the granted lock is a shared
lock, you can thus sometimes see shared locks blocking on
a shared lock, and the blocker will sometimes be reported
as being an exclusive lock because the blocked shared
request is actually queued behind the exclusive lock.




> Reports says: "lock on row 2 page 66315660" for both
> processes, it means that both processes are updating the
> same record (ro_operacion,ro_concepto)?
>
> Thanks!!!

Yes. Both processes have read the row and both
of them now want to update the row. One of them
must give up their hold on the shared lock before
an exclusive lock can be granted.


RGS Posted on 2011-03-11 14:49:53.0Z
Sender: 6bf9.4d750e00.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Re: Crazy Deadlocks
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d7a3691.2d26.1681692777@sybase.com>
References: <4d7962c8$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 11 Mar 2011 06:49:53 -0800
X-Trace: forums-1-dub 1299854993 10.22.241.41 (11 Mar 2011 06:49:53 -0800)
X-Original-Trace: 11 Mar 2011 06:49:53 -0800, 10.22.241.41
Lines: 41
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30038
Article PK: 79267

Thanks a lot!!!

> On 3/10/2011 12:18 PM, RGS wrote:
>
> > I don't understand the report, both processes are
> > executing a SELECT command (shared lock), but report
> > says "exclusive lock" ???
> >
>
>
> The process is currently executing a select, but could
> have executed an update or delete earlier in a
> multi-statement transaction.
>
> Things can also read a little weird when there are
> exclusive lock requests from some process being blocked by
> a shared lock. To avoid what is called "starving writer
> syndrome", ASE only allows a couple of of subsequent
> shared lock requests to bypass the blocked exclusive lock
> requests. After that additional shared lock requests
> start blocking on the pending exclusive lock request.
> Although the granted lock is a shared lock, you can thus
> sometimes see shared locks blocking on a shared lock, and
> the blocker will sometimes be reported as being an
> exclusive lock because the blocked shared request is
> actually queued behind the exclusive lock.
>
>
>
>
> > Reports says: "lock on row 2 page 66315660" for both
> > processes, it means that both processes are updating the
> > same record (ro_operacion,ro_concepto)?
> >
> > Thanks!!!
>
> Yes. Both processes have read the row and both
> of them now want to update the row. One of them
> must give up their hold on the shared lock before
> an exclusive lock can be granted.