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.

Enhancements - Print deadlock information

3 posts in Product Futures Discussion Last posting was on 2003-05-06 13:03:16.0Z
Dilip_Wadhwa Posted on 2003-05-05 18:38:07.0Z
From: Dilip_Wadhwa
Date: Mon, 5 May 2003 14:38:07 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Enhancements - Print deadlock information
Message-ID: <1F4943A0541C101000665DD285256D1D.00665DF285256D1D@webforums>
Lines: 74
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1200
Article PK: 95439

Hello,

I believe the deadlock information which is being printed in the sybase
errorlog file is not sufficient to identify the victim/culprit
applications, which were involved in deadlock cases. I wish if Sybase could
make the following enhancements: These enhancements will definitely save
hours of extra work.

[1]: print the hostprocess and program_name also which would really
help us at any point of time to identify the victim/culprit
application names, though Sybase do print the spid for victim/culprit
process but this is not sufficient as everytime DBA is not available to get
the correspoding hostprocess,program_name from master..sysprocesses and get
the application name with 'ps -ef' command. These extra details would
definitely help us to identify the victim/culprit application names for old
deadlock cases also for which the information is still available in sybase
errorlog file.

[2]: Print the timestamp in each row , instead of printing only on first
row. The first row for each deadlock Id prints the timestamp but the
detailed rows for the correspoding deadlock Id doesn't print the timestamp.
If timestamp is available in each row, we can easily generate a report with
'nawk' which will contain only the timestamp and thetable_name.
06:00000:00824:2003/04/24 00:00:50.70 server Deadlock Id 1131 detected)
Deadlock Id 1131: Process (Familyid 824 824) (suid 13) was executing a
UPDATE command in the procedure 'objid -54002120'.
Deadlock Id 1131: Process (Familyid 63 63) (suid 13) was executing a UPDATE
command in the procedure 'objid -4129224'.
Deadlock Id 1131: Process (Familyid 0, Spid 63) was waiting for a
'exclusive row' lock on row 2 page 17303527 of the 'TCFPROOF' t
able in database 6 but process (Familyid 824, Spid 824) already held a
'shared row' 'range' lock on it.


For example, I had written a script which scan the huge sybase errorlog
file (about 400,000 records) row by row and generate the following summary
report which prints timestamp, deadlock id ,table_name and spid. Naturally,
the script will take a long time to generate a report like this. After
generating this report,I still do not know the application names which were
involved in deadlock cases and for that I need to run a script around the
clock which keep on collecting the sybase process details from sysprocesses
and stores the output in a output file and again another script which keep
on collecting the Unix process details (i.e application name) with 'ps -ef'
command, so much extra work is involved in order to identity the deadlock
cases. With the above enhancement, we can easily reduce the extra efforts.

02:00000:00824:2003/04/24 00:00:51.70 server Deadlock Id 1132 detected
'TCAONMAS' 63)
03:00000:00063:2003/04/24 00:00:52.70 server Deadlock Id 1133 detected
'TCAONMAS' 824)
04:00000:00430:2003/04/24 01:05:02.40 server Deadlock Id 1136 detected
'TCAONMAS' 1015)
05:00000:01015:2003/04/24 01:05:03.41 server Deadlock Id 1137 detected
'TCAONMAS' 430)
08:00000:00384:2003/04/24 06:32:35.91 server Deadlock Id 1143 detected
'TPENDFIN_0' 301)
08:00000:00384:2003/04/24 06:32:35.91 server Deadlock Id 1143 detected
'TPENDFIN_0' 384)
05:00000:00329:2003/04/24 09:27:21.61 server Deadlock Id 1147 detected
'TGLORGBUS' 948)
01:00000:00865:2003/04/24 09:45:38.00 server Deadlock Id 1150 detected
'TPENDFIN_0' 177)
01:00000:00865:2003/04/24 09:45:38.00 server Deadlock Id 1150 detected
'TPENDFIN_0' 865)
05:00000:00609:2003/04/24 09:50:25.60 server Deadlock Id 1151 detected
'TPENDFIN_0' 177)
05:00000:00609:2003/04/24 09:50:25.60 server Deadlock Id 1151 detected
'TPENDFIN_0' 609)
03:00000:00177:2003/04/24 09:51:16.11 server Deadlock Id 1152 detected
'TCAONMAS' 177)
03:00000:00177:2003/04/24 09:51:16.11 server Deadlock Id 1152 detected
'TPENDFIN_0' 174)

Thanks,
Dilip


Aditya Gurajada Posted on 2003-05-06 06:38:59.0Z
Message-ID: <3EB75883.4799FA04@Do-Not-sybase-SpAM-.com>
Date: Mon, 05 May 2003 23:38:59 -0700
From: Aditya Gurajada <aditya.gurajada@Do-Not-sybase-SpAM-.com>
Reply-To: aditya.gurajada@Do-Not-sybase-SpAM-.com
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.5 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Enhancements - Print deadlock information
References: <1F4943A0541C101000665DD285256D1D.00665DF285256D1D@webforums>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: vpn-dub-121.sybase.com 10.22.120.121
Lines: 51
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1201
Article PK: 95441

Hi Dilip,

Your requests seem simple enough, and I'll take that back via a CR so that
we can consider some changes. The 1st one is not that hard, but about the 2nd
one -- there might be other scripts out there that already cope with the
current
output layout. Changing that might break other such grep'ping scripts/tools.
We always wonder what impact such a change in output might make. So not
sure that this can be done unconditionally, without some form of feedback from
users that adding the timestamp line would be ok.

Btw -- about generating the report you wanted, what does this nawk script
do more than a grep? Also, did you consider using something like Gnu e/grep,
which allows for a # of context lines after the matching pattern. You could
also
probably run that, collect the output to a file, and post-process that to do
additional filtering. (Just some ideas ... maybe to reduce your script's execn
time.)

-- Aditya.

Dilip_Wadhwa wrote:

> Hello,
>
> I believe the deadlock information which is being printed in the sybase
> errorlog file is not sufficient to identify the victim/culprit
> applications, which were involved in deadlock cases. I wish if Sybase could
> make the following enhancements: These enhancements will definitely save
> hours of extra work.
>
> [1]: print the hostprocess and program_name also which would really
> help us at any point of time to identify the victim/culprit
> application names, though Sybase do print the spid for victim/culprit
> process but this is not sufficient as everytime DBA is not available to get
> the correspoding hostprocess,program_name from master..sysprocesses and get
> the application name with 'ps -ef' command. These extra details would
> definitely help us to identify the victim/culprit application names for old
> deadlock cases also for which the information is still available in sybase
> errorlog file.
>
> [2]: Print the timestamp in each row , instead of printing only on first
> row.


Dilip_Wadhwa Posted on 2003-05-06 13:03:16.0Z
From: Dilip_Wadhwa
Date: Tue, 6 May 2003 09:03:16 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Enhancements - Print deadlock information
Message-ID: <D182845A177437230047B5D985256D1E.0029B33F85256D1E@webforums>
References: <1F4943A0541C101000665DD285256D1D.00665DF285256D1D@webforums> <3EB75883.4799FA04@Do-Not-sybase-SpAM-.com>
Lines: 9
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1204
Article PK: 95443

Hello Aditya,

Thanks for your response and recommendation. I'm glad that Sybase accepted
my first enhancement request though we dont know when it will be
incorporated.

Cheers,
Dilip