Locking

[NickA]

Hi all

I seem to be beating my head against a wall with what appears to be a simple locking problem.

We have an old character system running on Progress 8.3D clients on Windows NT workstation. The code originates way back from Progress V6 (No internal procedures, 63k limit, no 'counters'), and pulls a sequential counter from a database table called 'spf' as a unique ID for a high volume history table called 'tranfile'.

We seem to have the situation where occasionally two clients lock each other out on 'spf' getting the counter (Call them 'a' and 'b'). Computer 'a' says computer 'b' is locking it out of 'spf', whilst computer 'b' says computer 'a' is locking it out of 'spf'. Surely only one of them can have the lock, and already has the lock? So why do they both think the other has?

Once PC's 'a' and 'b' get caught in their deadly embrace, the whole lot grinds to a halt as every other computer on the network waits for a lock on 'spf' to get the next ID.

Any suggestions you guys/gals can give deeply appreciated.

TIA

 

[m1_ru]

If you read counter from database record and update it,
the deadlock is very likely to happen if
you use initial share-lock to read the current value of counter.

If you use
do transaction:
find first record .
assign record.counter = record.counter + 1.
end.

try to use instead
do transaction:
find first record exclusive-lock .
assign record.counter = record.counter + 1.
end.

In other words:
If there are any chance that data can be changed during transaction they must be initially read with exclusive-lock only (of course you can make exclusive lock of some other corresponding record - "resource"). Otherwise you can have deadlock.

 

[NickA]

Perhaps I should have mentioned?

..that it's our company policy to never use SHARE-LOCK. All uses of the counter are EXCLUSIVE-LOCK.

 

[Serj HAMMER]

Large transaction

Variant 2: it is may be depend from Your transaction.
When You make RELEASE spf?
I think, that between RELEASE and END_OF_TRANSACTION table 'spf' stay in SHARE-LOCK. Because SHARE-LOCK is default for PROGRESS, because RELEASE not always unlock record.

 

[NickA]

Thanks for the suggestions

I've pretty much decided that it'd be less painful to swap over to a 'counter' in the long run: As more people start using this system a growing queue forms for the lock on the 'spf' record - and this is under normal operation. The result: people keep going off and doing other things while they're waiting, which in turn affects productivity, which in turn doesn't make us look good.

It involves modifying a lot of code (No-one thought to put the code to get the counter into a separate procedure, it's manually coded every time! :dizzy: ), but what the heck!