Using transactions with isolation: isc_tpb_read_committed + isc_tpb_no_rec_version + wait may easily lead to deadlocks. One such case discussed in firebird-support lead to next exchange...
Dmitry Kuzmenko wrote:
For me no_rec_version itself is the worst case, nearly useless. I even wonder why it exist - versioning engine is designed not to block readers.
Ann W. Harrison answers:
Very close. They were trying to emulate Sybase which was the foundation from which SQL Server emerged in the early 90's. The whole effort was contemporary with the creation of SQL Server.
When Borland bought Ashton-Tate which had bought InterBase, they brought in a new group of engineers who had worked on other products and considered InterBase's concurrency control to be odd and suspect. Their goal was to make it work like the systems they knew. Since the other systems didn't have record versions and couldn't read behind a blocking write, InterBase had to have that mode too. They ALSO tried to add an undo-log to replace MVCC completely, but failed to complete that change before Borland lost interest in InterBase.
The question of "wait" is less simple. In concurrency mode, "wait" is a good thing, although it usually just delays the news that your transaction isn't going to be able to delete or update the record that made it wait. If the transaction does not wait, but instead rolls back and retries, it will fail again (and again and again) until the transaction that made the conflicting change commits. Waiting quietly is better (at least cheaper) than retrying over and over.