Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Paradox Programming Articles  |  Beyond Help Articles  |  Tips & Tricks Articles  


Transaction Basics
© 2001 Stefan Bodingh

Preface

A transaction is a group of actions that must all be carried out successfully on one or more tables in a database before they are committed (made permanent). If any of the actions in the group fails, then all actions are rolled back (undone).

Transactions protect against hardware failures that occur in the middle of a database command or set of commands. They also form the basis of multi-user concurrency control on SQL servers. When each user interacts with the database only through transactions, one user's commands can't disrupt the unity of another user's transaction. Instead, the SQL server schedules incoming transactions, which either succeed as a whole or fail as a whole.


Locking Strategies

There are two locking strategies and SQL (server-based) systems usually use Optimistic locking, while fileserver-based systems like Paradox® use Pessimistic locking.

Pessimistic locking -- As soon as a user attempts to change a record, the record is locked. No other user can change the record until the first user finishes editing, or cancels the changes. This is good, in that a user who successfully obtains a lock can definitely complete the editing operation. It's also bad, because a user can monopolize a record indefinitely. This mode requires more system resources and introduces recovery issues, but is more suitable for situations when conflicts are likely.

Optimistic locking -- It takes a snapshot of the record prior to its being modified. When the record is unlocked, the BDE/SQL-Link queries the server for the record, locks it, checks each field value by value to see if any changes have taken place since the user touched the record. If the state of the record has not been changed, the old record is updated with the new values and the lock is released. If the record on the database server has been changed since the record was last retrieved, the BDE/SQL-Link generates an exception and does not process the change.

For many situations, pessimistic locks are better because they give the programmer more control with which to protect the user and the data. It will also be my main subject in this article.


Working with Transactions in Paradox

In order to work with transactions on Paradox tables, you have to set StandardTransactions to true in the Registry. This is done automatically from version 8 of Paradox for the user that installed Paradox, but when you add a new user (NT), the new user has StandardTransactions set to false. (See end of article for details.)

Transactions are efficient, when you need to update more than one table at a time. Let's say if you need to update three different tables at one time and the last update fails or the user presses the cancel key, you can easily revert all completed changes in the other two tables, and instruct the user to try again. There is a limitation of 255 locks on a table and any change to a table during a transaction creates a lock (unless you do a write lock on the table). So you have to make a choice before you reach the limit to commit or to rollback.

If you read the ObjectPAL Reference, you should note that the transactionActive method always returns false on Paradox tables, but you can check on the beginTransaction method instead, because it will return false if another transaction is active and true if not.

Below are some procedures I've used to handle transactions. You could test it all out by setting out three buttons on a form. Name them "Start Trans", "Commit Trans" and "Rollback Trans".

In the "Start Trans" pushButton() you insert StartTrans() and so on. In the init event you could insert the chkTrans() method, described below.

Please insert the following code at the top level of your form:
Var
  transOn Logical
  db      Database ;// handle to db
endVar

;----------------------------------------------------------
Proc StartTrans() Logical
if transOn then
  ;msgStop("Problem","Transactions are already started")
else
  if db.beginTransaction() then
    transOn=True
  else
    errorshow()
    return False
  endIf
endIf
return True
endProc
;----------------------------------------------------------
Proc CommitTrans() Logical
if transOn then
  if db.commitTransaction() then
    transOn=False
  else
    errorshow()
    return False
  endIf
else
  ;msgStop("Problem","Transactions are already stopped")
endIf
endProc
;----------------------------------------------------------
Proc RollbackTrans() Logical
if transOn then
  if db.rollbackTransaction() then
    transOn=False
  else
    errorshow()
    return False
  endIf
else
  ;msgStop("Problem","Transactions are already stopped")
endIf
endProc
;----------------------------------------------------------
The way I have checked transaction capability is in the method below. I've used it in the startup script to check if Transactions are supported or not. You could also put it in your init event.
method chkTrans() logical
var
  db   Database
  bool Logical
endVar

if not db.open(":PRIV:") then
  errorShow("Could not open the database.")
  return false ; exit the method
endIf
bool = True
if db.beginTransaction() then ; begin a transaction
  if NOT db.rollbackTransaction() then
    errorShow("Transaction NOT rolled back.")
  endIf
else
  bool = False
  if errorCode() = peDatabaseErr then
    msgStop("Problem","Transactions are NOT supported")
  else
    errorshow()
  endIf
endIf
db.close()
return bool
endMethod

Registry Info
The following are examples of where the StandardTransactions registry value can be found. Based on these examples, you should be able to locate the relevant registry entry on your own computer. Please note that by default, Paradox 7 has StandardTransactions disabled. Later versions seem to default to enabled; this is for the user that installed Paradox; in NT, where you may use the same PC with a different user login, additional users have had StandardTransactions disabled until you enable them.

Paradox 7
HKEY_CURRENT_USER\Software\Borland\Paradox\7.0\Pdoxwin\Properties

Paradox 7 Runtime
HKEY_CURRENT_USER\Software\Borland\Paradox Runtime\7.0   \PDOXWIN\Properties

Windows 9x, Paradox 8:
HKEY_CURRENT_USER\Software\Corel\Paradox\8.0\PDOXWIN\Properties

Windows NT, Paradox 8:
HKEY_CURRENT_USER\Software\Corel\Paradox\8.0\PDOXWIN\Properties

Windows 2000, Paradox 10:
HKEY_CURRENT_USER\Software\Corel\Paradox\10.0\PDOXWIN\Properties
HKEY_LOCAL_MACHINE\SOFTWARE\COREL\Paradox\10.0   \PDOXWIN\Properties

Download a sample form with the above code for trying out transactions first hand.


Discussion of this article


 Feedback |  Paradox Day |  Who Uses Paradox |  I Use Paradox |  Downloads 


 The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation.
 Paradox is a registered trademark of Corel Corporation.


 Modified: 15 May 2003
 Terms of Use / Legal Disclaimer


 Copyright © 2001- 2003 Paradox Community. All rights reserved. 
 Company and product names are trademarks or registered trademarks of their respective companies. 
 Authors hold the copyrights to their own works. Please contact the author of any article for details.