cancel
Showing results for 
Search instead for 
Did you mean: 

Insert into hold lock and never release.

Former Member
0 Kudos

I put following insert in a SP:

Insert into mytab(name, access_date,username, ipaddress )

     Select  'my name', getdate(), 'NA', ipaddr from master..sysprocesses where spid = @@spid   

.....

and then run pb app to call this SP. This cause a ex lock hold on table mytab and never release even auto commit =true in pb until I stop the app.

Not sure why. It is because of @@spid or master? how to release lock for this insert?

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member188958
Active Contributor
0 Kudos

My guess would be that there is an explicit BEGIN TRANSACTION statement upstream of the insert that never gets explicitly committed, your code likely needs to add a COMMIT TRANSACTION statement somewhere.

You can check if you are in an open transaction with @@transtate and @@trancount,

Former Member
0 Kudos

Then I tried following:

CREATE PROCEDURE MyProc

AS

BEGIN

  Begin Tran ABC

     Insert into mytab(name, access_date,username, ipaddress )

     Select  'my name', getdate(), 'NA', ipaddr from master..sysprocesses where spid = @@spid

  Commit ABC     

//Select ....

END

Same. still have a lock hold on mytab.

Former Member
0 Kudos

Found out reason: it is because powerbuilder transaction attribute AutoCommid =  false.

So looks like if client pb app set AutoCommid =  false, even put commit transaction in SP, the transaction still not committed.

Not sure what's the solution for only change code in SP, not touch PB code.