locking problem

[chenthil]

I have developed a maintenance program for an enquiry screen. The problem i am facing is when one user is entering details for an enquiry the control file is locked and other user is not assigned the next enquiry number till the earlier suer completes the enquiry form
this is the code
IF KEYLABEL(LASTKEY) = "RETURN" OR KEYLABEL(LASTKEY) = "PF1" THEN
DO:
FIND FIRST xxpr_ctrl EXCLUSIVE-LOCK NO-ERROR.
IF AVAIL xxpr_ctrl THEN
DO:
ASSIGN menq = STRING(xxpr_enqno)
xxpr_enqno = xxpr_enqno + 1.
RELEASE xxpr_ctrl.
DISP menq WITH FRAME A.
END. /*avail xxpr_ctrl*/
END. /*keylabel of lastkey*/
Could anyone let me know whats to be done

cheers
chenthil.

 

[smithxxl]

PROGRESS does not have the best transaction control. I would suggest useing a share-lock instead because a share-lock, a user can still read the records until the actual transaction has been released. On top of that, whenever you are trying to control the transactions, you need to use a transaction block.

For Example...

IF KEYLABEL(LASTKEY) = "RETURN" OR KEYLABEL(LASTKEY) = "PF1" THEN
  DO:
    FIND FIRST xxpr_ctrl NO-LOCK NO-ERROR.

    IF AVAIL xxpr_ctrl THEN
      DO TRANSACTION: /* Transaction block */
        FIND CURRENT xxpr_ctrl SHARE-LOCK NO-ERROR.

        ASSIGN
          menq = STRING(xxpr_enqno)
          xxpr_enqno = xxpr_enqno + 1.

        DISP menq WITH FRAME A.
      END. /* avail xxpr_ctrl DO TRANSACTION */

    RELEASE xxpr_ctrl. /* Release must be outside the transaction block */
  END. /*keylabel of lastkey*/

 

[chenthil]

hi
thanks for ur response
i tried it but when i run it i get this message
|** WARNING--TRANSACTION keyword given within actual transaction level. |
| |(214)

and also I am still getting locked with this message
xxpr_ctrl in use by chg on ttyp5. Wait or press CTRL-C to stop

Cheers
chen

 

[Rajesh Arumugam]

reply for locking issue

|** WARNING--TRANSACTION keyword given within actual transaction level. |
| |(214)

The Error will go when u COMPILE the program and put in your LIVE path.
Once the .r file is created the user will not get this error.
This won't affect your program.

and also I am still getting locked with this message
xxpr_ctrl in use by chg on ttyp5. Wait or press CTRL-C to stop

centhil your code is right. DID you try your code with SHARE LOCk.
Jus give messages before and after the release of the control table and ensure the release of control record.

Cheers
chen[/QUOTE]

 

[MHotovec]

I wouldn't expect a share-lock to make a difference. You'll need to upgrade the share-lock to an exclusive-lock at some point. And when that happens it's going to scope the same.
At the point in your code where you're having a problem, you're obviously already inside a transaction (thus the 'transaction keyword' error received). Look 'above' in your code to see where you've done an exclusive-lock. Given the chance, Progress will scope out to the largest transaction. My guess is you're holding an exclusive-lock on some other table, and this transaction is scoping to the lock. You can find where the transaction begins with a little trial and error placement of the 'do transaction/end' statements. Once you no longer get the error you've mentioned, you're outside of the larger transaction. At that point you'll probably be able to see the scope of the transaction.
Also, the 'release' statement probably doesn't do what you're thinking it does. It does NOT release an exclusive-lock on a table so that another user can lock the table. It DOES remove your pointer on that table so that YOU can't reference fields in the table.

Mark

 

[johnnebi]

Chentill,,

First of all, I would disagree strongly that Progress "doesn't have the best transaction control". I see no basis for that. It in fact has excellent, pinpoint transactional control. This is one of it's strengths. The problem you are experiencing requires the use of a database sequence. That is one of the main reasons for sequences, which are also used in Oracle, SQLServer, Sybase, etc. In certain situations, with ALL databases, deadlocks will occur. Sequence solve the problem.

John

 

[chenthil]

hi john

could u pls tell me how to solve this problem in the code since the locking still exists?
I have tried all sorts of things ! Pls do let me know what changes would I have to do in the code?
cheers
chenthil

 

[johnnebi]

Sure. Your problem is common to older Progress applications written back when the database did not support sequences. A sequence is simply an integer value that defined in the database that is incremented using the next-value() function. The important thing about it is that it is TRANSACTION INDEPENDENT, this freeing you from your problem.

- add a sequence to the database schema - refer to the Progress Documentation on how to do this. Also read about sequences to understand them a little better.

