List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:November 5 2009 5:35am
Subject:Re: Help with InnoDB and locked transactions
View as plain text  
Ok, take a deep breath.

Your basic strategy looks sound.. you generally don't want to lock
tables unless you are doing something specific that demands it.

start, modify, commit should cover all your normal success cases just fine.

A few boiler-plate questions
   your id_aircraft, it is a unique index? perhaps a primary key?
   how many rows do you have in that table?
   what is it's table type
   are there any other processes which might also be accessing that data?
   how is the data directory mounted on your system (what os btw?)
   you are using PHP through a wrapper library of some kind?  as you
don't have access to the raw connection resource, I can't help but
wonder if your client interface isn't playing with your hand

the reason you put 'auto-commit=0;' is because many client apis
explicily create connections as auto-commit=1, which makes
transactions impossible.  you only need to do it once at the beginning
of the connection.

to get a view of what is going on while your lock is pending, try
logging into the console as root and type

show full processlist;

this shold show you your locked process as well as any other processes
it might be conflicting with.

 - michael dykman
On Wed, Nov 4, 2009 at 10:35 PM, Daevid Vincent <daevid@stripped> wrote:
> I am new to transactions, but they're causing me a lot of grief right now...
>
> Basically I do this:
>
>        sql_query('agis_core_master', 'SET autocommit=0');
>        sql_query('agis_core_master', 'START TRANSACTION');
>
>        $query_result .= sql_query('agis_core_master', "UPDATE
> registration
> SET registration_name = ? WHERE id_aircraft = ? LIMIT 1",
> array($data['registration_name'], $data['id']));
>
>        $query_result .= sql_query('agis_core_master', "UPDATE
> sdu_tray SET
> sdu_tray_id = ? WHERE id_aircraft = ? LIMIT 1", array($data['sdu_tray_id'],
> $data['id']));
>
>        if ($query_result == '11')
>                sql_query('agis_core_master',
> 'COMMIT');
>        else
>                sql_query('agis_core_master',
> 'ROLLBACK');
>
>        //weird that I don't need to do this, since I had to set
> it manually
> up there!?
>        //sql_query('agis_core_master', 'SET autocommit=1');
>
> But it seems that sometimes these UPDATEs take a long time (longer than they
> should) and so they just accumulate locks in mysql and I can't figure out
> how to clear them all. If I refresh my page, it exacerbates the situation
> with now even more locks.
>
> I'm confused especially because the documentation is not only sparce in
> examples, but also seems to contradict itself.
>
> http://dev.mysql.com/doc/refman/5.0/en/commit.html
>
>        SET autocommit=0;
>        START TRANSACTION;
>        SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
>        UPDATE table2 SET summary=@A WHERE type=1;
>        COMMIT;
>
> "Beginning a transaction causes any pending transaction to be committed. ...
> Beginning a transaction also causes table locks acquired with LOCK TABLES to
> be released, as though you had executed UNLOCK TABLES. "
>
> I don't understand why I have to manually 'SET autocommit=0' and then it's
> even more confusing why I don't have to 'SET autocommit=1' (or do I? -- I've
> tried it both ways and it doesn't seem to help)
>
> Furthermore this page gives a completely different example/usage:
> http://dev.mysql.com/doc/refman/5.0/en/lock-tables-and-transactions.html
>
> "The correct way to use LOCK TABLES and UNLOCK TABLES with transactional
> tables, such as InnoDB tables, is to begin a transaction with SET autocommit
> = 0 (not START TRANSACTION) followed by LOCK TABLES, and to not call UNLOCK
> TABLES until you commit the transaction explicitly. For example, if you need
> to write to table t1 and read from table t2, you can do this:"
>
>        SET autocommit=0;
>        LOCK TABLES t1 WRITE, t2 READ, ...;
>        ... do something with tables t1 and t2 here ...
>        COMMIT;
>        UNLOCK TABLES;
>
> So WTF? How am I supposed to really do this transaction stuff, and HOW do I
> force the clearing of locks or stuck queries or whatever should something
> S.T.B.?
>
> I've logged in as root and typed all sorts of commands to no avail:
>
> SET autocommit=1;
> START TRANSACTION;
> UNLOCK TABLES;
> COMMIT;
> ROLLBACK;
> FLUSH TABLES WITH READ LOCK;
> FLUSH TABLES WITH WRITE LOCK;
>
> But even if there is some magic incantation that root can use to "fix" this,
> it seems pretty fragile that ANY QUERY I can make should cause my RDBMS to
> be hosed like this. Why isn't InnoDB/MySQL more resilient and bounce back,
> clearing locks, etc.
>
> "/etc/init.d/mysql restart" will "fix" the problem, but we have nearly a
> billion rows, so bringing the database down and up is painfully slow as it
> does it's auto-checking and "fixing" of rows and whatever other magic it
> does behind the scenes. Not an option.
>
> Nor do we want/need to be sitting there restarting servers or issuing CLI
> commands to fix the database should it be hosed by some failed transaction.
> Isn't that the point of transactions?
>
> We're running Ubuntu LTS 8.04 and mysql 5.0.51a (with all patches, etc.)
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>



-- 
 - michael dykman
 - mdykman@stripped

"May you live every day of your life."
    Jonathan Swift
Thread
Help with InnoDB and locked transactionsDaevid Vincent5 Nov
  • Re: Help with InnoDB and locked transactionsMichael Dykman5 Nov
  • Re: Help with InnoDB and locked transactionsRaj Shekhar5 Nov