On 11/30/2010 12:54 PM, Dmitry Yemanov wrote:

> 30.11.2010 19:17, Jim Starkey wrote:
>
>> The algorithm is simple, though the details might not be. When a record
>> is fetched "for update" in write committed mode:
>>
>> 1. Look at the head record version. If the transaction that created
>> it is committed, do a dummy update and return.
>> 2. If the transaction that created the head version is active, wait
>> for it to complete. Then go back to step #1
> So do you ignore this scenario:
>
> txn 1: update tab set col = 2 where col = 1
> txn 2: select * from tab where col = 1 for update
> txn 1: commit
>
> and lock the record which supposedly shouldn't have been locked?
>
> Or, once again, I'm missing something?

I presume each is operating in "write committed" mode. Txn 1 creates a
new version with "col = 2". Txn 2 goes to fetch the record (either
indexed or exhaustive), blocking pending txn1's commit, does a dummy
update of the record, then filters out the record so it doesn't see it.
When it commits or rolls back, unconsummated dummy updates get undone.

If you argument is that it locks a record it didn't intend to, yup, it
does. If there were an index on "col", it might lock a whole bunch of
unintended records leading to a Jonestownian mass deadlock.

Nobody is claiming that this is a virtuous behavior. It isn't. It's a
hack to deal with applications written by idiots. Would we ever
recommend that a user use this? Nope. We'd say use a sequence, dummy,
that's what they're for. But we can't make a dumb user smarter, let
alone change tpcc/dbt2 to use standard SQL in an appropriate manner.

My personal goal for all of these decades is to make concurrency /
consistency trade-offs that mortals can use and give predictable and
useful results. I detest "read committed", "write committed", and,
well, modes in general.

--
Jim Starkey
Founder, NimbusDB, Inc.
978 526-1376