Transaction Isolation Level

Be sensitive to the transaction isolation level used for queries. As a general rule, use the lowest isolation level you can so that you:

Avoid unnecessary database locks.
Reduce database processing

FTM includes the following functions to prepare queries with different isolation levels:

-- Default Isolation Level
CREATE FUNCTION PreProcessSQL(IN cSQL CHAR) RETURNS CHAR;

-- Uncommitted Read Isolation Level
CREATE FUNCTION PreProcessSQL_WithUR(IN cSQL CHAR) RETURNS CHAR;

-- Cursor Stability Isolation Level
CREATE FUNCTION PreProcessSQL_WithCS(IN cSQL CHAR) RETURNS CHAR;

-- Read Stability and hold UPDATE LOCKS
CREATE FUNCTION PreProcessSQL_UpdLock(IN cSQL CHAR) RETURNS CHAR;

-- Read Stability and hold UPDATE LOCKS
CREATE FUNCTION PreProcessSQL_ExcLock(IN cSQL CHAR) RETURNS CHAR;

All of these functions replace all instances of $DBSchema with the real schema.

It may be inefficient to first issue a select with the uncommitted read isolation level, and then later upgrade that lock (and therefore using a different isolation level) against the same row. So, if the unit of work is very short and it is certain that the row will be updated, it is recommended to not use uncommitted read first, but to choose a more suitable isolation level.