From: Kevin Lewis
Date: December 4 2008 5:20pm
Subject: Re: Bug 41194
List-Archive: http://lists.mysql.com/falcon/258
Message-Id: <49381142.6020803@sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=GB2312
Content-Transfer-Encoding: 8BIT

Xuekun,

Please see Ann's reply.  Yes two transactions wait on the same row.
Note that this happens a lot more on something like DBT2 than it does in
real life, because database developers try to avoid that in schema and
application design.

Hu, Xuekun wrote:
> Hi, Kevin
> 
> Can you explain a little about the scope of the same record that two transactions are working on? At least the record is not the same as row. 
> 
> Thx, Xuekun 
> 
> -----Original Message-----
> From: Kevin.Lewis@stripped [mailto:Kevin.Lewis@stripped] 
> Sent: 2008年12月4日 14:52
> To: Hu, Xuekun
> Cc: Ann W. Harrison; Kevin Lewis; Jim Starkey; FalconDev; Yu, Zhidong; Fang, Xiang; Duan, Jiangang
> Subject: Re: Bug 41194
> 
> Xuekun,
> 
> Thank you for following this discussion and offering your perspective.
> It is a very interesting idea.
> 
> The step where the transaction is synchronously moved trans from Active
> to committed lists is a engine wide doorway.  There is only one
> transaction manager and it controls those two lists.  All commits must
> go through there exclusively and many other threads use those lists with
> shared locks, for example, to call findTransaction().
> 
> Transaction::waitForTransaction is usually called when one transaction
> needs to make a change to the same record that is involved with another
> uncommitted transaction.  It is not at the table level, just a wait by
> one thread for another.
> 
> But that wait is currently waiting on the serialized portion of
> Transaction::commit.  So your idea is interesting.
> 
> There are many places in the code that either use the committed list or
> the active list, assuming that non-committed transaction are only found
> on the active list, and only committed transactions are found on the
> committed list.  This change would break that assumption.  We would need
> to investigate each of those to see what the affect would be.
> 
> So I will have to get back to you on that...
> 
> Thanks again for the suggestion.
> 
> Kevin
> 
> 
> Hu, Xuekun wrote:
>> Hi, Ann & Kevin
>>
>> I happened to read Transaction::commit() code, since I found most contention are due to transaction commit, some transactions are waiting at waitForTransaction due to other transaction::syncIsActive is locked. 
>>
>> So I wonder does this order violate MVCC semantics? 
>>
>>   1) Durability - write committed record to serial log.
>>   2) Transaction::state = committed
>>   3) Transaction::syncIsActive.unlock()
>>   4) synchronously move trans from Active to committed lists
>>
>> This order may decrease the waiting time of waiting transaction. 
>>
>> Another thinking is how you decide two transactions have dependency. I only found if two transactions are updating the same table, then one transaction will wait for the other transaction committed. So seems like Transaction::syncIsActive is at table level lock, which seems not very efficient. 
>>
>> Sorry if my idea is idiot, since all my view are from performance view. :-) Your comments are appreciated. 
>>
>> Thx, Xuekun 
>>
>>
>> -----Original Message-----
>> From: Ann.Harrison@stripped [mailto:Ann.Harrison@stripped] On Behalf Of Ann W. Harrison
>> Sent: 2008年12月4日 1:22
>> To: Kevin Lewis
>> Cc: Kevin Lewis; Jim Starkey; FalconDev
>> Subject: Re: Bug 41194
>>
>> Kevin
>>
>>> The lock that prevents action being taken on a record being committed by 
>>> transaction A is Transaction::syncIsActive. 
>>> Transaction::waitForTransaction() gets a shared lock on it if 
>>> Transaction::state == active.  So now we have this order
>>>
>>> 1) synchronously move trans from Active to committed lists
>>> 2) Transaction::state = committed
>>> 3) Durability - write committed record to serial log.
>>> 4) Transaction::syncIsActive.unlock()
>>>
>>> There is admittedly a gap between 2 and 4 where another transaction can 
>>> take action on a record that is "committed" but not yet durable.
>> And I worry that a transaction could start, read the committing
>> transaction's work, and commit in that gap.  Seems unlikely, but
>> really mystic things happen between instructions under load.
>>>  > An alternate solution might be to have the gopher check that
>>>  > the transaction either no longer exists or has a state of
>>>  > committed before it starts to move changes out of the serial
>>>  > log.
>>>
>>> Good idea.  The gopher thread can get a quick shared lock on 
>>> Transaction::syncIsActive before processing it.  Since the gopher is in 
>>> the background the performance cost is acceptable.  And the transaction 
>>> in the serial log with writePending == true has a predictable path 
>>> bewteen Transactin::state == active to committed.  So that wait is 
>>> deterministic.
>>>
>>> I'll try it.
>>>
>>> It is still a good idea to do #3-durability before #4-signal, right?
>>> It was the other way around.
>> Oh yes, absolutely!  Regardless of what state the transaction may
>> claim or what list it's on, the actual commit happens when the
>> serial log commit record hits oxide (or SSD).  If we can keep an
>> over active gopher from getting confused, I'd like to see the
>> order as:
>>
>>   1) Durability - write committed record to serial log.
>>   2) synchronously move trans from Active to committed lists
>>   3) Transaction::state = committed
>>   4) Transaction::syncIsActive.unlock()
>>
>> Nothing is lost if a new transaction sees a transaction that is
>> in the process of committing as uncommitted - if it had started
>> a microsecond sooner, the transaction would have been active.
>> Not seeing concurrent results (except in special cases of unique
>> and foreign key constraints) is not a problem.  Seeing results
>> that are not actually durable is a major violation of transaction
>> semantics.
>>
>> Cheers,
>>
>>
>> Ann
>>
>>