sleeping processes holding locks 
Author Message
 sleeping processes holding locks

Well, we've had an open call with Sybase tech (alleged) support for
over two months now with not a hint of resolution for this problem
so I'd thought I'd check and see if anyone else has seen anything like
this:

Back end is Sybase 10.0.2 on an Alpha OSF/1 (Digital Unix).
Front end is Sybase APT 5.2.2 EBF2599 on Vax VMS.

The symptom which is not reproducible but happens fairly frequently
is that a process will go into receive sleep mode (awaiting command)
but won't release all of the locks it was holding.  Eventually
enough people lock sleep on it that someone screams for help
(or sometimes we notice this doing sp_locks or something) and
that process has to be killed.  Now, one of the things is that
this process is *not*{*filter*} from the client point of view
 -- in fact, one of the solutions can be to ask that person
to exit from the application in which case when the process goes
away the locks get released.

This has turned into a nightmare for us since someone always has to be
on-line monitoring the production system to catch these before they
trip up too many other people.

Anyone else seen this?  Is this an APT problem?  A lock manager problem
in system 10?  What about sybase tech support: is this how they usually
handle a problem they don't know the answer to?  Should I expect them
to ever find a solution, or what?
--
                        __o
Asya Kamsky             \<_       Ride.  Eat.  Sleep.    California AIDS Ride 2
Santa Cruz, CA       ( )/( )        Ride.  Eat.  Sleep.   1000/525/7  May 14-20



Mon, 02 Mar 1998 03:00:00 GMT
 sleeping processes holding locks


Quote:

>Back end is Sybase 10.0.2 on an Alpha OSF/1 (Digital Unix).
>Front end is Sybase APT 5.2.2 EBF2599 on Vax VMS.

>The symptom which is not reproducible but happens fairly frequently
>is that a process will go into receive sleep mode (awaiting command)
>but won't release all of the locks it was holding.  Eventually
>enough people lock sleep on it that someone screams for help
>(or sometimes we notice this doing sp_locks or something) and
>that process has to be killed.  Now, one of the things is that
>this process is *not*{*filter*} from the client point of view
> -- in fact, one of the solutions can be to ask that person
>to exit from the application in which case when the process goes
>away the locks get released.

>This has turned into a nightmare for us since someone always has to be
>on-line monitoring the production system to catch these before they
>trip up too many other people.

I don't think it's a bug in Sybase.

