*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.
|
|
Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.
|
Breck Carter
Last modified: May 28, 1996
mail to: bcarter@bcarter.com
(This article was originally published as the cover story in the November 1994 issue of PowerBuilder Developer's Journal. It specifically mentions PowerBuilder 3 and Sybase 4.x but experience has shown the main points still apply to PowerBuilder 4 and other versions of SQL Server, even to Informix.)
The communication between the Client and the Server is the most underestimated and misunderstood of all programming tasks in a multi-user Client Server environment. Handling the database traffic in a manner which maximizes concurrency and ensures consistency is critical to the success of any project. It is substantially more important than anything else a developer does inside PowerBuilder.
For example, consistency demands that two banking machines must be prevented from checking the balance and withdrawing the full amount from the same account at the same time. Concurrency demands that many banking machines be allowed to check balances and withdraw funds at the same time.
A system which allowed only one user at a time would certainly preserve consistency but it wouldn't be very popular. Neither would a system that let two customers go into overdraft on a joint account without warning. Consistency is an absolute requirement, but concurrency is important too.
This article will focus on Transaction Management with PowerBuilder Version 3's native DBLIB interface to Sybase and Microsoft SQL Server Versions 4.x, henceforth simply referred to as "Sybase". That covers most current installations, from Version 4.2 running on OS/2 to Version 4.9 running on UNIX. Although some of the basic concepts apply other databases such as Watcom, many of the techniques are specific to Sybase. In particular, Sybase System 10, CTLIB, Microsoft SQL Server 95 and ODBC will not be discussed.
As a PowerBuilder programmer, it's up to you to take control of Sybase transactions and manage them. If you don't, PowerBuilder will do it for you, and you might not like the results.
Consider this example of a school bus company: a Driver Dispatch system was developed to show who's supposed to be driving which bus, who's late, and to let the dispatcher sign drivers in as they arrive for work. One workstation was used by the dispatcher, and another was set up in display-only mode for all to see the current status.
As the day wore on, the Sybase transaction log would begin to fill up. By noon the situation became critical, and repeated attempts to dump the transaction log failed to free up any space. Finally, in the middle of the afternoon rush, the log became completely full and the server had to be shut down.
The frustrating part was that the dump commands seemed to work OK. They didn't produce any error messages, and they ran very quickly, but they just didn't do anything. Folks started asking "did you run the dump with the Do-Nothing option?"
The problem lay with the display-only workstation. The programmers had followed conventional wisdom, setting AutoCommit=False in the transaction object to allow "normal recoverable transaction handling" (page 19 of the PowerBuilder SQL Server Interface manual). This meant when the workstation was turned on in the morning, the first Connect issued a Sybase BEGIN TRANSACTION command. Even though nothing was being updated, Sybase recorded that BEGIN TRANSACTION command on the database log to indicate that a new transaction was starting. The Connect was followed by an endless series of SELECT statements every few minutes. Because it wasn't updating anything, this workstation didn't do any Commits or Rollbacks, so Sybase never recorded that this transaction had ended.
Other workstations, of course, were busy making updates. All these changes were recorded in the log and it started to fill up. When an attempt was made to free up space, the dump utility looked for the oldest incomplete transaction on the log. It then removed all the records for transactions that were completed before the oldest incomplete one started. The only trouble was that all the other transactions came after that, so nothing got removed.
It didn't matter that every program used SetTransObject instead of SetTrans, or that they carefully performed a commit or rollback after every update. Increasing the size of the log file worked for a while but the problem returned when more workstations were added.
The only thing that seemed to work was to turn the display-only workstation off and on again every once in a while. Now Sybase could finally record the old transaction as complete; now it was no longer the oldest incomplete transaction in the log, and now the dump utility could remove the other records.
This example might seem extreme but it has been repeated in many installations. All it takes is for one casual user to start a PowerBuilder application and leave it minimized all day long. Or someone to retrieve a long list of data and spend several hours browsing through it. Several solutions have been proposed (e.g., halt idle applications, issue a commit after every retrieve(), set Truncate Log on Check Point, etc.) but they don't always work. What's required for this and other problems is a thorough understanding of Sybase transactions and their management.
In general a Sybase transaction should never span a long wait. This often happens when the user has control as when scrolling through a DataWindow or looking at a MessageBox. Long running transactions may cause problems with the log file. If locks are being held, other users might not get any work done while this one takes a lunch break.
Because the program has no control over how long it has to wait, a good rule of thumb is to "Never give the user input focus while a Sybase transaction is running!" The whole idea is to prevent any transactions from starting until they're really needed, and once one is started to finish its work as soon as possible. To do this a program must take complete control of transaction management beginning with the first connection to Sybase.
The easiest way to establish a connection is to use PowerBuilder's Connect. If AutoCommit is set to False, however, the Connect statement will actually do two things: establish a connection to Sybase and then immediately issue a Sybase BEGIN TRANSACTION. If you want complete control over Sybase transactions, AutoCommit must be set to True when connecting.
Listing 1 shows how to establish a Sybase connection without starting a Sybase transaction. First, a few fields in the transaction object SQLCA are filled in. Then AutoCommit is set to True and the Connect statement is executed, followed by a check for errors.
// Initialize Transaction Object.
SQLCA.DBMS = "Sybase"
SQLCA.ServerName = "DSQUERY"
SQLCA.Database = "DMMPD1A"
SQLCA.DBParm = ""
SQLCA.LogID = "Frank"
SQLCA.LogPass = "********"
SQLCA.AutoCommit = true
// Connect Transaction Object.
connect using SQLCA;
if SQLCA.SQLCode <> 0 then
MessageBox ( "Error", &
"Connect SQLCode = " &
+ string ( SQLCA.SQLCode ) )
halt close
end if
A program may establish more than one connection to Sybase, either by using more than one transaction object at the same time and issuing multiple Connects, or by disconnecting and reconnecting the same transaction object. To keep things simple this article sticks to one connection and one transaction object (SQLCA).
Because it really doesn't have much to do with Sybase transactions, the transaction object SQLCA should really be called a "connection object". Of course, you need a connection before you can execute transactions, but that's as far as the relationship goes.
Once a connection has been established, it is possible to retrieve data without starting a Sybase transaction. To do this, you must associate your DataWindow with the connection via SetTransObject and then execute a Sybase SELECT via retrieve() as shown in Listing 2.
This article assumes you have already decided to use SetTransObject instead of SetTrans. Using SetTrans has profound implications for both connections and transactions, and is not discussed here.
integer li_RC
// Associate DataWindow with Connection.
li_RC = dw_1.SetTransObject ( SQLCA )
if li_RC <> 1 then
MessageBox ( "Error", &
"SetTransObject return code = " &
+ string ( li_RC ) )
halt close
end if
// Issue Sybase SELECT.
li_RC = dw_1.retrieve()
if li_RC <> 1 then
MessageBox ( "Error", &
"Retrieve return code = " &
+ string ( li_RC ) &
+ ", SQLCode = " &
+ string ( SQLCA.SQLCode ) )
halt close
end if
At this point, control may be given to the user to browse the data and make changes. Because no transaction has been started, no locks are being held, so concurrency is very good. In other words, this user hasn't locked anything so other users are free to get on with their work.
Once the user has decided to update the database, the program should take control and execute a Sybase transaction from beginning to end. The transaction is started with an Execute Immediate "BEGIN TRANSACTION" statement as shown in Listing 3.
A call to update() will send the necessary Sybase INSERT, UPDATE and DELETE commands to Sybase. If the update() worked, the transaction is committed, otherwise it is rolled back. Note that with functions like SetTransObject(), retrieve() and update() it's the return code that must be checked first; these functions sometimes detect errors that don't put anything into SQLCode.
Execute Immediate statements are also used to directly issue the Sybase COMMIT TRANSACTION and ROLLBACK TRANSACTION commands. While the PowerBuilder Commit and Rollback statements do currently work to generate the appropriate Sybase COMMIT TRANSACTION and ROLLBACK TRANSACTION commands when AutoCommit is True, this may be risky. It contradicts the manual, and Powersoft may determine that this is a bug and not a feature. By not taking advantage of this "feature" you will ensure that your application will not break at some point in the future.
Setting AutoCommit to False just so you can use PowerBuilder's Commit and Rollback is out of the question because it causes these statements to issue a Sybase BEGIN TRANSACTION immediately after their respective COMMIT and ROLLBACK TRANSACTION commands. We don't want to do that because it would mean the beginning of another Sybase transaction, and we're going to give back control to the user at this point.
It is important to note that if the update() fails the program must do an immediate rollback before informing the user. MessageBox calls can cause long waits, and we want the transaction to be completed before that happens. This means that you will need to save the existing contents of the transaction object SQLCA if you want to display this information when the Message Box is finally displayed. The reason is that the SQLCA will get changed by the ROLLBACK TRANSACTION command, and you will lose the information associated with the original update() error.
integer li_RC
string ls_command
transaction ltxn_save
// Issue Sybase BEGIN TRANSACTION.
ls_command = "BEGIN TRANSACTION"
execute immediate :ls_command using SQLCA;
if SQLCA.SQLCode <> 0 then
MessageBox ( "Error", &
"BEGIN SQLCode = " &
+ string ( SQLCA.SQLCode ) )
halt close
end if
// Issue Sybase INSERT/UPDATE/DELETE(s).
li_RC = dw_1.update()
if li_RC = 1 then
// Update OK: Issue Sybase COMMIT.
ls_command = "COMMIT TRANSACTION"
execute immediate :ls_command using SQLCA;
if SQLCA.SQLCode <> 0 then
MessageBox ( "Error", &
"COMMIT SQLCode = " &
+ string ( SQLCA.SQLCode ) )
halt close
end if
else
// Not OK: Save current txn object,
ltxn_save = create transaction
ltxn_save.SQLCode = SQLCA.SQLCode
// (also copy other attributes if desired)
// ...then Issue Sybase ROLLBACK,
ls_command = "ROLLBACK TRANSACTION"
execute immediate :ls_command using SQLCA;
if SQLCA.SQLCode <> 0 then
MessageBox ( "Error", &
"ROLLBACK SQLCode = " &
+ string ( SQLCA.SQLCode ) )
halt close
end if
// ... and finally tell the user.
MessageBox ( "Sorry!", &
"Update failed. SQLCode = " &
+ string ( ltxn_save.SQLCode ) )
destroy ltxn_save
end if
It may be tempting to control a program's behavior by changing the value of AutoCommit while the program is connected to the database. This has a very confusing side-effect: PowerBuilder will immediately issue a Sybase BEGIN TRANSACTION when AutoCommit changes from True to False, and a COMMIT TRANSACTION when it goes from False to True. The method shown in this article is much clearer and more straightforward: Don't muck about with AutoCommit, just leave it set to True and issue explicit Sybase commands.
When it's time to disconnect from Sybase, the easiest way is to use the PowerBuilder Disconnect statement. The only problem here is that Disconnect automatically issues a Sybase COMMIT TRANSACTION before terminating the connection. With proper program design, this shouldn't matter because there shouldn't be any pending updates at this point.
However, errors do occur. If a disconnect is issued from the application close or SystemError scripts then database consistency may be threatened; the program may be halfway through a save operation when a runtime error occurs. Listings 4 and 5 show a safe approach: a Sybase ROLLBACK TRANSACTION is issued before disconnecting.
The code shown is Listing 4 is appropriate for a SystemError or application close event. No error checking is performed because there's not much that can be done if one is detected.
string ls_command // Issue Sybase ROLLBACK. ls_command = "ROLLBACK TRANSACTION" execute immediate :ls_command using SQLCA; // Disconnect Transaction Object. disconnect using SQLCA;
The function shown in Listing 5 may be called in situations where the user is going to continue working after disconnecting from Sybase; e.g., when changing to a different database. Error checking is appropriate here, but only if the caller is going to handle a -1 return code.
string ls_SQL
// Issue Sybase ROLLBACK.
ls_SQL = "ROLLBACK TRANSACTION"
execute immediate :ls_SQL using SQLCA;
if SQLCA.SQLCode <> 0 then
MessageBox ( "Error", &
"ROLLBACK SQLCode = " &
+ string ( SQLCA.SQLCode ) )
return -1
end if
// Disconnect Transaction Object.
disconnect using SQLCA;
if SQLCA.SQLCode <> 0 then
MessageBox ( "Error", &
"Disconnect SQLCode = " &
+ string ( SQLCA.SQLCode ) )
return -1
end if
return 1
Proper program design to control the length of Sybase transactions is only one part of transaction management. In future articles, we'll discuss concurrency control, isolation levels, locking and other related subjects.