> >
> > Hello.
> >
> > We have a web application for our intranet, which uses MySQL for db
> > management. The application has a way of logging query errors that are
> > generated in the system.
> >
> > We get a mysql error recorded as - "Deadlock found when trying to get
> > lock;
> > try restarting transaction" when one of the files tries to create a
> > temporary table. The SQL query for it is as below -
> > CREATE TEMPORARY TABLE TBL_B AS SELECT TA.FLD_MSG_ID FROM TBL_A TA WHERE
> > {... conditions for selection ...}
> >
> > I looked through the MySQL lists, but I could not get info as to why a
> > deadlock is created in this scenario. I also do not know how to
replicate
> > the scenario, as this error is not logged always. Is there a particular
> > case
> > when this happens?
> >
> > I looked through
> > http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html and it says
> > "SELECT ... FROM is a consistent read, reading a snapshot of the
database
> > and setting no locks ..." So why is a lock being taken in the first
> > place?
> >
> > I am at a loss. Can someone shed light on this, or point me to some
> > resource?
>
> in 4.1.xx, you can use the my.cnf option innodb_locks_unsafe_for_binlog to
> make InnoDB to use a consistent read in the SELECT tables in CREATE ...
> SELECT. Read the caveats about the my.cnf option, though.
Thanks. However, whatever is written in
http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html on
innodb_locks_unsafe_for_binlog was enough to scare me to not use it in my
my.cnf :o)
Also I believe this variable is available as of MySQL 4.1.4, whereas ours in
4.1.11. Upgradation is not a viable option for us right now.
>
> This is explained at:
> http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html
>
> I noticed that this behavior is broken in 5.0. I filed the bug report
> http://bugs.mysql.com/bug.php?id=18350 about this. Thank you for bringing
> this up.
No problemos.. :o)
>
> > Environment -
> > OS : RHEL 3
> > DB : MySQL 4.1.11 using INNoDB.
> >
> > Regards,
> > Rithish.
>
> Best regards,
>
> Heikki
>
> Oracle Corp./Innobase Oy
> InnoDB - transactions, row level locking, and foreign keys for MySQL
>
> InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM
> tables
> http://www.innodb.com/order.php
Regards,
Rithish.