All of your description fit into one senario:  the application didn't
`commit' all the way to the outer-most transaction at some point.  That is,
the whole thing after that point is an outstanding transaction.

Your APT app (or probably even your trigger or stored proc) forgot to
'commit/rollback tran' for some 'begin tran' statement.  I'll bet that
when your trouble-maker logs out and hence releases all the locks, he
actually loses all his work as well.  That's because the server implicitly
rollback the transaction if the user logs off in the middle of an
outstanding transaction.

To debug your app, you usually need to know the exact sequence of actions
your user had taken, and follow your application to hunt down the missing
'commit/rollback'.  For example, did you only print a message "successfully
saved" without indeed commit'ing the transaction?

Here's a little suggestion to make your application safer:
Even after you find out the missing commit, you're probably still not sure
whether the bug exists elsewhere as well.  That's why I adopt a defensive
programming style.  If I can't gurantee bugs don't exist, I could at least
be pretty sure to stop it from spreading to the rest of the application.
To do that, every time I commit a transaction, I do

instead of just
        commit tran
That way I at least know that if for somereason I missed a commit before this
point, the damage it causes stops here.  

On the other hand, whenever I issue a 'begin tran' from APT, I do

        begin
            raiserror 99999 "Did you forget to commit something?"
            rollback tran /* or whatever */
            return
        end
        else
        begin
            begin tran
        end

instead of simply

        begin tran

Buy putting the above into a "libbegintran_1.fpl" file, I would just do
        call libbegintran_1()
everytime I want to begin a transaction.

--

Q Vincent Yin                   |       Repeat

                                |       Until 0 = 1;



Mon, 02 Mar 1998 03:00:00 GMT
 sleeping processes holding locks

Quote:

>Well, we've had an open call with Sybase tech (alleged) support for
>over two months now with not a hint of resolution for this problem
>so I'd thought I'd check and see if anyone else has seen anything like
>this:

>Back end is Sybase 10.0.2 on an Alpha OSF/1 (Digital Unix).
>Front end is Sybase APT 5.2.2 EBF2599 on Vax VMS.

>The symptom which is not reproducible but happens fairly frequently
>is that a process will go into receive sleep mode (awaiting command)
>but won't release all of the locks it was holding.  Eventually
>enough people lock sleep on it that someone screams for help
>(or sometimes we notice this doing sp_locks or something) and
>that process has to be killed.  Now, one of the things is that
>this process is *not*{*filter*} from the client point of view
> -- in fact, one of the solutions can be to ask that person
>to exit from the application in which case when the process goes
>away the locks get released.

>This has turned into a nightmare for us since someone always has to be
>on-line monitoring the production system to catch these before they
>trip up too many other people.

>Anyone else seen this?  Is this an APT problem?  A lock manager problem
>in system 10?  What about sybase tech support: is this how they usually
>handle a problem they don't know the answer to?  Should I expect them
>to ever find a solution, or what?
>--
>                        __o
>Asya Kamsky             \<_       Ride.  Eat.  Sleep.    California AIDS Ride 2
>Santa Cruz, CA       ( )/( )        Ride.  Eat.  Sleep.   1000/525/7  May 14-20

Hi Asya,

Unfortunately, we hit your problem too. Our back end is Sybase 10.0.2 with no EBF, the front
end is also APT 5.2.2 but on AIX 3.2.5 platform. It seems not the APT problem, and it should
be the problem of back end. i.e. Sybase 10.0.2 engine. 'Cause we do not have this problem
before we upgrade our server from 4.9.2.

We have also called Sybase tech. support for help. Although they do not have any exact answer
for us, they try to find out some EBF related to the problem. They have asked us to try the
EBF 4430, but there is no point in the bug description list of the EBF relating to this
problem. They said that the EBF may have fixed some other bugs related to the problem!?
Really?

This problem is quite a serious problem because it occurs frequently. So if you have any
response from Sybase or any work around solution, can you mail the solution to me? If I have
any progress, I'll try to tell you also. Thanks.

Best regards

Dick Lam
Hospital Authority, HK

======================================================================================

======================================================================================



Mon, 02 Mar 1998 03:00:00 GMT
 sleeping processes holding locks


[description of problem omitted]

Quote:
>I don't think it's a bug in Sybase.

I think it is.

Quote:
>All of your description fit into one senario:  the application didn't
>`commit' all the way to the outer-most transaction at some point.  That is,
>the whole thing after that point is an outstanding transaction.

No, and here is why:

The exact same code runs many many times throughout the day, week, month,
year (yes this code is almost 4 years mature).  This happens at different
parts of the application, and is never reproducible if exactly the same
action is taken.

Quote:
>Your APT app (or probably even your trigger or stored proc) forgot to
>'commit/rollback tran' for some 'begin tran' statement.  I'll bet that

Nope, all the transactions are committed or rolled back in our source-code.
Now, you are certainly right in that it looks like maybe Sybase isn't
finishing the commit like it's supposed to.

Quote:
>when your trouble-maker logs out and hence releases all the locks, he
>actually loses all his work as well.  That's because the server implicitly

Nope, also not true.  The work actually ends up being saved (unless
we kill the process when we can't get the person fast enough to
log out, or if they are in the middle of some other action).

Gosh... we've that too... what do you know, maybe we are not morons
after all (heavy sarcasm, sorry, but this problem is more frustrating
than you could imagine).

Now... we did upgrade to 10.0.2 recently (we had plenty of problems
with 4.9.* and prior, but not exactly like this one).

Any other suggestions?
--
                        __o
Asya Kamsky             \<_       Ride.  Eat.  Sleep.    California AIDS Ride 2
Santa Cruz, CA       ( )/( )        Ride.  Eat.  Sleep.   1000/525/7  May 14-20



Mon, 02 Mar 1998 03:00:00 GMT
 sleeping processes holding locks
You need EBF 5380.  This fixes several problems with cursors, locks,
and many other good things.  We had several problems with 8311
Severity 26 errors using an Intersolve ODBC System 10 Driver.

The problems was with Sybase.  The ODBC drivers uses cursors to
access the database.  EBF 5380 fixes this problem.  It is real
easy to install.
***********************************

Quote:
>The symptom which is not reproducible but happens fairly frequently

 is that a process will go into receive sleep mode (awaiting command)
 but won't release all of the locks it was holding.  Eventually
 enough people lock sleep on it that someone screams for help
 (or sometimes we notice this doing sp_locks or something) and
 that process has to be killed.  Now, one of the things is that
 this process is *not*{*filter*} from the client point of view
  -- in fact, one of the solutions can be to ask that person
 to exit from the application in which case when the process goes
 away the locks get released.

 This has turned into a nightmare for us since someone always has to be
 on-line monitoring the production system to catch these before they
 trip up too many other people.

 Anyone else seen this?  Is this an APT problem?  A lock manager problem
 in system 10?  What about sybase tech support: is this how they usually
 handle a problem they don't know the answer to?  Should I expect them
 to ever find a solution, or what?



Tue, 03 Mar 1998 03:00:00 GMT
 sleeping processes holding locks


Quote:


>>Back end is Sybase 10.0.2 on an Alpha OSF/1 (Digital Unix).
>>Front end is Sybase APT 5.2.2 EBF2599 on Vax VMS.

>>The symptom which is not reproducible but happens fairly frequently
>>is that a process will go into receive sleep mode (awaiting command)
>>but won't release all of the locks it was holding.  Eventually
>>enough people lock sleep on it that someone screams for help
>>(or sometimes we notice this doing sp_locks or something) and
>>that process has to be killed.  Now, one of the things is that
>>this process is *not*{*filter*} from the client point of view
>> -- in fact, one of the solutions can be to ask that person
>>to exit from the application in which case when the process goes
>>away the locks get released.

>>This has turned into a nightmare for us since someone always has to be
>>on-line monitoring the production system to catch these before they
>>trip up too many other people.

>I don't think it's a bug in Sybase.

