Oracle Performance and locking – PB Docs 126

Oracle Performance and locking

An important consideration when designing a database application
is deciding when CONNECT and COMMIT statements should occur to maximize performance
and limit locking and resource use. A CONNECT takes a certain amount
of time and can tie up resources during the life of the connection.
If this time is significant, then limiting the number of CONNECTs
is desirable.

After a connection is established, SQL statements can cause
locks to be placed on database entities. The more locks there are
in place at a given moment in time, the more likely it is that the
locks will hold up another transaction.

Rules

No set of rules for designing a database application is totally
comprehensive. However, when you design a PowerBuilder application,
you should do the following:

  • Long-running connections

    Determine whether you can afford to have long-running connections.
    If not, your application should connect to the database only when
    absolutely necessary. After all the work for that connection is
    complete, the transaction should be disconnected.

    If long-running connections are acceptable, then COMMITs should
    be issued as often as possible to guarantee that all changes do
    in fact occur. More importantly, COMMITs should be issued to release
    any locks that may have been placed on database entities as a result
    of the statements executed using the connection.

  • SetTrans or SetTransObject function

    Determine whether you want to use default DataWindow transaction
    processing (the SetTrans function) or control the transaction in
    a script (the SetTransObject function).

    If you cannot afford to have long-running connections and
    therefore have many short-lived transactions, use the default DataWindow
    transaction processing. If you want to keep connections open and
    issue periodic COMMITs, use the SetTransObject function and control
    the transaction yourself.

Example 1

This script uses embedded SQL to connect to a database and
insert a row in the ORDER_HEADER table and a row in the
ORDER_ITEM table. Depending on the success of the statements
in the script, the script executes a COMMIT or ROLLBACK.

note.png Error checking

Although you should test the SQLCode after every SQL statement,
these examples show statements to test the SQLCode only to illustrate
a specific point.

Example 2

This example uses the scripts for the Open and Close events
in a window and the Clicked event in a CommandButton to illustrate
how you can manage transactions in a DataWindow control. Assume
the window contains a DataWindow control dw_1 and the user
enters data in dw_1 and then clicks the Cb_Update
button to send the data to the database.

Since this script uses SetTransObject to connect to the database,
the programmer is responsible for managing the transaction.

The window Open event script:

The CommandButton Clicked event script:

The window Close event script:


Document get from Powerbuilder help
Thank you for watching.
Was this article helpful?
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x