CODEWITHSUNDEEP
sql-servert-sql
John
John

Reputation: 217

TSQL error: The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION

I'm learning TSQL and getting an error when trying to update a column in my products table. It's telling me that The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION, yet I have a begin transaction at the top of my sproc. Any help is appreciated. 

 alter proc dbo.ProductOrders

@ProdId int, @ProductPrice smallmoney
as
Declare @timesOrdered int, @ProductId int,
Declare  @counter int,  @return_value int
Declare @time timestamp, @irowCount int

set transaction isolation level read uncommitted
set nocount on
set @counter = 0
while(@counter < 3)
begin

begin transaction

select @ProductId = ProductId, @ProductPrice = UnitPrice, @time = ProductStamp
from dbo.Products
where ProductId = @ProdId

select @timesOrdered = COUNT(ProductId)
from dbo.Products
where ProductId = @ProdId 




if(@timesOrdered < 2)
begin
raiserror('Product hasnt been ordered enough to raise price',16,1)
rollback transaction 
break
end





EXEC    @return_value = [dbo].[UpdateProduct]
        @ProductPrice = @ProductPrice,
        @ProdId = @ProdId,
        @time = @time,
        @eRowCount = @irowCount OUTPUT



SELECT  'Return Value' = @return_value





if @return_value <> 0
begin
raiserror ('Product not updated, error occured',16,1,@return_value) 
return @return_value
end

if(@irowCount = 0)
begin
print 'another transaction is trying to access the data'
set @counter += 1
rollback transaction
end

raiserror('Price updated',16,1)
commit transaction
set @counter = 0
return 0

    end--end while loop
    if(@counter = 3)
    begin
    raiserror('try again later',16,1)
    return 99
    end

Upvotes: 0

Views: 8322

Answers (2)

Thimira Dunuville
Thimira Dunuville

Reputation: 69

I faced similar problem recently with SQLserver 2012 in distributed environment. Basically we sent bulk requests by using multiple threads. Even though one of the thread failed all records related to that thread was available in the database. After investigating configuration we observed that we didn't disable "Enlist" in connection string. By default Enlist value is true and it is enabled. After disabling Enlist value we didn't observe above exception and multi threading scenario was working without any problem.Add following configuration to web.config connection string.

Enlist=false

Upvotes: 0

Grantly
Grantly

Reputation: 2556

Your code is calling ROLLBACK then also COMMIT, try this amendment:

...
    if(@irowCount = 0)
    begin
      print 'another transaction is trying to access the data'
      set @counter += 1
      rollback transaction  
    end
    else
    begin
      raiserror('Price updated',16,1)
      commit transaction  
      set @counter = 0
    end
...

Are you missing a RETURN after your ROLLBACK?

Your code is hitting ROLLBACK, then also calling COMMIT a few lines later...

Once you call ROLLBACK, there is no longer any transaction to COMMIT, so you can either start a new transaction (not what you want I'm sure), or ensure you only call COMMIT in the case where ROLLBACK is not called already.

Upvotes: 2

Related Questions