Sybase locks

[Julie Lawton <lawton_julie@HOTMAIL.COM>]

Further to my mail the other day, I am still having problems with executing
my SQR script against a Sybase database.

The script (attached) is selecting from a table called FXTicket and then
within the select it calls an update procedure to mark the records as sent.
The script generates a file containing those records for exporting to
another system.

I am currently testing it on a large volume of data (over 4000 records).
Under normal conditions it would not select this sort of volume but more
likely a max of 200 records.  The script is run every 10 minutes.

During my testing, I can run the script and it completes successfully.  I
can run it again immediately and it will hang.  I can not get it to
consistently work or not work.  When it hangs, I always get a sh_page-blk
lock when I do sp_lock.

I understand that this lock is blocking another process and this page cannot
be accessed.  I guess that the select is holding the lock and the update is
the other process trying to access that page.

I have tried committing the transaction after every update but the problem
still occurs.

Does anyone know how I can avoid this locking situation?  I can't get my
head around why it does not happen every time I run the script.

Thanks for any help.

Julie


______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com

Aim.sqr