Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Michael Peppler <mpeppler at MBAY dot NET>
Subject: Re: shared locks and connections
Date: Feb 27 1998 11:12PM

Chuck Griffith wrote:
> We are having a lot of problem with deadlocks. It seems to be involved
> with several programs that simply do selects and hence would only have
> shared locks. The program runs like this:
> startup
> Sybase::CTlib->ct_connect(...)
> $dbh->ct_execute( select ... from ... noholdlock...)
> while (($status = $dbh->ct_results($resultType)) == CS_SUCCEED) {
>   next unless ($dbh->ct_fetchable($resultType));
>   unless (defined($hash) and $hash == 0) {
>     my (%dat);
>     while ((%dat = $dbh->ct_fetch(1))) {
>       push @$list, { %dat };
>     }
>   }
> }
> build html
> end program
> 1. using noholdlock does not seem to be helping and we have a call into
>    Sybase as to why.

I've never used the noholdlock keyword (I'd never even heard about it)
but I've never seen this behaviour with 'select' type queries.

> 2. is it possible that even after retrieving the last row that the locks
>    are still being held? It seems to be true that if 'build html' takes
>    too long, the deadlocks increase. I suppose this could be that this
>    step is holding up other programs while they are retrieving rows and
>    those programs would be involved with deadlocks.

Well - theoretically as all the programs are doing is 'select's there
shouldn't be any lock contention at all. The issue is the programs that
you have that are writing to the same tables, and how they run.

> 3. would closing the connection after the last sql is finished help the
>    problem?

It might - but theoretically the lock should be released as soon as 
the result set has been processed, and your code looks
perfectly OK to me.

Michael Peppler         -||-  Data Migrations Inc.    -||-
Int. Sybase User Group  -||-