From: Ann W. Harrison
Date: December 4 2008 4:10pm
Subject: Re: Bug 41194
List-Archive: http://lists.mysql.com/falcon/257
Message-Id: <493800E6.9080201@mysql.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=GB2312
Content-Transfer-Encoding: 7BIT

Hi Xuekun,
> 
> 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.

That situation occurs when there is a potential update
conflict between two transactions.  While it is active,
a transaction keeps its syncIsActive locked exclusively.

Suppose that transaction A updates a record.  When
transaction B starts, transaction A is still active (on
the active list).  B attempts to update the same record,
realizes that A was active when B started, so there's a
possible conflict.  B queues a lock on A's syncIsActive
and waits for A to complete and release its lock.  When
B gets a shared lock on A's syncIsActive, it checks A's
final state, and if A has committed, B gets an error on
it's update.  If A rolled back, B's update succeeds.

A similar series of events occur when B attempts to store
a record that has a unique index (primary or unique
constraint) and A has stored a record with the same value.
B waits for A to complete and then gets an error if A
committed or proceeds if A rolled back.
> 
> 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. 

I don't think it breaks transaction semantics, but I worry
about it's effect on internal Falcon logic.

Writing the the serial log has to happen before any other
transaction can see the committing transaction's changes.
That's by far the most expensive step in the commit - there's
a physical write involved - the serial log must be flushed.
Unless the serial log is on a solid state device or RAM disk,
the flush is two orders of magnitude more expensive than
the other three steps combined.

Certainly, you shouldn't unlock syncIsActive before setting
the state to committed; step 2 has to occur before step 3.

I'd prefer to see step 4 before steps 2 and 3, even if it
increases the time that other transactions have to wait.

First, there's the issue of having a transaction in the
active list with its state property set to committed, but
without a valid commitId.

Second, if the waiting transactions are running in
repeatable read mode, they're going to get errors on
their current operations, that require that they
end their current transaction before retrying the
operation.



> 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. 

It's not a table level lock - it's a row (record) level
lock.

"Dependency" is a confusing word here - it had been used
to indicate that a transaction needs to read the state of
records before some other transaction changed them, and
limited the scope of scavenging old record versions.

I think you mean, how does a transaction get in a state
where it is waiting for a lock on some other transaction's
syncIsActive.  As I explained above, that happens when
the two transactions have tried to update the same record
or insert the same value into a unique index.

It's not a table level lock - it's a row (record) level
lock.

Best regards,

Ann