Server down : don't lose everything.

[B.Dauvissat]

Hi,
I have a problem on a query i wrote.

It (the query) is very complex and has a lot of recursive requests (same table, previous lines).

On a very heavy database, the request lasted over 6 days :blush:. But the server went down and all changes were lost.

my code is like that :

DEFINE QUERY q-Table FOR Table.

    OPEN QUERY q-Table
    FOR EACH  Table
    WHERE Table.Date >= i-Date
    BY Table.Date.
    
    GET FIRST q-Table.

    DO WHILE NOT QUERY-OFF-END("q-Table") :

        [Many things]

        GET NEXT q-Table.

    END.

    CLOSE QUERY q-Table.

I would like to commit every 200 lines so, if the server is down, I'm not loosing everything.

Thank you for your help.

 

[B.Dauvissat]

Help !
Users are tracking me down across the cubicles !
And there is nobody to offer me a blue or a red pill !

 

[bulklodd]

The following code will commit each line and if your server breaks down you won't lose anything.

DEFINE QUERY q-Table FOR Table.
 /* Query should have NO-LOCK option */
 OPEN QUERY q-Table
 FOR EACH  Table
 WHERE Table.Date >= i-Date
 BY Table.Date
 NO-LOCK.
 
 /* Getting a record with NO-LOCK */
 GET FIRST q-Table NO-LOCK.
 DO WHILE NOT QUERY-OFF-END("q-Table") :

     TR:
     DO TRANSACTION
        ON ERROR UNDO TR,LEAVE TR
        ON STOP  UNDO TR,LEAVE TR:
        /* Re-getting the record with EXCLUSIVE and starting transaction here */
        GET CURRENT q-Table EXCLUSIVE.
        [Many things]
     END.
     
     /* Getting a record with NO-LOCK */
     GET NEXT q-Table NO-LOCK.
 END.
 CLOSE QUERY q-Table.

HTH

 

[B.Dauvissat]

Thank you very much.

Now, they can wait while I'm trying to make it faster.

 

[joey.jeremiah]

unless you're reading billions and billions of records or running nuclear
reaction simulations

no query should take days period.


run the query on a self-service client that access the database thru
shared memory and not the network.

run the query, maybe a nightly batch run, on the same machine as the
database.


