If I have the following SQL block (in SQL SERVER 2008 R2):
BEGIN
BEGIN TRAN
DELETE FROM dbo.fooData
WHERE LastUpdate < DateAdd(hour, -1,GETUTCDATE())
COMMIT
BEGIN TRAN
DELETE FROM dbo.barData
WHERE SessionID NOT IN (SELECT sub.SessionId FROM dbo.fooData sub)
COMMIT
RETURN 0
END
I am assuming that I have to do an explicit COMMIT between the statements in order for the deleted data from fooData to show up in the second delete. Is this correct? Ideally, I'd want all of this to be in one transaction. Example:
BEGIN
BEGIN TRAN
DELETE FROM dbo.fooData
WHERE LastUpdate < DateAdd(hour, -1,GETUTCDATE())
DELETE FROM dbo.barData
WHERE SessionID NOT IN (SELECT sub.SessionId FROM dbo.fooData sub)
COMMIT
RETURN 0
END
My fear is that the second statement will not pick up the first's deleted data. Note, the return is there because this is part of a stored procedure. I am not interested in cascading deletes or joining, I am somewhat constrained to this method.
The same transaction/session can see it's own changes. Other sessions won't see these uncommitted transactions from this session
So your 2nd form (one wider transaction) is safe to use.