On Mon, Feb 27, 2012 at 3:38 AM, nathanelrick <[email protected]> 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
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*, 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. 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. Good luck, Ann * 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.
