Tips for Optimizing Transaction Behavior - Firebird

Choose an Appropriate Transaction Model

The single-transaction model tempts the inexperienced developer to ignore multiuser issues in favor of “easy programming.” The result is application architectures that perform poorly at all levels: slow queries and refresh responses, glutted networks, user-unfriendly workflows, and high levels of conflict.

Don’t Go “Generic” Unless You Need To

Generic application -to -database interfaces, such as ODBC or Borland’s BDE layers, merge a single database connection and a single transaction. Because their purpose is to hide the differences between low-end, file-based data repositories and sophisticated, transaction-capable database management systems, they do not support the capability to have multiple transactions concurrently active in a database session or to have a transaction that spans connections to multiple databases.

At best, these generic interfaces provide an easy route for scaling up low-end databases or flattening out the differences between different vendors’ DBMS implementations. If you don’t need the lowest common denominator, don’t choose it.

Exploit Multiple Transaction Capabilities

A Firebird client can run multiple concurrent transactions. A user working on multiple tasks in a single application can perform a variety of activities over the same (or over-lapping) sets of target data. Firebird’s transaction model provides great benefits for designs that need to cater for a modular, multiple -tasking environment in a highly responsive manner. It is an important responsibility for the software engineer to develop techniques to keep commits flowing and user views synchronized with data-base state.

Keep the OAT Moving!

A slow-moving OAT almost always indicates long-running transactions. Avoiding them is one of the best skills you can acquire when learning to write client applications for Firebird.

It is easy to blame long transactions on user behavior. You can help them to help themselves by teaching them to complete tasks within a reasonable time: not to go for coffee breaks, leaving tasks unfinished; not to submit “wild queries” at peak times; and so on. However, good client application design avoids depending on users to behave well.

  • Mechanisms that “time out” neglected transactions are effective.
  • As a general rule, avoid browsing interfaces and favor the drill-down kind.
  • If a browsing interface is unavoidable, isolate the statements that select the browsed data in READ-ONLY READ COMMITTED transactions.
  • Ensure that READ-WRITE transactions are committed regularly—even if users are using them only to view data.
  • Restrict applications that permit random querying by enforcing the inclusion of WHERE clauses and timeout limits.
  • Make certain that your applications provide the means to perform periodic “hard commits” on any transactions that employ COMMIT RETAIN.
  • Make it a rule to use RollbackRetaining not more than once in an exception handler. Don’t put RollbackRetaining inside a loop!
  • Understand what’s going on inside transactions! Use gstat –h or equivalent tools to monitor the OIT and the OAT, and pay attention to the “gaps.”
  • Don’t allow housecleaning to be neglected. Sweeps must be allowed to happen at timely intervals, and regular backups, even without restoring, will help to keep the transaction inventory in good shape.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Firebird Topics