On Mon, Dec 29, 2014 at 4:55 AM, hvlad@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

 

Walter,

there are few reasons:

a) MVCC engine never block records - it just can't :)

b) the only way to disable concurrent updates of the same record is... update :)
  More exactly: engine must mark primary record version by current transaction and
it will make concurrent writes wait or fail.

c) therefore, i can't imagine how it is ever possible to block record *before* reading it

d) Firebird allows developer to avoid such problem (see below) but doesn't force developer to do it.

  To avoid exactly this kind of issues Firebird have ablity to "lock" record while reading (i enclosed
"lock" into brackets because there is no true record locks in Firebird, see (a) above).

  So, to "lock" record engine must update it and there is special statement to read-and-update:
SELECT ... WITH LOCK

  So, if you really don't want to handle update conflicts and decided to wait for concurrent updates,
you should issue SELECT ... WITH LOCK before UPDATE (and, of course, use "read-commited no
record version" transaction isolation mode with non-zero wait timeout).

Regards,
Vlad

---In firebird-support@yahoogroups.com, <sistemas2000profesional@...> wrote :

Vlad, your explanation is very good, but I still don't understand something: Why before step 1 the engine does not blocks the record avoiding such problem?

Greetings.

Walter.

On Sat, Dec 27, 2014 at 5:19 AM, hvlad@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

 

  Update, internally, consists of following steps (simplified) :

1. read record
2. evaluate new record
3. write new record into data page

really, it is a more complex, but for now we interesting only in steps above.

At step 1, read-committed (RC) transaction waits for commit or rollback of concurrent active transaction and thus reads only comitted record version.

At step 3, transaction re-reads original record version and must ensure that it is still the same record version as it was at step 1 (else we will silently replace other's work). If current record version was changed since step 1, update_conflict error will be reported - exactly as you see.
Again, it is simplified description.

  Note, time slice between steps 3 and 1 usually very small. When you test concurrent updates in isql, you can't reproduce such scenario. But in real life, when you have a lot of concurrent updates by applications it becomes much more probably and really happens sometimes.

Hope it helps,
Vlad