@@Trancount goes from 0 to 2
-
Hi,
I have a stored procedure where I am carrying out two updates. To ensure data integrity the first update is placed within a transaction. After I commit the first transaction I still find that the @@trancount = 1, thus not allowing the second transaction to proceed.
I am not sure if the use of TABLOCKX and HOLDLOCK are affecting the behaviour, but I've looked in BOL and found that both should cease to act when the transaction is terminated...and in my case it hasn't.
BTW, I have inherited this SP
Thanks,
Mauro
*******************************************************************
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetChapsBatch]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetChapsBatch]
GO
/****** Object: Stored Procedure dbo.GetChapsBatch Script Date: 11/03/2005 10:40:16 ******/
CREATE PROCEDURE dbo.GetChapsBatch
(
@Domain varchar(35),
@Logon varchar(35)
)
AS
SET NOCOUNT ON
declare @User_ID int,
@now datetime,
@BatchSize int
-- Get the user id
set @User_ID = dbo.User_ID(@Domain, @Logon)
if @User_ID < 0
return @User_ID -- error code
set @now = GetDate()
-- does this user already own some items for batching?
-- if not then assign items to user
if (select count(*)
from dbo.Completion
Where OwnerUser_ID = @User_ID
AND Status_ID = 3 -- waiting for batch
) = 0
begin
-- the transaction is to make the update atomic
begin transaction
-- lock a minimum (not absolute minimum) rows to ensure data integrity.
declare @id int
select @id = Completion_ID
from dbo.Completion With (TABLOCKX, HOLDLOCK) -- lock for read & write until commit
Where Status_ID = 3 -- waiting for batch
AND Error_ID = 0 -- no error
AND OwnerUser_ID = 0 -- not owned
And FundMatch_ID in (1,2,3,7) -- match or cheque match and overruns
-- none owned so far so grab some.
Update dbo.Completion
set OwnerUser_ID = @User_ID
Where Completion_ID in
(
-- take the top N payments. we may get duplicates but
-- we should NOT use distinct as we could then end up with too many payments.
SELECT top 100 P.Completion_ID
FROM Payment P
INNER JOIN Completion C
ON P.Completion_ID = C.Completion_ID
INNER JOIN Account AFT
ON C.Account_ID = AFT.Account_ID -- Source Account Info
INNER JOIN Account Dest
ON P.Account_ID = Dest.Account_ID -- Destination Account Info
WHERE
-- 30/11/04 KJO Check both Times in range
dbo.IsTimeInRange(@Now, AFT.StartTime, AFT.FinishTime) = 1 -- Source Time in Range
AND dbo.IsTimeInRange(@Now, Dest.StartTime, Dest.FinishTime) = 1 -- Dest Time in range
AND C.Status_ID = 3 -- waiting for batch
AND C.Error_ID = 0 -- no error
AND P.PaymentType_ID = 1 -- Telegraphic Transfer
AND DATEDIFF(DayOfYear, C. CompletionDate, @Now) >= 0 -- on or after completion date
AND C.OwnerUser_ID = 0 -- not owned
And P.Batch_ID is null -- not previously been batched
And C.FundMatch_ID in (1,2,3,7) -- match or cheque match or overrun
Order By P.Payment_ID
)
commit transaction
end
/* DEBUG CODE - PROVES @@TRANCOUNT > 0 */
IF @@TRANCOUNT > 0
BEGIN
PRINT 'A transaction needs to be rolled back'
--ROLLBACK TRAN
END
Update dbo.Completion
set Status_ID = 6 -- set as pseudo Batched ready for MM complete
Where
(
Status_ID = 3 -- waiting for batch
AND
FundMatch_ID in (2, 7) -- cheque match or Cheque Overrun
AND
DATEDIFF(DayOfYear, CompletionDate, @Now) >= 0 -- on or after completion date
)
RETURN 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXECUTE ON [dbo].[GetChapsBatch] TO [public]
GO
-
OOOPSS
Sorry folks, I had not noticed I was running in Query Analyzer Debug with the "Auto Roll Back" check box ticked.
As soon as it hit the begin tran, the @@trancount went up to 2 immediately. Matter of fact as soon as you even do a SELECT, the @@trancount increases. Live and learn
Mauro
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply