|
Home > Archive > ASE Database forum > October 2005 > Server out of lock error
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Server out of lock error
|
|
|
| Lately, endusers has been reporting server running out of
locks error. I recently bumped the number of locks to
300,000 and we're still running out of locks. We have a
cronjob that does sp_lock every 5 mins to capture the
activity on the server and I have not seen accumulated locks
go beyond a couple of hundred in all the outputs for our
process. My question is if ASE account for cascade locks in
syslocks table. Meaning if I insert a row in tableA with RI
trigger on tableB within a transaction. Would I see locks
associated with table tableB in syslocks and does it count
towards available locks on the server?
| |
| Bret Halford 2005-10-27, 8:21 am |
| If you have some time when the server isn't otherwise being used,
you can turn on traceflag 1212 and do a sample insert. The output
will show you all the locks involved as they are granted and released.
Don't do this while there is other activity on the serever - there is
just too much output.
sp_monitorconfig may be a better choice than sp_lock for your
monitoring. If you have also installed the MDA monitoring tables,
you can watch sp_monitorconfig for when the max_used spikes
and then look in the MDA tables to see what sql just ran.
-bret
Ollie wrote:
> Lately, endusers has been reporting server running out of
> locks error. I recently bumped the number of locks to
> 300,000 and we're still running out of locks. We have a
> cronjob that does sp_lock every 5 mins to capture the
> activity on the server and I have not seen accumulated locks
> go beyond a couple of hundred in all the outputs for our
> process. My question is if ASE account for cascade locks in
> syslocks table. Meaning if I insert a row in tableA with RI
> trigger on tableB within a transaction. Would I see locks
> associated with table tableB in syslocks and does it count
> towards available locks on the server?
| |
| Andrew Schonberger 2005-10-27, 8:21 am |
| Ollie,
do you have, by any chance, stored procs, doing updates in a cursor loop ?
I first noticed this behaviour in 1998, and again this year (versions
11.0.1 ... 12.5.2 ). The problem came from stored procedures running
cursor loops. At each iteration of the cursor, the code changed a few
records ( immediate commit, no transaction). I noticed the the locks
were not released immediately. During investigation, I placed into the
loop a statement like
select count(*)
from master..syslocks
where id = object_id( 'mytable' )
-- I'm writing this from memory.
-- Just beware: do not join syslocks with anything else,
-- because your own measurement becomes slow and
-- misses some transient items.
The query shows that locks survive for a few milliseconds after the
insert-update-delete. This makes sense, as a write-behind mechanism.
Trouble is, the cursor loop is sometimes running faster than the
write-behind lock release thread. So, on the next iteration, more locks
are being added, even before the older ones are being collected. In
essence, locks are created faster than they are being removed. After a
few thousand iterations of the loop, the system runs out of locks.
My solution was to add a Waitfor Delay "00:00:00.020" in the code of the
loop. A twenty to fifty millisecond pause was usually sufficient to
allow the locks to be removed, before creating new ones. With that
select count(*) from syslocks, it was possible to fine-tune the value of
the delay, to make sure the locks were really gone before proceeding to
the next iteration.
I'm just a developer, not a DBA. But my solution has worked for me in a
variety of environments.
Andrew
Ollie wrote:
>Lately, endusers has been reporting server running out of
>locks error. I recently bumped the number of locks to
>300,000 and we're still running out of locks. We have a
>cronjob that does sp_lock every 5 mins to capture the
>activity on the server and I have not seen accumulated locks
>go beyond a couple of hundred in all the outputs for our
>process. My question is if ASE account for cascade locks in
>syslocks table. Meaning if I insert a row in tableA with RI
>trigger on tableB within a transaction. Would I see locks
>associated with table tableB in syslocks and does it count
>towards available locks on the server?
>
>
|
|
|
|
|