hi,
i opened two mysql sessions(A,B) and set the autocommit variable to 0 for both the sessions.
by giving "set autocommit=0"
then in session A i added a row to my table by calling a stored procedure and i tried doing the same in session B also..
but in session B it gave the following error message"error1205:lock wait timeout exceeded:try restarting the transaction".
could any body pls explain why i got this error..??
I suspect that you have not committed the first transaction, so it retains a lock, thus preventing the second transaction from beginning until the first transaction's lock is released... which will not happen until you commit it.
http://dev.mysql.com/doc/refman/5.0/en/ :
By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent. To disable autocommit mode, use the following statement:
SET autocommit=0;
After disabling autocommit mode by setting the autocommit variable to zero, changes to transaction-safe tables (such as those for InnoDB, BDB, or NDBCLUSTER) are not made permanent immediately. You must use COMMIT to store your changes to disk or ROLLBACK to ignore the changes.
add commit inside the stored procedure and rerun the same.
else commit manually after each execution before running another session, the problem will be solved.
@griswolf
you mean to say that if a transaction is not committed it has a lock on the table??
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.