List:Internals« Previous MessageNext Message »
From:MARK CALLAGHAN Date:February 18 2010 6:19pm
Subject:Re: Finding the start of a statement
View as plain text  
On Wed, Feb 3, 2010 at 3:17 AM, Michael Widenius <monty@stripped> wrote:

> Paul> So how do I know where the actual boundaries of the statements are?
>>>
>>> Why not simply have a counter in your transaction object for how
>>> start_stmt - reset();  When this is 0 then you know stmnt ended.
>>>
>>> In Maria we count number of calls to external_lock() and when the sum
>>> goes to 0 we know the transaction has ended.
>
> MARK> Why does the solution need to be so obscure?
>
> Historic reasons.
>
> MySQL never kept a count of which handlers are used by a transaction,
> only which tables.
>
> So the original logic was that external_lock(lock/unlock) is called
> for each usage of the table, which is normally more than enough
> information for a handler to know when a statement starts/ends.
>
> The one case this didn't work was in the case someone does lock
> tables as then external_lock is not called per statement.
> It was to satisfy this case that we added a call to start_stmt() for
> each table.
>
> It's of course possible to change things so that start_stmt() /
> end_stmt() would be called once per used handler, but this would be
> yet another overhead for the upper level to do which the current
> handlers that tracks call to external_lock() doesn't need.
>

http://bugs.mysql.com/bug.php?id=51285 has an example of a stack trace
where there is a call from
  JOIN::optimize -> ... -> ha_innodb::external_lock -> innobase_commit
-> trx_commit_for_mysql

This is not good. The trx functions lock kernel_mutex which is already
too hot. Why must commit be done during query optimization? This is
the comment from ha_innodb::external_lock()

        /* If the MySQL lock count drops to zero we know that the current SQL
        statement has ended */

        if (trx->n_mysql_tables_in_use == 0) {

                trx->mysql_n_tables_locked = 0;
                prebuilt->used_in_HANDLER = FALSE;

                if (!thd_test_options(thd, OPTION_NOT_AUTOCOMMIT |
OPTION_BEGIN)) {
                        if (trx->active_trans != 0) {
                                innobase_commit(ht, thd, TRUE);


-- 
Mark Callaghan
mdcallag@stripped
Thread
Finding the start of a statementPaul McCullagh27 Jan
  • re: Finding the start of a statementMichael Widenius29 Jan
    • Re: Finding the start of a statementMARK CALLAGHAN29 Jan
      • Re: Finding the start of a statementPaul McCullagh1 Feb
        • Re: Finding the start of a statementKristian Nielsen1 Feb
          • Re: Finding the start of a statementPaul McCullagh1 Feb
          • Re: Finding the start of a statementKristian Nielsen11 Feb
        • Re: Finding the start of a statementSergei Golubchik1 Feb
          • Re: Finding the start of a statementPaul McCullagh2 Feb
      • Re: Finding the start of a statementMichael Widenius3 Feb
        • Re: Finding the start of a statementMARK CALLAGHAN18 Feb
          • Re: Finding the start of a statementMARK CALLAGHAN18 Feb
  • Re: Finding the start of a statementSergei Golubchik1 Feb
    • Re: Finding the start of a statementPaul McCullagh1 Feb