nathanelrick wrote:
What is the most fastest isolation level ? I know the behavior of each, but i need to know the difference in speed / resource usage between each of them ...
isc_tpb_concurrency isc_tpb_consistency isc_tpb_read_committed + isc_tpb_rec_version isc_tpb_read_committed + isc_tpb_no_rec_version
Ann W. Harrison answers:
isc_tpb_consistency can cause performance problems due the fact that it's locking tables and possibly excluding concurrent access.
isc_tpb_concurrency is the design center for Firebird. Readers don't block writers, writers don't block readers, and both get a consistent view of the database.
isc_tpb_read_committed + isc_tpb_rec_version + isc_tbp_read_only give inconsistent results and occasionally produces an error on a blob read [1], but unlike other modes, it does not block garbage collection so it's a good mode for long running read transactions that don't have to get the "right" answer.
isc_tpb_read_committeed + isc_tpb_rec_version has the same performance as isc_tpb_concurrency, but gets inconsistent results - the same query run twice in the same transaction may return different rows.
isc_tpb_read_committed + isc_tpb_no_rec_version + isc_tpb_wait is slower than other modes because it will wait for a change to be commited rather than reading the newest committed version. Like all variants of isc_tpb_read_committed, it does not produce consistent results.
The problem is that the read_committed transaction will fail if it tries to update a record version created by a concurrent transaction, even if that transaction has committed. It's kinda dumb, but that's the way it's implemented. Read-committed transactions follow the same update concurrency rules as consistent read transaction. Even though a read-committed transaction can read a record version that was created and committed by a concurrent transaction, it can can't update that version.
isc_tpb_read_committed + isc_tpb_no_rec_version + isc_tpb_no_wait gives lots and lots of deadlock errors because every time a reader encounters a record that's being changed, it returns an error.
I stand by my preference for concurrency as an isolation mode.
- [1] If you have the bad luck to read a record version which includes a blob and someone else changes the record version and the blob, and that transaction commits, and somebody else comes by and garbage collects the record version and blob before you get around to reading the blob, you'll get an error. Doesn't happen in other modes.