Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
10
Erster Beitrag:
vor 2 Jahren, 4 Monaten
Letzter Beitrag:
vor 2 Jahren, 3 Monaten
Beteiligte Autoren:
DarrenF, DerekT, Stefan Bentvelsen

[WD20] Transactions and isolation...

Startbeitrag von DarrenF am 16.02.2016 16:54

Hi,

Now I'm converting an app over to Client/Server mode, there's a whole new world of Transactions and "isolation" modes opening up in front of me - allowing me to be in control of commit points :xcool: ...something I've taken for granted in Oracle.

Q: Has anyone seen the need to use HTransactionIsolation yet?

Antworten:

Hi Darren,

I use it to set the mode to Read-committed so that other users only see finalized transactions:


lbTrsOk = HTransactionIsolation(pgctMyConnection,hReadCommitted)


BTW, it is only usable at HFSQL Client/Server, not in Classic mode which is always read-uncommitted.

von Stefan Bentvelsen - am 17.02.2016 11:57
Hi Stefan,

Thanks for responding...

Yes, I got that; only for C/S :spos:

After reading the Help, I think I'm going to use HTransactionStart with it's Read-Uncommitted default, but it's good to know about the alternative isolation modes. I just need to control the processing in the eventuality of an error during a database intensive bit of processing, so being able to carry out a ROLLBACK is very important to keep the consistency/integrity of the data.

Thanks again...

von DarrenF - am 17.02.2016 13:50
Darren
I am sure you know what you are doing but for others reading with less experience.

I have never found that 'one size fits all'.
Unlike the mainstream DB's HF makes all records written/modified available when using the default hReadUncommitted.
Great for testing/debugging but if you are in a multi user environment and users or queries access the data you are writing/modifying you can get unwanted consequences.

Sure we all follow the rules to keep transaction periods as short as possible but this is not always a option.
I have cases were I post to an accounts system via third party interface software and need to wait for a response before committing or rolling back - in this instance I use hReadCommitted.

Not sure what you mean by 'database intensive' but if it means that the transaction will be active for more than a few seconds I would recommend this option.

von DerekT - am 18.02.2016 12:57
Hi Derek,

Nope... I know what I'd like to happen... what I meant to say was that I'm going to "start" with Read-Uncommitted ;)

By database intensive, I mean several inter-related files/tables being read and modified one after another, but not as intensive as your example, i.e. it's not a batch-type process. However, it will (eventually), be several PCs (clients) hitting these product, stock, accounting tables.

Are you saying that record locking within transactions doesn't work in some way or is unpredictable or something else? Have you used HMode or HLockFile?

von DarrenF - am 18.02.2016 14:24
Darren
My understanding is that actual records taking part in a transaction are not locked automatically.
I have never read anything to indicate that they are.

I have not used hMode and would never want a complete file locked.

I guess it depends on what your application is designed for.

In my app all records that may be involved in posting to the accounts have a batch number.
As soon as these are accessed a record is written to the nicely named 'BatchLock' file which means only one user can ever post a given batch.

All other (single) records opened for editing are locked, usually as ReadWrite, in the normal way.
This regardless of whether they may be involved in a transaction or not.

The reason I use hReadCommitted is that there are many other windows where the records in the Transaction can be seen and/or updated plus many reports based on the files in question.
Having a user being able to see or report on records that are not yet part (uncommitted) of the data set serves no purpose and could well lead to any number of issues up the line.

As said I have not fully investigated the 'lock' status of a record that is part of a transaction.
If I get a mo at the weekend I may well have a play to see what the score is on that.

von DerekT - am 18.02.2016 17:19
Derek,

Thanks... I too haven't seen anything that mentions locking within transactions. Hmmm, HRead... statements can lock, but precisely the same statements within a Transaction ignore locking? ...that's interesting.

In that case, I might "start" with read-committed and see where my testing leads me.

I'll update this thread with my findings...

von DarrenF - am 18.02.2016 17:34
Hi Derek,

I've written some suitable code for the particular transaction and used the ReadCommitted mode as you suggested. Testing is going well... although I had to fix the fact that HTransactionIsolation doesn't "like" Classic mode (my app needs to be run in Classic & C/S modes).

...which leads onto my question; Do you use the HTransactionInterrupted function in your code to handle interrupted transactions due to crashes, power cuts (where UPS's aren't used ;) ) etc...?

I ask because I found a mention of this functions use in an old(ish) WX18 WLanguage manual (that doesn't seem to be produced any longer :-( ), that gives some example code that it advises can be added to your Project code to tidy-up any transaction files that may be "hanging around" due to unforeseen "crash" type events.

I then found the corresponding entry in the online Help (which seemed to be buried a bit):

http://help.windev.com/en-US/?3044336&name=Transactions_Operations

von DarrenF - am 24.02.2016 12:26
Darren

I use the following, declared and run during project initialization.

PROCEDURE pCloseTransactions()

lsTransactID,lsTransactItem,lsTransactList,lsTransactStatus is string

// Retrieve the list of transactions
lsTransactList = HTransactionList(:msConnection)
IF lsTransactList "" THEN
//If found then check the current status
FOR EACH STRING lsTransactItem OF lsTransactList SEPARATED BY CR
lsTransactID = ExtractString(lsTransactItem,1,TAB)
lsTransactStatus = ExtractString(lsTransactItem,5,TAB)
//Cancel transaction is status = 0 (Interrupted)
IF HTransactionInterrupted(lsTransactID) = True THEN
HTransactionCancel()
END
END
END



This clears interrupted transactions when the user restarts.

I also have (somewhere) a variation on this used in an admin window to allow the app admin to clear any languishing transactions

von DerekT - am 24.02.2016 13:50
Hi,

Thanks for the code Derek.

I seem to remember there's a HFCS control centre area for this kind of thing, although an interactive version embedded in my app may be a convenient function for the admin :spos:

Thanks again...

von DarrenF - am 24.02.2016 14:49
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.