Transaction to set a flag

11 May 2005 12:34 AM

dtbascent

Long-running transactions are generally a bad idea. But what about this:

A group in my company is creating a report queueing and generation engine.
They want to create a server collect and queue reports requests in a table,
and subsequently run reports at later times depending on certain parameters.
These reports may take 30 minutes or more to complete. This service may
run on mutiple application servers. They want to allow any one application
server to pick up on the queue and begin running a report and set a flag so
that the other servers do not pick up a report already in process. But at
the same time they want a mechanism to assure that if the database or
application server fails, that flag is not perpetually showing the report in
process when it is not.

Normally I would not advise a long-running transaction, but in this case,
why not start a transaction, set the flag, and when the report is complete,
commit it? This will assure a flag rollback upon server or connection
failure. The report processing requires all reads, so that nothing will be
logged from that work, or if extensive logged work is required, then the
actual report work could be handled via a secondary database connection to
assure the log does not grow to large.

Comments?

12 May 2005 10:30 PM

Craig Kelly

"dtbascent":

Show quote

> Long-running transactions are generally a bad idea. But what about
> this:
>
> A group in my company is creating a report queueing and generation
> engine. They want to create a server collect and queue reports
> requests in a table, and subsequently run reports at later times
> depending on certain parameters.
> These reports may take 30 minutes or more to complete. This service
> may run on mutiple application servers. They want to allow any one
> application server to pick up on the queue and begin running a report
> and set a flag so that the other servers do not pick up a report
> already in process. But at the same time they want a mechanism to
> assure that if the database or application server fails, that flag
> is not perpetually showing the report in process when it is not.
>
> Normally I would not advise a long-running transaction, but in this
> case, why not start a transaction, set the flag, and when the report
> is complete, commit it? This will assure a flag rollback upon server
> or connection failure. The report processing requires all reads, so
> that nothing will be logged from that work, or if extensive logged
> work is required, then the actual report work could be handled via
> a secondary database connection to assure the log does not grow to
> large.
>
> Comments?

Sorry to jump in late here,

I understand the desire to use a transaction for this, but even if you get
it to work (in the face of other concerns posted in this thread) it still
might cause a few issues I can think of.

- Future maintainers will not be expecting transactions to be used for this

- In the course of day to day operations, DBA's will need to know that some
long-running trans are "good".

My suggestion would be similar but not quite the same... you want to use SQL
Server's ability to handle mutual exclusion. Why not use a lock instead of
a tran (since that's what you're using the tran to create anyway)? Check
out: sp_getapplock and sp_releaseapplock.

Craig

13 May 2005 12:15 AM

dtbascent

The point of the transaction is to support automatic and immediate rollback
of the flag should either the application or the database server crash. This
is the feature I am mainly after. If this happens, I don't want the user to
have to wait for some arbitrary time limit to restart the report process.
And that time limit must be set very high to account for server busy times
when report take longer. At the same time, I don't want the application
server to kick off the same report for processing just because the current
report process is taking an abnormally long time to complete. Both of these
are scenarios that the "lease" solution risks. sp_getapplock does not
automatically rollback the flag upon server or connection failure.

After all, what is a transaction? It is a set of conditions that you want
to happen all together or not if there is one failure of one. That's what I
want. My transactions may just happen to take 30-60 minutes to complete.
But that extra time is adding little more burden to the server.

Show quote

"Craig Kelly" wrote:

> "dtbascent":
>
> > Long-running transactions are generally a bad idea. But what about
> > this:
> >
> > A group in my company is creating a report queueing and generation
> > engine. They want to create a server collect and queue reports
> > requests in a table, and subsequently run reports at later times
> > depending on certain parameters.
> > These reports may take 30 minutes or more to complete. This service
> > may run on mutiple application servers. They want to allow any one
> > application server to pick up on the queue and begin running a report
> > and set a flag so that the other servers do not pick up a report
> > already in process. But at the same time they want a mechanism to
> > assure that if the database or application server fails, that flag
> > is not perpetually showing the report in process when it is not.
> >
> > Normally I would not advise a long-running transaction, but in this
> > case, why not start a transaction, set the flag, and when the report
> > is complete, commit it? This will assure a flag rollback upon server
> > or connection failure. The report processing requires all reads, so
> > that nothing will be logged from that work, or if extensive logged
> > work is required, then the actual report work could be handled via
> > a secondary database connection to assure the log does not grow to
> > large.
> >
> > Comments?
>
> Sorry to jump in late here,
>
> I understand the desire to use a transaction for this, but even if you get
> it to work (in the face of other concerns posted in this thread) it still
> might cause a few issues I can think of.
>
> - Future maintainers will not be expecting transactions to be used for this
>
> - In the course of day to day operations, DBA's will need to know that some
> long-running trans are "good".
>
> My suggestion would be similar but not quite the same... you want to use SQL
> Server's ability to handle mutual exclusion. Why not use a lock instead of
> a tran (since that's what you're using the tran to create anyway)? Check
> out: sp_getapplock and sp_releaseapplock.
>
> Craig
>
>
>

16 May 2005 10:59 PM

Craig Kelly

"dtbascent" wrote:

<snip>

> sp_getapplock does not automatically rollback the flag upon server
> or connection failure.

Oops... I was actually thinking (but failed to write :) that the lock itself
would become the "flag" which probably would not fulfill your requirements
now that I think about it. Of course, you'll need to make sure that the
flag is readable while your transaction is open. My personal gut feeling is
that you don't have sufficiently fine-grained control over SQL Server
locking semantics to insure this consistently and with future versions
(including SP's) of SQL Server. That's not an assertion of fact or a
technical criticism of your plan: just my opinion.

> After all, what is a transaction? It is a set of conditions that
> you want to happen all together or not if there is one failure of
> one. That's what I want. My transactions may just happen to take
> 30-60 minutes to complete. But that extra time is adding little
> more burden to the server.

I can't argue with anything other than the last sentence: I don't think
that's guaranteed to be true. However, as I said above, that's just an
opinion and I get the impression that you've already fully tested the
situation: just be careful to re-test after the next SP!.

Something else that worries me now that I start typing is the fact that
you'll need to mandate that any connection querying the table in question
always uses NOLOCK or sets the isolation level to read uncommitted.

Another idea to consider one I've seen Erland suggest: a table keyed by
SPID. But then you'd need to run a job every so often (every minute maybe?)
to recover orphaned SPID's and you'd need to check to make sure the SPID
wasn't being reused.

I do have one request: if you go the transaction route, could you post
details about how it worked out and how you implemented it? I realize time
constraints and company policies might make this impossible, but I for one
would be interested to hear how it went...

Craig

12 Jul 2005 2:48 PM

ML

I know by now you're done with that problem, but still... Why not use
replication? That way you can run those time-consuming queryies on a
different server (or in a different database) without locking the production
server.

ML