Member Avatar for indr

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..??

  • 3 Contributors
  • 3 Replies
  • 230 Views
  • 16 Hours Discussion Span
  • Latest Post Latest Post by indr

Dani AI

Generated

A short, practical explanation and next steps.

As pointed out, the second session was blocked because the first session left a transaction open and was holding the lock(s) that the second session needed; when the wait exceeded InnoDB’s lock-wait timeout the server returned ERROR 1205. As noted, ending the transaction (commit or rollback) releases those locks and removes the block. (dev.mysql.com)

Quick diagnostics (run from an admin session):

SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS\G

On servers that expose the InnoDB monitoring tables you can also inspect current transactions and waits (INNODB_TRX, INNODB_LOCK_WAITS or the Performance Schema lock tables) to see which transaction is blocking which. Use those queries to find the blocking thread id so you can investigate the SQL it’s running. (dev.mysql.com)

Why locks can persist beyond a single statement: InnoDB holds row locks for the duration of the transaction. Depending on isolation level (REPEATABLE READ vs READ COMMITTED) gap/next-key locking behavior can also cause seemingly unrelated statements to block inserts/updates; stored-procedure internals, foreign-key checks or duplicate-key checks can widen the scope of locks. If you need fewer gap locks, READ COMMITTED changes the behavior (with its tradeoffs). (dev.mysql.com)

Practical fixes and cautions: keep transactions short, avoid interactive pauses inside a transaction, have the caller manage commits when appropriate, and inspect the stored procedure for long-running statements. As an emergency unblock you can KILL <thread_id> (the server will roll back that transaction, which can be expensive). Raising innodb_lock_wait_timeout only masks the symptom; use it carefully. If you want the whole transaction rolled back on a timeout, the server has an option for that behavior—review the InnoDB parameters. (dev.mysql.com)

Recommended Answers

All 3 Replies

Member Avatar for griswolf

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.

Edited by griswolf because: n/a
Member Avatar for debasisdas

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.

Member Avatar for indr

you mean to say that if a transaction is not committed it has a lock on the table??

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.