Re: Show innodb engine status tuning db server

[Rick James]

> But why did my ibdata1 increase even though I am using innodb per-tablespace.

ibdata1 is used for a variety of things. You did something (transaction, undo, lock info, temp, etc) that need more space than previously.

> can it still cause the locking issue

Here is a very simple deadlock. Two threads (T1 and T2) are running 'simultaneously', and performing these actions in this order:

T1: BEGIN
T2: BEGIN
T1: UPDATE foo ... WHERE id = 123;
T2: UPDATE foo ... WHERE id = 987;
T1: UPDATE foo ... WHERE id = 987; -- This stalls, waiting for T2 to release its lock
T2: UPDATE foo ... WHERE id = 123; -- Deadlock! InnoDB realizes that T1 and T2 are now waiting on each other. Result: One of the transactions is ROLLBACKed; then the other continues.

The are two fixes for a deadlock:
(1) Change the logic so they won't happen.
(2) Catch them (check for errors), and restart any transaction that is aborted due to a deadlock.

A different 'lock' case is where InnoDB will give up after waiting 50 seconds to get a lock. It will then abort (ROLLBACK) the transaction. Adding suitable indexes helps avoid this by helping queries to finish faster.