asked on

Transaction / Locking ?

I have this vb.net code that is trying to do a simple read Select outside of the Transaction. But it fails. I would think there should not be a problem do this. What am I doing wrong?
sql server database is set to compatibility 120

   myTransaction = SQLConn.BeginTransaction(IsolationLevel.ReadUncommitted)
                Dim sql As String = "Execute dbo.insertCancelInvoicePlan @InvoiceHeaderID, @PFCustomerOrderNumber"
                Using comm As New SqlCommand(sql, SQLConn)
                    comm.CommandTimeout = 300
                    comm.Transaction = myTransaction
                    For Each InvoiceHeaderID As Integer In InvoiceHeaderIDList
                        Dim CustomerOrderNumber As String = GetRandomPO(8)
                        While True
                            ' next line does a select against a table that is used in this transaction
                            If IsNewCustomerPO(CustomerOrderNumber) = True Then
                                Exit While
                            End If
                            CustomerOrderNumber = GetRandomPO(8)
                        End While
                        comm.Parameters.AddWithValue("@InvoiceHeaderID", InvoiceHeaderID)
                        comm.Parameters.AddWithValue("@PFCustomerOrderNumber", CustomerOrderNumber)
                        comm.ExecuteNonQuery()
                        comm.Parameters.Clear()
                    Next
                End Using

Open in new window

lcohan

"But it fails." - is there any specific error that you can post? Also it may be that even you have CommandTimeout = 300 the connection timeout default is the reason and I believe that default value is 30

Jess31

ASKER

I removed the timeout and the same thing.
No error. But it will hang when I try to query either of the two tables I have used in the still open transaction. I tried this in SSMS with same result. Namely I paused the program at line 18 and when I try to query either of the two tables it does not return anything.
Here is the dbo.insertCancelInvoicePlan

ALTER  PROCEDURE [dbo].[InsertCancelInvoicePlan]

	@InvoiceHeaderID int,
	@PFCustomerOrderNumber varchar(12)

AS

   SET NOCOUNT ON


  
	  
Begin

	Declare @NewInvoiceHeaderID int

	Insert Into InvoiceHeader (PFCustomerOrderNumber) Values (@PFCustomerOrderNumber);


	set @NewInvoiceHeaderID =  SCOPE_IDENTITY()

	Insert Into InvoiceLine (InvoiceHeaderID, OrderLineID, Quantity) 
	Select @NewInvoiceHeaderID, OrderLineID, Quantity From InvoiceLine Where InvoiceHeaderID = @InvoiceHeaderID

	Update InvoiceHeader Set Cancelled = 1 Where ID = @InvoiceHeaderID;

End

Open in new window

lcohan

Ouch...so in the INSERT SP you're doing an update on the new record by the ID returned via SCOPE_IDENTITY() - is there a clustered index on that table?
Also are there any FKey between the InvoiceHeader and InvoiceLine? I guess an option would be to use the OUTPUT instead as Microsot recommends here: https://support.microsoft.com/en-us/help/2019779/you-may-receive-incorrect-values-when-using-scope-identity-and-identity and perform the other insert/update outside this SP and in their own code objects.

lcohan

So if I read correctly the business logic you need to perform is to make sure that after inserting a new InvoiceHeader you need to generate a new InvoiceLine with that ID that just got created and reset(cancel) the old InvoiceHeader - InvoiceHeaderID and all this in a atomic transaction right?

Jess31

ASKER

I am not doing an Update on New row, it is on the old row. Basically I am copying old row to a new one, and then Updating old rows column Cancelled to 1

Jess31

ASKER

These tables are new, just developing, so the only index is the index on an PK ID which is clustered

Jess31

ASKER

>>So if I read correctly the business logic you need to perform is to make sure that after inserting a new InvoiceHeader you need to generate a new InvoiceLine with that ID that just got created and reset(cancel) the old InvoiceHeader - InvoiceHeaderID and all this in a atomic transaction right?

Exactly

lcohan

Would it be possible to try this piece of code instead?

ALTER  PROCEDURE [dbo].[InsertCancelInvoicePlan]
	@InvoiceHeaderID int,
	@PFCustomerOrderNumber varchar(12)
AS
SET NOCOUNT ON;
Begin

	Declare @NewInvoiceHeaderID int

	Insert Into InvoiceHeader (PFCustomerOrderNumber) Values (@PFCustomerOrderNumber);


	set @NewInvoiceHeaderID =  SCOPE_IDENTITY()

	--Insert Into InvoiceLine (InvoiceHeaderID, OrderLineID, Quantity) 
	--Select @NewInvoiceHeaderID, OrderLineID, Quantity From InvoiceLine Where InvoiceHeaderID = @InvoiceHeaderID

	DECLARE @OrderLineID int, --check to match your table actual data type
			@Quantity int -- --check to match your table actual data type

	select @OrderLineID = q.OrderLineID, @Quantity = q.Quantity 
	from (select OrderLineID, Quantity From InvoiceLine Where InvoiceHeaderID = @InvoiceHeaderID) q

	Insert Into InvoiceLine (InvoiceHeaderID, OrderLineID, Quantity) 
	select @NewInvoiceHeaderID, @OrderLineID, @Quantity

	IF @@ERROR = 0
	Update InvoiceHeader Set Cancelled = 1 Where ID = @InvoiceHeaderID;

End
GO

Open in new window

Vitor Montalvão

I'm not seeing you commit the Transaction and this will keep your tables locked until a Commit or Rollback is executed.

Expected to see mytransaction.Commit(); and mytransaction.Rollback(); in case of error

ASKER CERTIFIED SOLUTION

Jess31

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

Jess31, if this issue is solved please close this question.
Cheers

Vitor Montalvão

Recommendation to close this question by accepting the above comment as solution.