From: Michael Widenius
Date: January 29 2010 7:29pm
Subject: re: Finding the start of a statement
List-Archive: http://lists.mysql.com/internals/37675
Message-Id: <19299.14116.624846.175076@narttu.askmonty.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


Hi!

>>>>> "Paul" == Paul McCullagh <paul.mccullagh@stripped> writes:

Paul> Hi All,
Paul> I am having a bit of a problem fixing the following bug:

Paul> https://bugs.launchpad.net/pbxt/+bug/513012

Paul> The problem is that it leads to "nested statements".

Paul> In the test, we have a table called tb0_eng2 which contains a few rows:

Paul> create table tb0_eng2 (
Paul>    i1 int NOT NULL auto_increment, primary key (i1),
Paul>    f1 int,
Paul>    f2 char (32)
Paul> );

Paul> insert into tb0_eng2 (f1,f2) values
Paul>    (7,  'init_val'),
Paul>    (14, 'init_val'),
Paul>    (10, 'init_val'),
Paul>    (14, 'init_val'),
Paul>    (4,  'init_val');

Paul> One of the rows is being constantly updated, as follows:

Paul> update tb0_eng2 set f2='Update #$counter' where f1=10;

Paul> A trigger on tb0_eng2, does an INSERT on tb0_logs for every UPDATE on  
Paul> tb0_eng2:

Paul> Create trigger tb0_eng2_upd after update on tb0_eng2
Paul>    for each row
Paul>      insert into tb0_logs (entry_dsc)
Paul>      values (concat('Update row ', old.i1, ' ', old.f2, ' -> ',  
Paul> new.f2));

Paul> So the INSERT statement is "nested" in the UPDATE statement.

Paul> My problem is that I need to detect the start and end of statements.

Paul> As far as I can tell (at least I have always assumed this) in this case:

Paul> ha_handler::start_stmt() is called for each table at the start of a  
Paul> statement, and

Paul> ha_handler::reset() is called for each table at the end of each  
Paul> statement.

Paul> It would be easy to tell the start and end of statements if these  
Paul> functions were only called ONCE for each statement.

Paul> However, if multiple tables are involved in a statement (which is not  
Paul> the case in the example above), then start_stmt() and reset() are  
Paul> called multiple times for each statement.

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.

Regards,
Monty