- In the code, when you create the record that contains the field that you need to store the next value in, do as follows:

"myTable.myField = NEXT-VALUE(mySequence)"

The database will increment the sequence automatically. Keep in mind that if the transaction gets undone, that particular value will be lost. So if for example the next value was 6, 6 will never be used anywhere, The next time you create a record, next-value(mySequence) will return 7.

 

[Jim Teel]

Progress implicitly starts a transaction whenever you update or exclusive-lock a database record within an outermost loop (e.g. REPEAT, FOR EACH, etc.) If your program has a REPEAT at (or near) the top, and you have an update anywhere within it, then that is your transaction, and your attempts to create one within it will be problematic. In that case, carve up the entire outside REPEAT loop with DO TRANSACTION...END groups, so that no part of the loop exists outside of one of these groups. I had to fix a program that kept filling the lock table because the developer had a FOR EACH update loop that hit +600,000 records. I tried to put TRANSACTION on the FOR EACH, but got the same complaint from the compiler as was previously mentioned. I grouped the code above the loop, but the problem persisted, because there was a single update AFTER the update loop that still made the whole REPEAT loop a single transaction. When I grouped the code after the update loop the compiler was happy, and my lock table problems went away.

Put me down in the "don't-think-Progress-does-transactions-very-well" category...

 

[johnnebi]

"Progress implicitly starts a transaction whenever you update or exclusive-lock a database record within an outermost loop (e.g. REPEAT, FOR EACH, etc.)

"I would put that differently. A TRANSACTION is SCOPED to the OUTER-MOST BLOCK in which there is a statement that updates the database or a statement that EXCLUSIVE-LOCKs a record.

Some BLOCKS provide automatic TRANSACTION SERVICES (FOR EACH, REPEAT), others do not (DO, DO WHILE TRUE). The PROCEDURE BLOCK also provides transaction services.

That is why you get a compiler error when you use the TRANSACTION keyword with the FOR EACH - it's redundant. The TRANSACTION keyword is useful for adding transaction services to DO blocks.

All updates performed in a block that provides transaction services are undo-able, thus you will use system resources to maintain that undo-ability. This becomes magnified with the presence of iterative blocks. So it is really a matter of planning your transaction structure accordingly. It works quite well for relational databases.

Consider the following trivial procedure (lets say there are 50 order-lines):

FIND FIRST order.

FOR EACH order-line OF order:
ASSIGN item-date = TODAY.
END.

The scope of the transaction? 1 record.

Lets add 1 line of code to RAISE THE SCOPE of the the TRANSACTION:


FIND FIRST order.
ASSIGN order.order-date = TODAY.

FOR EACH order-line OF order:
ASSIGN item-date = TODAY.
END.

The scope? 51 records. This is normally what you would want.
Notice that the following makes no difference:


FIND FIRST order.

FOR EACH order-line OF order:
ASSIGN item-date = TODAY.
END.

ASSIGN order.order-date = TODAY.

The scope is still 51 records, because the ASSIGN statement, regardless of the fact that it comes AFTER the FOR EACH, is still at the PROCEDURE level
(outer-most block). With blocks, It is import to not think LINEARLY.


If you to create 2 single record scopes do as follows:

DO TRANSACTION:
FIND FIRST order.
ASSIGN order.order-date = TODAY.
END.

FOR EACH order-line OF order:
ASSIGN item-date = TODAY.
END.

THIS LOWERS the SCOPE of the TRANSACTION. But, in the relational context, you may not want this, because in the event of an undo, you could end up with the order date different fro the order line dates.

As is the case with any language, the basic constructs, which seem elegant and straight-forward at the elemental level, can blossom in to ones that are quite complex and hard to de-bug. But, compared to any SQL-based system, I'll take the Progress 4GL everytime.

But, in any event, the SEQUENCE is the answer to the problem that started this thread.

 

[Casper]

Hi all,

Just one question:
Why should there be a release outside of the transaction block as proposed by smithxxl?

I always put the release inside the transaction block, because a release causes the write trigger to fire. So any changes made , or errors occurring inside the write trigger are still handled by the same transaction bock. Which in my opinion simplifies an undo of the transaction.

Casper.

 

[Casper]

Hi,

I forgot to say something about the actual question
Another option is creating something like a number pool. The idea is that you generate x numbers and everyone who needs an enquiry number takes one from the pool. If anything goes wrong during the making the number is put back and otherwhise the next number is put back into the pool....

You probably need a database change for this, but this has the advantage that besides numbers you can put in anything you like.

We use it for filenumbers of the type 'X0000001', 'X0000002' etc.....

Casper.

 

[johnnebi]