>All of your description fit into one senario:  the application didn't
>`commit' all the way to the outer-most transaction at some point.  That is,
>the whole thing after that point is an outstanding transaction.

[lots of stuff deleted]

You can easily check to see whether or not this is the case by looking
for outstanding (i.e. not commited) transactions.  The following is
a procedure that was posted by Scott Spencer (3M Company), and I found
it to be very useful.  Thanks, Scott!

Here it goes:

----------------------------------------------------------------------------

HOW TO FIND THE OLDEST OPEN TRANSACTION !!

PLEASE NOTE THAT I HAVE NEVER FOUND ANY OF THIS INFORMATION DOCUMENTED IN
THE STANDARD SQL/SERVER DOCUMENTATION, SO "DBCC LOG" IS PROBABLY AN
UNSUPPORTED UTILITY.  WHEN IN DOUBT, ASK SYBASE (I don't work there).

I have found it very useful to know who has the oldest open transaction in
a database and how old that transaction is.  This can be critical in
tracking down programs that are leaving long open transactions and are
causing SQL/Server transaction logs to fill.  Here is how I do it (an
example follows):

  1.  Find out the DBID of the database in question and use "DBCC
          LOG(DBID,0,0,0,-1,17,0)" to find out the oldest active page and
          row in the log (ACTIVE XACTS).  You have to do a TRACEON first to
          make this work and I read somewhere that it may not be completely
          accurate unless you have recently done a CHECKPOINT.

  2.  Use "DBCC LOG(DBID,1,PAGENUM,ROWNUM,1,0,1)" to display the time the
          transaction was started and the SPID that started it.  I also
          reset TRACEON at this point.

  3.  Use the SPID against MASTER..SYSPROCESSES to get the SUID of the
          user.  I also get the PROGRAM_NAME, HOSTPROCESS, and CMD as they
          can be useful in later troubleshooting.

  4.  Use the SUID to get the user's name.

EXAMPLE:

1> select dbid from sysdatabases where name = "csmsep1"
2> go
 dbid
 ------
      4
(1 row affected)

1> use csmsep1
2> go

1> checkpoint
2> go

1> dbcc traceon(3604)
2> go
DBCC execution completed. If DBCC printed error messages, see ...

1> dbcc log(4,0,0,0,-1,17,0)
2> go
LOG RECORDS:
        CHECKPOINT      (351813 , 5)
        attcnt=1 rno=5 op=17 padlen=0 xactid=(0 , 0) len=60 status=0x0004
        rows=538976288, pages=8224 extents=8224
        status=0 timestamp=0x0001 0x1c9b8287  active xacts:(318620 , 10)
DBCC execution completed. If DBCC printed error messages, see ...

1> dbcc log(4,1,318620,10,1,0,1)
2> go
LOG RECORDS:
        BEGINXACT       (318620 , 10)
        attcnt=1 rno=10 op=0 padlen=3 xactid=(318620 , 10) len=76
           status=0x736b
        masterid=(0 , 0)        lastrec=(0 , 0)
        xstat=XBEG_ENDXACT,
        spid=197 suid=171 uid=168 masterdbid=0 mastersite=0 endstat=3
        name=user_transaction   time=Jul 29 1994  8:48AM
DBCC execution completed. If DBCC printed error messages, see ...
1> dbcc traceoff(3604)
2> go
DBCC execution completed. If DBCC printed error messages, see ...

1> select spid, suid, program_name, hostprocess, cmd
2> from master..sysprocesses where spid=197
3> go
 spid   suid   program_name     hostprocess cmd
 ------ ------ ---------------- ----------- ----------------
    197    171 Cust_Maint       18551       AWAITING COMMAND
(1 row affected)

1> select suid, name from sysusers where suid=171
2> go
 suid   name
 ------ ------------------------------
    171 JohnDoe
(1 row affected)

At this point you know that JohnDoe was running Cust_Maint (which would
have a UNIX process ID of 18551 if it is a UNIX job) and that it had SPID
197 with an open transaction since 8:48AM.

M. Scott Spencer
3M Company
-------------------------------------------------------

Frank

------------------------------------------------------------------------
Frank Tiemann                             'A foolish consistency is the
Citadel Investment Group, Inc.             hobgoblin of little minds.'

------------------------------------------------------------------------



Tue, 03 Mar 1998 03:00:00 GMT
 sleeping processes holding locks

Quote:


>>Well, we've had an open call with Sybase tech (alleged) support for
>>over two months now with not a hint of resolution for this problem
>>so I'd thought I'd check and see if anyone else has seen anything
like
>>this:

>>Back end is Sybase 10.0.2 on an Alpha OSF/1 (Digital Unix).
>>Front end is Sybase APT 5.2.2 EBF2599 on Vax VMS.

>>The symptom which is not reproducible but happens fairly frequently
>>is that a process will go into receive sleep mode (awaiting command)
>>but won't release all of the locks it was holding.  Eventually
>>enough people lock sleep on it that someone screams for help
>>(or sometimes we notice this doing sp_locks or something) and
>>that process has to be killed.  Now, one of the things is that
>>this process is *not*{*filter*} from the client point of view
>> -- in fact, one of the solutions can be to ask that person
>>to exit from the application in which case when the process goes
>>away the locks get released.

>>This has turned into a nightmare for us since someone always has to
be
>>on-line monitoring the production system to catch these before they
>>trip up too many other people.

>>Anyone else seen this?  Is this an APT problem?  A lock manager
problem
>>in system 10?  What about sybase tech support: is this how they
usually
>>handle a problem they don't know the answer to?  Should I expect them
>>to ever find a solution, or what?
>>--
>>                        __o
>>Asya Kamsky             \<_       Ride.  Eat.  Sleep.    California
AIDS Ride 2
>>Santa Cruz, CA       ( )/( )        Ride.  Eat.  Sleep.   1000/525/7
May 14-20

>Hi Asya,

>Unfortunately, we hit your problem too. Our back end is Sybase 10.0.2

with no EBF, the front
Quote:
>end is also APT 5.2.2 but on AIX 3.2.5 platform. It seems not the APT

problem, and it should
Quote:
>be the problem of back end. i.e. Sybase 10.0.2 engine. 'Cause we do

not have this problem
Quote:
>before we upgrade our server from 4.9.2.

>We have also called Sybase tech. support for help. Although they do

not have any exact answer
Quote:
>for us, they try to find out some EBF related to the problem. They

have asked us to try the
Quote:
>EBF 4430, but there is no point in the bug description list of the EBF
relating to this
>problem. They said that the EBF may have fixed some other bugs related
to the problem!?
>Really?

>This problem is quite a serious problem because it occurs frequently.
So if you have any
>response from Sybase or any work around solution, can you mail the

solution to me?

We've seen this as well. Our's is a development system (10.0.2 on HPUX)
and not everything we do works the first time (imagine that!). All of
our db-code is esql.

I'm thinking of writing a simple routine that would use sp_lock &
sp_who (or some equivalent) to identify processes that are both
sleeping and blocking other processes. Thus identified, the guilty
process could be "killed" and the event logged. This hunter-killer
routine would then be set up to cycle every n (60?) seconds. The trick
will be to avoid killing off the innocent.

Whadayathink?
--

    or                 | { eat ; sleep ; work }



Mon, 09 Mar 1998 03:00:00 GMT
 sleeping processes holding locks

Quote:

>I'm thinking of writing a simple routine that would use sp_lock &
>sp_who (or some equivalent) to identify processes that are both
>sleeping and blocking other processes. Thus identified, the guilty
>process could be "killed" and the event logged. This hunter-killer
>routine would then be set up to cycle every n (60?) seconds. The trick
>will be to avoid killing off the innocent.

I think it would be easy enough to do this, but why?

When I pay a vendor a lot of money for a product I expect it
to work.  When bugs are found I expect them to work on a fix.

And when I pay for technical support, the least I can expect
is call backs.  I'm almost ready to call it quits wasting $$$
on their tech "support".

--
                        __o
Asya Kamsky             \<_       Ride.  Eat.  Sleep.    California AIDS Ride 2
Santa Cruz, CA       ( )/( )        Ride.  Eat.  Sleep.   1000/525/7  May 14-20



Sat, 14 Mar 1998 03:00:00 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. SLEEPING tasks holding locks

2. How to get rid of Sleeping Process/Locks

3. system processes, e.g., LOCK MONITOR, LAZY WRITER, LOG WRITER, CHECKPOINT SLEEP

4. LONG POST - Ghost process holding blocking locks

5. Rogue processes holding locks

6. Numerous sleeping processes/connections - again

7. why it shows the sleeping processes?

8. Sleeping Process

9. Numerous sleeping processes/connections

10. Handle of Sleeping Process

11. sp_cursorclose last T_SQL statement, process sleeping

12. Sleeping Process


 
Powered by phpBB® Forum Software