Cause of Page Lock After an update statement??

[sobeit]

The first process that happens in my VB application is calling a stored procedure (from an ADO object)that submits a BEGIN TRANSACTION statement, an INSERT to table-A, and exits the SP without a ROLLBACK statement. The VB app then destroys the ADO object so no connection object is active. A second call from the VB app to the SQL Server with a different ADO object to run a SP that does an update (without any transaction statement) to table-B and it looks like the command is successful. The problem then occur when I try to SELECT (from Query Analyzer)the same data from table-B. The query never return because the page has been locked.

The question that I have is SQL Server treating both stored procedures as one transaction since it never receives the commit or rollback statement?? If so, is it tying the two SPs because they have the same sessionID??

Thanks for your time guys.

 

[RickCole]

I could understand the above scenario if you were trying to run the select from table-a but not table-b.

Maybe if we could see the actual SP's in question then it might become clearer.

Oh if you a Begin Transaction anyway in your SP's or SQL always have a corresponding commit or rollback.

Rick.

 

[sobeit]

Sorry took so long to reply.
well the first SP that gets called has been changed by its creator because the SP didn't have a ROLLBACK before exiting but it goes something like this..

CREATE PROCEDURE prcBADSample AS
BEGIN TRAN
Insert into TABLE-A values(99999)
/*note: checking error right after the insert call....*/
IF (@@error <> 0
Begin
RAISERROR('Raising Bum Error', 14, 2) WITH SETERROR
GOTO PROC_END --exiting without a rollback.
End
COMMIT TRAN

BEGIN TRAN
update tbtEDI set statusid = 101 where eid = 99999
/*note: checking error right after the update call....*/
IF (@@error <> 0
Begin
RAISERROR('Raising Bum Error', 14, 2) WITH SETERROR
GOTO PROC_END ----exiting without a rollback.
End
COMMIT TRAN

......the return to the VB program which then call the second SP (without any transaction). The second call which does and update to table-B returns without any error, but if i try to do a SELECT of the same record in table-B (using Query Analyzer) the query never return. A page lock is in affect.

the second SP...
CREATE PROCEDURE dbo.prcUpdStatusByTID
@TID numeric,
@StatusID int
AS

IF EXISTS (SELECT TID FROM TABLE_B WHERE TID = @TID)
UPDATE tbtTABLE_B
SET StatusID = @StatusID,
ProcessDt = GETDATE()
WHERE TID = @TID
ELSE
RAISERROR('Record does not exist - update aborted',
14, 2) WITH SETERROR

 

[RickCole]

Stored procedures look fine to me so still not sure why you’re hitting this locking problem. The only thing, which might be worth checking, is weather the connection is operating in implicit transaction mode.

Have you tried running the stored procedures direct from Query analyser to see if your still getting the same behaviour?? If from Query analyser you still have the same result try it without the first stored procedure to see if it’s just the second one causing the problem.

If your still no closer after this I would try using SQL profiler to actually see what's going on as there might be something else that's causing the problem.

Rick.

 

[cdukes]

I think you will find that by stating a transaction, the database engine will lock the record that you are about to update. When you exit the stored procedure in error condition without a rollback, the database engine does not know what to do with the opened transaction so the lock will remain there until the connection is closed.

A transaction must ALWAYS be closed, either by a COMMIT or ROLLBACK.


Hope this helps,

Chris Dukes