I will answer that but, let me first mention something that I forgot to mention in my last reply. The original problem that inspired this thread is really a problem of CONCURRENCY, not a TRANSACTION problem. A TRANSACTION is a change or set of changes to the database that must be applied entirely or not applied at all. A record lock is simply one of the necessary side-effects of a transaction.

Smithxx put the RELEASE in the code because the transaction is WEAK SCOPED - the record will indeed be LOCKED outside of the transaction block - but the transaction WILL be committed. The RELEASE statement validates the record, and clears it from the buffer and, as long as there is no active transaction, releases the LOCK. But it cannot unlock a record during an active transaction. He could also have used STRONG SCOPING - the "DO FOR" block. Then the release would have not been necessary:

DO FOR <mytable> TRANSACTION:

FIND FIRST <mytable>:
UPDATE <mytable>.

END.

/* If inserted, this line of code: "DISPLAY mytable." WILL NOT COMPILE.

With STRONG SCOPING, the compiler WILL NOT EVEN LET YOU REFERENCE mytable outside of the STRONG SCOPED BLOCK unless there is
another FIND of it outside that block !!! */

Place a PAUSE at the end of this procedure, start another session and verify that the record is not locked as follows:

find first myTable exclusive-lock no-wait no-error.
IF NOT AVAIL myTable THEN
IF LOCKED myTable THEN
MESSAGE "Record is locked" VIEW-AS ALERT-BOX.
ELSE
MESSAGE "There is no record" VIEW-AS ALERT-BOX.

Remove the strong scoping and try again. The record will still be locked, BUT it WILL BE COMMITTED.

 

[smithxxl]

I'll have to try that and do some testing. Thanks for your input John (johnnebi)... Please use the [CODE ] and [/CODE ] vBulletin tags though. It's a little easier to read.

Thanks,

Brad

 

[Casper]

Hi,

So the point that i made still stands. I believe it is'nt wise to put the release statement outside of the transactionblock for obvious undo reasons. The record will be unlocked as soon as the transaction is done. Any errors in the write trigger requiring an undo will only be handled correctly if the release is done within the transaction block (weak transaction).

To get back to the inital question. I believe the problem is that the there is a bigger transaction in progress (maybe the procedure). If you put a do transaction: .... end. around your code and you remove the uppper transction if possible then the record would'nt remain locked for a long time. It's only at the lastkey = .... event that the record is locked.
If the higher transcation level is required then it's better to use a sequence or some other form of numbering.

Casper.

 

[johnnebi]

"The record will be unlocked as soon as the transaction is done. Any errors in the write trigger requiring an undo will only be handled correctly if the release is done within the transaction block (weak transaction)".

Incorrect.

There is no such thing as a "weak transaction".
There is however something called "WEAK SCOPE", which means that the lock and the record remains in the buffer even after undo processing is out of scope. All you are doing by putting the release inside the block is clearing the buffer of that record, and removing the lock. If there is an error in the block, the write trigger or whatever, undo processing will occur regardless of whether or not you release the record inside or outside the block.

Try this:

on write of mytable
   do:
	  message "trigger fired" view-as alert-box.
	  return error.
   end.

do transaction on error undo, leave:
  find first mytable.
  update mytable.
end.
message "After block" view-as alert-box.

 

[neilbert-berina]

hi i dont know if its right to interfere to another thread but i cant find any same thread about my problem . . . this is the only thread i found that is very similar to what my problem is . . . i am working on a company who develop banking system . . . i have a problem also about record locking and it involves updating the same database simultaneosly . . . . weve created many module on our system and when my problem is when the system will be use there are many programs will run simultaneosly and access the data simultaneosly also. . and it will also update the database by many programs i wolud like to know what will be the effect if all the program will access the same table and update it also . . we have a field on the table that will save all the transaction sequence made by the system and all of the module will access and update it also . . what if one module uses the table and other also will use it. . . on our query we use no-lock when we will access only the table but when we will update it or create record we use exclusive lock . . . i know that if someone is using that table the other transaction will just wait until the record is not lock . . . please help me what will be the most posible solution on my problem . . . tanx very much . . . . and im a newbie on this site tanx . . .

 

[neilbert-berina]

and we are also trying to use the do transaction block but we are not sure it will solved our problem . . . the bigger picture of the problem is that the bank will have more branches all of them will access the database on the main branch . . . . .

 

[tamhas]

hi i dont know if its right to interfere to another thread

No, it wasn't the right thing to do since you just caused me to read a bunch of 4 year old posts and I almost responded ... which probably wouldn't do the original poster much good at this point.

Start a new thread and leave this one alone.

BTW, one of the issues not clearly addressed in the original thread, which might apply to you, is that there should be no open database activity during user interaction. If you do the UI into local variables and then at the time of commit have a tightly scoped transaction of the briefest possible duration, then you minimize a great many of these issues.