Properly scoped transactions in Stored Procs

Question

Suppose I have a stored procedure that manages its own transaction

CREATE PROCEDURE theProc
AS
BEGIN

  BEGIN TRANSACTION

-- do some stuff

  IF @ThereIsAProblem
    ROLLBACK TRANSACTION
  ELSE
    COMMIT TRANSACTION
END

If I call this proc from an existing transaction, the proc can ROLLBACK the external transaction.

BEGIN TRANSACTION
EXEC theProc
COMMIT TRANSACTION

How do I properly scope the transaction within the stored procedure, so that the stored procedure does not rollback external transactions?

Answer 1

use @@trancount to see if you're already in a transaction when entering

Answer 2

The syntax to do this probably varies by database. But in Transact-SQL what you do is check @@TRANCOUNT to see if you are in a transaction. If you are then you want to create a savepoint, and at the end you can just pass through the end of the function (believing a commit or rollback will happen later) or else rollback to your savepoint.

See Microsoft's documentation on savepoints for more.

Support for savepoints is fairly widespread, but I think the mechanism (assuming there is one) for finding out that you're currently in a transaction will vary quite a bit.

Answer 3

Watch this DNR-TV video, they talk a lot about transaction scoping, though handling it in code vs. sql.

hope this helps.

Answer 4

David, this is actually a response to a question you left in a comment to my answer elsewhere (this site doesn't let one contact a different user directly).

You asked if I can suggest a Key-Value store that can handle concurrency and large volumes without incurring the overhead of a full-blown db. The answer is yes, it's called BerkeleyDB (in fact BerkeleyDB is one of MySQL's storage engines). It's owned by Oracle now, but it's still free and good. http://www.oracle.com/technology/products/berkeley-db/index.html .