Answered Reason for Locking issue this multiuser Scenerio.

[Pavan Yadav]

Hello Friend,
I am facing locking error in when i execute second user session and i am not clear on conceptual side why progress disp lock condition occurs. The scenerio is geiven as below :

Session 1 :
DO TRANSACTION :
Create Customer.
Assign cust-num = 999. /* any non existing cust-num. cust-num is unique index */
Pause.
Delete Customer.
END.

Session 2:
DO TRANSACTION :
Create Customer.
Assign cust-num = 999. /* any non existing cust-num. cust-num is unique index */
Delete Customer.
END.

Execute Session 1 first and when at Pause , run second session also and Locking condition occurs for the second session. Kindly help me to understand why it is so?

Regards,
Ravi

 

[Pavan Yadav]

Just one correction
in Session 1, Pause statement is after Delete Statement.

Regards,
Ravi

 

[Pavan Yadav]

Also if we keep session 1 code as it is in my first comment ,then index error is displayed when second session is executed.
As par my understanding , for the scenerio when Pause is after Delete , Since its in Do transaction block , Cust-num will not be reflected in DB as 999 untill the transaction is not over, hence if second user session also uses the same cust-num to update and Delete , then that session should be allowed to go ahead without any lock.

In Scenerio when Pause if before Delete , why Second session is facing index issue 9 Record Already exist" as cust-num as 999 is not saved in DB yet since transaction is not over yet and second session should be allowed to create it.

Regards,
Ravi

 

[RealHeavyDude]

What are you trying to prove with your code samples?

See if I can explain this: You are right in that changes to the database get written to the database at the end of the transaction. But there are some exceptions to that rule: When you create a record and assign a field that is part of an index it gets written to the database before the transaction ends. If I remember correctly that is documented somewere - but I can't find it right now.

That is also the reason as to why your second session will raise an index violation error.

Nevertheless, if I were you, I would always check the existence of a record first before a create a new one and assign fields that are uniquely indexed. IMHO something like that is not only a violation of a unique index - it is also a violation of a business rule. And, I always to want handle violation of business rules in the application instead of relying on the database throwing an error that most likely won't be handled properly in the application. But, that is IMHO.

Heavy Regards, RealHeavyDude.

 

[krishangopalmca]

Thanks a lot RealHeavyDude.....!!
Yes we will not be using this practice in real scenario, but was surprised with this behavior.

One doubt is cleared that index error as per you indexed field will be written to DB before Transaction.

Still second doubt is there why locking issue is there if PAUSE is after Delete statement...?

This is just to clear whats really going in in background...!!

Thanks again..!!!

 

[TomBascom]

Because the DELETE is not committed until the end of the transaction. Which is after the PAUSE. The 999 record could, in theory, still end up being created.

"In theory" would require a somewhat more complex snippet of code where the DELETE is a sub-transaction.

 

[krishangopalmca]

Hi Tom,

Even if PAUSE is after DELETE, in second session record shows locked.
Trying to understand how progress is behaving.

Thanks,
Krishan

 

[TomBascom]

That's what I am telling you.

The transaction commits after the END statement. The PAUSE is between the DELETE and the END. So the transaction has not yet committed. Which means that record 999 still exists to outside eyes. The DELETE could roll-back (for instance if the user types F4...) Of course in this very simple case such a roll-back would also UNDO the CREATE -- but in more realistic examples the CREATE and the DELETE are not likely to be in the same transaction block.