Gotchas

Read Committed isolation

READ COMMITTED isolation level allows one transaction to see changes committed by concurrent transactions. However there are some specifics related to Firebird:

Gotcha 1

READ COMMITTED transaction can successfully update record modified by a concurrent transaction if it was committed before update operation. To better understand this let’s consider following situation (I use pseudo-code):

  A.startTransaction();
  B.startTransaction();

  A.selectRowById(accountId = 1);      // balance = 5
  B.selectRowById(accountId = 1);      // balance = 5

  A.updateRowById(accountId = 1, 
      set balance = balance - 3);
  A.commit();                          // now balance is 2, but B believes it is 5

  B.updateRowById(accountId = 1,       // <--- Ups! we get no lock conflict!
      set balance = balance - 4);
  B.commit();                          // now balance is -2, not 1

Transaction B will successfully commit unless there is a database integrity constraint that checks that the balance is not negative. This is related to the way Firebird handles READ COMMITTED transactions, REPEATABLE READ and SERIALIZABLE isolations work as expected.

Gotcha 2

READ COMMITTED transaction might provide inconsistent view of the database during big selects, if concurrent transactions performs commit. For example, if transaction A performs select on a table where each record has monotonously increasing number with no gaps (1, 2, 3, 4, 5, and so on), and transaction B perfoms UPDATE test_table SET counter = counter + 1 and commits, transaction A will eventually will see a gap caused that new values are visible to it.

When REPEATABLE READ is used, transaction A sees the snapshot of the database at a time of transaction start.

SELECT FOR UPDATE

Firebird provides a possibility to execute SELECT ... FROM ... WHERE ... FOR UPDATE statements. When executed in database servers with pessimistic locking scheme this puts a lock on records. Transactions trying to update locked records get lock conflict error. In Firebird this statement does virtually nothing. Locks are places only when the record is actually updated.

Until Firebird 1.5 the only possible way to lock records in a pessimistic way was to execute a dummy update UPDATE ... SET someColumn = someColumn WHERE .... Firebird 1.5 provides new syntax SELECT ... FROM ... WHERE ... FOR UPDATE WITH LOCK. For more information see release notes from Firebird 1.5.

Cursor Unknown

Q: When I attempt to retrieve the record using ResultSet.next() the following error is thrown:

 org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569.
 Dynamic SQL Error SQL error code = -504
 Cursor unknown

A: Most likely following happens:

  PreparedStatement pstmt = conn.prepareStatement("select * from some_table");
  ResultSet rs = pstmt.executeQuery();
  conn.commit();
  while(rs.next()) {
      ...
  }

In Firebird result sets do not survive commit or rollback. Application must check result of DatabaseMetaData.supportsOpenCursorsAcrossCommit() method call. In Jaybird it always returns false.

Another option is to use “commit retain”. Jaybird does not provide method to invoke this API call, however Stefano Magi reported that executing COMMIT RETAIN statement solves the issue.

One-char Unicode column

According to the SQL standard, columns defined as CHAR are padded with spaces to their full declared length. However, if column is defined as CHAR(1) CHARACTER SET UNICODE_FSS either implicitly (inheriting default character set of the database) or explicitly, it can happen that server returns more than single-character string. This depends on the character stored in the column, or more correctly, on its byte representation in UTF-8 encoding. If the character is represented by a single byte, it will be padded with two spaces, if it takes two bytes to represent a character, only one space will be added. This happens due to incorrect Unicode handling in the server. Jaybird versions lower than 1.5 RC3 do not provide any workaround for this issue. The only possible workaround in this case is to convert CHAR columns into VARCHAR, they are not padded with spaces. Starting the version 1.5 RC3 Jaybird driver contains a workaround for this issue.

Two gigabyte Blob limit

TODO: Verify if still relevant with recent Firebird versions

Firebird has two types of BLOB fields: segmented and stream. When they are accessed from Jaybird, the only difference visible to application is that seek operation is not defined for a segmented blobs. The biggest difference between segmented and stream blobs is the way they are physically stored in the database. In theory segmented blob does not have practical size limit. However due to the fact that blob length is stored in the 32-bit integer that is sometimes interpreted as unsigned and sometimes as signed integer, the practical safe limit of the single blob field is 2GB. After that limit stream blobs might become something called "write-only memory", segmented BLOBs might work, but not in all cases.

Unfortunately Firebird does not throw an exception when BLOB size reaches 2GB, therefore applications must ensure that this limit is not crossed.

BETWEEN with BIGINT columns

When fieldName BETWEEN 0 AND ? clause is used with the BIGINT columns with Firebird 1.5.x, driver will report the column to be of INTEGER type. This is a bug in Firebird 1.5.x server and is already fixed in Firebird 2.0.

A workaround is to convert the clause above into fieldName >= 0 AND fieldName <= ?.