to whom it may concern,
I need some help regarding transactions using innodb tables.
I am running MySQL 3.23.51-Max as database server and developing a web
front-end using ASP. Many of the tables used are of innodb format since it
is imperative for me to use transactions in most of my scripts.
In 95% of the cases the scripts work fine but at times this error pops up
'Lock Wait Timeout Try Restarting Transaction'
This leaves certain tables locked for a quite a number of hours and the
only possibilty to recover the table is to restart mysql. Now my software
is still at the testing stage and so i'm the only user. But when going live,
15 people will be using it concurrently and restarting my sql each time to
avoid the table lock will not be possible.
How can the deadlock be avoided?
I have set innodb_lock_wait as 60 in my.cnf file, is this enough?
as for the transaction syntax i am using the following
--------------------
Begin;
on error resume next
sql statements
If err then
rollback;
else
commit;
end if
on error goto 0
--------------------
am i doing something wrong? will set autocommit=0; be useful?
or is there any way to recover from the table lock?
i would really appreciate a reply
Best Regards
Erik DeBattista
Systems Developer
Webcraft Ltd.
--CONTACT DETAILS-----------
www.webcraft.com.mt
Email: [EMAIL PROTECTED]
Tel: +356 21421540 Fax: +356 21419300
----------------------------
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
