On Thu, Apr 28, 2016 at 9:19 AM, Gabor Boros gaborboros@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

2016. 04. 28. 14:55 keltezéssel, Dimitry Sibiryakov sd@...
[firebird-support] írta:
> 28.04.2016 13:41, Gabor Boros gaborboros@... [firebird-support] wrote:
>> The message is:
>> deadlock
>> update conflicts with concurrent update
>> concurrent transaction number is ...
>
>    That's an expected behavior.

Are you sure?

In the language reference for "isc_tpb_read_committed + isc_tpb_wait" I
see this: "Update conflict exceptions can never be raised by an explicit
lock statement in this TPB mode."

http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-select.html#fblangref25-dml-with-lock

Hmmm.  Interesting.  The whole text in the block for isc_tpb_read_committed + isc_tpb_wait is:

"If there is an active transaction holding ownership on this record (via explicit locking or by a normal optimistic* write-lock), the transaction attempting the explicit lock waits for the outcome of blocking transaction and when it finishes, attempts to get the lock on the record again.

Update conflict exceptions can never be raised by an explicit lock statement in this TPB mode."

As you may know, the implementation for "Select ... with lock" is to add an update to the select statement.  The update doesn't change anything in the record, but does create a new version belonging to the current transaction.  That new version blocks other transactions from updating the record until it is committed or rolled back.  So the question really is the behavior of an update in read-committed/wait mode.  

In ancient history, even in read-committed mode, Firebird blocked transactions from updating records if the most recent version was created by a concurrent transaction.  At the time, we considered anyone who liked inconsistency not worth worrying much about.  It was a long time ago.  I thought that more recent Firebird versions allowed read-committed transactions to update records as long as the most recent version was committed.  If that's right, then the documentation is right.  But your tests suggest otherwise.  And maybe I'm just confused.  If not, I wonder if there might be some error in the back-off and retry under contention.  

Here's what happens during a wait (be patient, it's a little complicated).  Although Firebird doesn't lock records, it does maintain a lock table to control access to some shared resources.  When a transaction starts, it takes out an exclusive lock on its own transaction id.  When a transaction must wait for another transaction, it requests a shared lock on its transaction id.  When a transaction ends, its lock on its transaction id is freed - however it ended.  The lock management code signals the waiting transactions that they can get their lock - which they don't particularly want - they just want too wait for their predecessor's end.  In the case of waiting for a potential update conflict to end, the waiting transactions re-read the record they were trying to update and check the most recent version.  If read-committed transactions can update any committed record version, then the first waiting transaction sees the most recent version as committed and updates it.  In the case in point, that transaction's "Select ... with lock" succeeds and all is well.

But suppose there were several transactions all trying to update the same record and all waiting for the same transaction to finish.  The second and subsequent transactions could retry and see a new uncommitted version.  In theory, those transactions should request a new lock on the transaction that created the new uncommited record version and wait again.

Or maybe I'm confused.

Good luck,

Ann  

* When I studied concurrency control, "optimistic" concurrency control meant that all transactions just went to work ignoring each other,  When a transaction ended, it built a dependency graph of sorts between itself and its contemporaries and if that graph was clean, it committed, otherwise it rolled back.  That makes the working part of a transaction really fast and the commit both slow and unlikely to succeed in a contentious environment.  Firebird's concurrency control is not "optimistic" in that sense.  Like other "pessimistic" systems, in Firebird, a transaction blocks reads and updates to record versions it creates as it runs and frees the record versions when it commits.  That's why I growl whenever someone suggests that Firebird's concurrency control is "optimistic" - that sounds to me like a synonym for "stupid**". 

**I'm sure there are many applications where optimistic concurrency control is not stupid.  Mike Stonebraker has exploited most of them, though he analyzes the transactions before they're run rather than after.