making all the generated records one single monstrous transaction is
insane ( i'm being generous ).

if there's no need for the generated records to be an all or nothing deal
( atomic ), make every create a separate transaction.

my guess is that the database crashed because it ran out of lock table
entries.


the good news is that there is a fix all pill, read the docs ( and join peg ).

that's it, that's all it take to be an expert.

if you don't, you deserve what ever is coming your way.

 

[Casper]

6 days.....I can't believe it, that's long!!!
It's probably a very big table with no index on the date field?..... wonder what the query did with overall perfomance on the server....
I once did by mistake an unidexed can-find on a record which didn't exist in a table > 1GB after a few hours I gave up... lol

Casper.

 

[John Nebi]

Actually the only real change you needed was this:
("DO" blocks do not provide transaction services)

DEFINE QUERY q-Table FOR Table.​

OPEN QUERY q-Table
FOR EACH Table
WHERE Table.Date >= i-Date
BY Table.Date.

GET FIRST q-Table.

REPEAT WHILE NOT QUERY-OFF-END("q-Table") :

[Many things]

GET NEXT q-Table.

END.

CLOSE QUERY q-Table.

 

[Casper]

Dauvissat:

On a very heavy database, the request lasted over 6 days :blush:. But the server went down and all changes were lost.

Actually the only real change you needed was this:
("DO" blocks do not provide transaction services)

I don't think changing the do block in a repeat block wil speed things up that much.

Casper.

 

[joey.jeremiah]

I also think, repeat will not change the transaction scope. From the Programming Handbook, Transaction Blocks ( 16-2 ) chapter.

<quote>

Any block that directly updates the database or directly reads records with EXCLUSIVE-LOCK likewise becomes a transaction block. This can be a procedure block, a trigger block, or each iteration of a DO, FOR EACH, or REPEAT block.

</quote>

There are references in the Transactions ( 16-2 ) and Managing Transactions ( 17-1 ) chapters.

But besides that you can just generate a listing file ( compile test.p listing test.lis ) and find out the buffers and transactions scope.

There are examples in the chapters mentioned above. HTH

 

[bulklodd]

Any block that directly updates the database or directly reads records with EXCLUSIVE-LOCK likewise becomes a transaction block. This can be a procedure block, a trigger block, or each iteration of a DO, FOR EACH, or REPEAT block.

It's not quite right I mean DO block. Try this:

DO:
   FIND FIRST <ANY DB BUFFER> WHERE EXCLUSIVE.
END.
/* Here transaction is still active */
MESSAGE TRANSACTION
   VIEW-AS ALERT-BOX INFO BUTTONS OK TITLE "DEBUG".

 

[John Nebi]

Aha but you are maybe missing the point. I'm talking about the SCOPE of the transaction, not the PRESENCE of one. The DO block carries no implicit transaction services, so it cannot affect the SCOPE of the transaction. A REPEAT block does carry implicit transaction services. Thus, in a DO loop, where the next outer block is the PROCEDURE itself, if you have 1000 records to update and something blows on record #1000, all 999 successful updates will be backed out.

In the REPEAT loop, which carries implicit transaction services, only update #1000 will be unsuccessful.

Of course if you code DO TRANSACTION, you can accomplish the same thing.

 

[bulklodd]

Aha but you are maybe missing the point.

I think it wasn't me who missed the point

Look at http://www.progresstalk.com/showpost.php?p=280002&postcount=3

 

[John Nebi]

Afterthought: And of course there will be more overhead with the DO block, because resources will be consumed in order to provide for possible rollback.

 

[John Nebi]

I think it wasn't me who missed the point

Look at http://www.progresstalk.com/showpost.php?p=280002&postcount=3

That post is irrelevant to what I was saying. You can simply replace the DO with a REPEAT and reduce the transaction scope to 1 record per iteration. That is a FACT. Check your documentation.

 

[John Nebi]

Here is is: Right at the beginning on the 4GL LR entry on REPEAT:

Begins a block of statements that are processed repeatedly until the block ends in one of several ways.
BLOCK PROPERTIES
Iteration, record scoping, frame scoping, transactions by default.

 

[Casper]

Hi John,

I remember we had this discussion earlier. And I get your point, but what I meant is that although there is (can be) overhead with a do while block IMHO it still doesn't acount for a query which takes 6 days (!!!) to run......

Casper.

 

[joey.jeremiah]

Bulklodd,

You are absolutely right, it was a stupid suggestion.

<snippet>

/* test.p */

DO WHILE TRUE.

FIND NEXT order EXCLUSIVE-LOCK.

END.

/* test.lis */

.\test.p 12/01/2005 17:14:43 PROGRESS(R) Page 1

{} Line Blk
-- ---- ---
1
2 1 DO WHILE TRUE.
3 1
4 1 FIND NEXT order EXCLUSIVE-LOCK.
5 1
6 END.
.\test.p 12/01/2005 17:14:43 PROGRESS(R) Page 2

File Name Line Blk. Type Tran Blk. Label
-------------------- ---- --------- ---- --------------------------------
.\test.p 0 Procedure Yes
Buffers: sports2000.Order
.\test.p 2 Do No

</snippet>



<snippet>

/* test.p */

REPEAT WHILE TRUE.

FIND NEXT order EXCLUSIVE-LOCK.

END.

/* test.lis */

.\test.p 12/01/2005 17:12:06 PROGRESS(R) Page 1

{} Line Blk
-- ---- ---
1
2 1 REPEAT WHILE TRUE.
3 1
4 1 FIND NEXT order EXCLUSIVE-LOCK.
5 1
6 END.
.\test.p 12/01/2005 17:12:06 PROGRESS(R) Page 2

File Name Line Blk. Type Tran Blk. Label
-------------------- ---- --------- ---- --------------------------------
.\test.p 0 Procedure No
.\test.p 2 Repeat Yes
Buffers: sports2000.Order

</snippet>

It's difficult to see, because of the trimmed spaces. But in the do while example the transaction is scoped to the procedure, while in the repeat example it's scoped to the repeat block.

 

[John Nebi]

Bulklodd,

You are absolutely right, it was a stupid suggestion.

Well, it's not stupid, either way works - it's just that a lot of people newer to the 4GL than I aren't aware of implicit services and end up coding a little extra to accomplish things.

 

[bulklodd]

That post is irrelevant to what I was saying. You can simply replace the DO with a REPEAT and reduce the transaction scope to 1 record per iteration. That is a FACT. Check your documentation.

No offence I don't see any reason to check my documentation again though... I can't say it's useless for all us to look through it from time to time

Regading your snippet I know you're right, quite right, you just forgot about the locks, I don't think SHARE-LOCK is a good thing but it's not important at this point anyway.