Sybase/JDBC: how to detect reorgs or exclusive locks?
We use Sybase ASE (15.5) server as our DB and are having strange,
intermittent SPID blocking issues that I am trying to detect and mitigate
programmatically at the application-layer.
Sybase allows you to schedule so-called "reorgs" which from what I can
tell are periodic re-indexes/table compactions, cleanups, etc. Scheduled
DB maintenance, basically.
Every once in a while, we get all the planets coming into alignment with
each other, where:
A query is executed (creating a SPID in Sybase) and hangs for some reason.
This places a (blocking) shared lock on, say, the widgets table; then
The scheduled reorg kicks off, and wants to cleanup the widgets table. The
reorg places an exclusive lock request on widgets, but can't obtain the
lock because widgets is already locked and blocked by the hanging
SPID/query; then
Subsequent queries are executed, each requesting shared locks on widgets;
such that
The whole system is now tied up: the reorg can't start until it obtains an
exclusive lock on widgets, but widgets is tied up in a blocking shared
lock by a hung SPID. And because the reorg has placed an exclusive lock on
widgets, all other queries wanting shared locks on widgets have to wait
until the reorg is complete (because a newly requested exclusive lock
trumps a newly requested shared lock).
I think my ideal strategy here would be to:
Timeout DB queries after say, 2 minutes, which will prevent SPIDs from
hanging and thus preventing the reorgs from running; and then
If a query attempts to hit a table that has an exclusive lock on it,
detect this and hadle it specially (like schedule the query to run again
1hr later, when hopefully the reorg is complete, etc.)
My questions:
How do I timeout a query to release a shared lock after, say, 2mins?
Is there a way to programmatically (most likely through the Sybase JDBC
driver, but perhaps via Sybase command-line, HTTP calls, etc.) determine
if a reorg is running? Or, that an exclusive lock exists on a table? That
way I could detect the exclusive lock and handle it in a special way.
Thanks in advance!
No comments:
Post a Comment