MySQL and Row Level Locking? Or why are you getting the error:
ERROR 1205 (HY000) : Lock wait timeout exceeded; try restarting transaction
You get the error because your allocated time to hold a DML lock in a transaction exceeds the set limit. Usually the default limit to hold a DML row lock, set by innodb_lock_wait_timeout db parameter, is 50 seconds. If your transaction doesn't commit/rollback within 50 seconds you will get this error. We don't want to hold locks for longer than 50 seconds anyway, throughput would be affected.
And yes MySQL in innodb uses row level locking. Since MySQL 5.1+ (time Oracle took over) it does row level locking in its InnoDB tables. That means only the rows which are selected . . . FOR UPDATE . . . are locked and not the whole table. To see the threads (sessions) which are locking other threads and which queries are locking, use the following INFORMATION_SCHEMA dictionary SQL query as DBA. You will be able to see blockers and waiters of transactions waiting on locks. Run it as is using INFORMATION_SCHEMA schema, no modifications.
Use this SQL query to monitor locks and transactions and note that query will return data only when there are locks!
SELECT
r.trx_id AS wating_trx_id,
r.trx_mysql_thread_id AS waiting_thread,
TIMESTAMPDIFF(SECOND,
r.trx_wait_started,
CURRENT_TIMESTAMP) AS wait_time,
r.trx_query AS waiting_query,
l.lock_table AS waiting_table_lock,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_thread,
SUBSTRING(p.host,
1,
INSTR(p.host, ':') - 1) AS blocking_host,
SUBSTRING(p.host,
INSTR(p.host, ':') + 1) AS blocking_port,
IF(p.command = 'Sleep', p.time, 0) AS idle_in_trx,
b.trx_query AS blocking_query
FROM
information_schema.innodb_lock_waits AS w
INNER JOIN
information_schema.innodb_trx AS b ON b.trx_id = w.blocking_trx_id
INNER JOIN
information_schema.innodb_trx AS r ON r.trx_id = w.requesting_trx_id
INNER JOIN
information_schema.innodb_locks AS l ON w.requested_lock_id - l.lock_id
LEFT JOIN
information_schema.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id
ORDER BY wait_time DESC;
ERROR 1205 (HY000) : Lock wait timeout exceeded; try restarting transaction
You get the error because your allocated time to hold a DML lock in a transaction exceeds the set limit. Usually the default limit to hold a DML row lock, set by innodb_lock_wait_timeout db parameter, is 50 seconds. If your transaction doesn't commit/rollback within 50 seconds you will get this error. We don't want to hold locks for longer than 50 seconds anyway, throughput would be affected.
And yes MySQL in innodb uses row level locking. Since MySQL 5.1+ (time Oracle took over) it does row level locking in its InnoDB tables. That means only the rows which are selected . . . FOR UPDATE . . . are locked and not the whole table. To see the threads (sessions) which are locking other threads and which queries are locking, use the following INFORMATION_SCHEMA dictionary SQL query as DBA. You will be able to see blockers and waiters of transactions waiting on locks. Run it as is using INFORMATION_SCHEMA schema, no modifications.
Use this SQL query to monitor locks and transactions and note that query will return data only when there are locks!
SELECT
r.trx_id AS wating_trx_id,
r.trx_mysql_thread_id AS waiting_thread,
TIMESTAMPDIFF(SECOND,
r.trx_wait_started,
CURRENT_TIMESTAMP) AS wait_time,
r.trx_query AS waiting_query,
l.lock_table AS waiting_table_lock,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_thread,
SUBSTRING(p.host,
1,
INSTR(p.host, ':') - 1) AS blocking_host,
SUBSTRING(p.host,
INSTR(p.host, ':') + 1) AS blocking_port,
IF(p.command = 'Sleep', p.time, 0) AS idle_in_trx,
b.trx_query AS blocking_query
FROM
information_schema.innodb_lock_waits AS w
INNER JOIN
information_schema.innodb_trx AS b ON b.trx_id = w.blocking_trx_id
INNER JOIN
information_schema.innodb_trx AS r ON r.trx_id = w.requesting_trx_id
INNER JOIN
information_schema.innodb_locks AS l ON w.requested_lock_id - l.lock_id
LEFT JOIN
information_schema.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id
ORDER BY wait_time DESC;