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.

Deadlock!

2 posts in General Discussion Last posting was on 2012-02-23 14:53:39.0Z
RGS Posted on 2012-02-23 13:48:45.0Z
Sender: 2677.4f4641f0.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Deadlock!
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f4643bd.26dc.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 23 Feb 2012 05:48:45 -0800
X-Trace: forums-1-dub 1330004925 172.20.134.41 (23 Feb 2012 05:48:45 -0800)
X-Original-Trace: 23 Feb 2012 05:48:45 -0800, 172.20.134.41
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30943
Article PK: 73833

Hi!

I am using ASE 15.5

Two questions about a deadlock situacion:

1) Can I turn on a parameter or something like that to force
ASE to always KILL a specific process in a DEADLOCK
situation? (I have one batch process and a lot on-line
processes and I need to kill always the batch process)

2) When I execute:

update table1 set field1 = field1 + @w_cont
where field2 in (select field3 from table2)

Can I LOCK all the pages involve in update for avoid a
DEADLOCK situation? Like the "HOLDLOCK" command?

Thanks!


Rob V Posted on 2012-02-23 14:53:39.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Deadlock!
References: <4f4643bd.26dc.1681692777@sybase.com>
In-Reply-To: <4f4643bd.26dc.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: <4f4652f3$1@forums-1-dub>
Date: 23 Feb 2012 06:53:39 -0800
X-Trace: forums-1-dub 1330008819 10.22.241.152 (23 Feb 2012 06:53:39 -0800)
X-Original-Trace: 23 Feb 2012 06:53:39 -0800, vip152.sybase.com
Lines: 51
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30944
Article PK: 73834


On 23-Feb-2012 14:48, RGS wrote:
> Hi!
>
> I am using ASE 15.5
>
> Two questions about a deadlock situacion:
>
> 1) Can I turn on a parameter or something like that to force
> ASE to always KILL a specific process in a DEADLOCK
> situation? (I have one batch process and a lot on-line
> processes and I need to kill always the batch process)

No, you cannot influence which of the sessions involved in the deadlock
has its transaction rolled back. ASE picks the session with the least
amount of CPU time used (which is, admittedly, an arbitrary criterium).

> 2) When I execute:
>
> update table1 set field1 = field1 + @w_cont
> where field2 in (select field3 from table2)
>
> Can I LOCK all the pages involve in update for avoid a
> DEADLOCK situation? Like the "HOLDLOCK" command?

Yes, you can use holdlock, but that does not guarantee you will not get
a deadlock. Holdlock will put a shared or update lock on all pages read,
which is then upgraded to an exclusive lock for the pages where an
update actually takes place. That still allows another session to take
shared locks at the same time.
If you want to guarantee no locking interference occurs, either use
"lock table" to lock the table exclusively before you run subsequent
update statements, or look into the 15.7 enhancement for "select...for
update".

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide" (new!)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------