The story background

Innodb locking mechanism





Table 1

Compare the direction Row locks Table locks
Locking granularity small big
Whether a deadlock occurs will Don’t
Lock conflict probability small big
concurrency more less
overhead small big
  • Read locks (shared locks) : allows read locks on other threads, but does not allow write locks.

    • SELECT * FROM t_cms_promotion t WHERE t.pro_id = ? LOCK IN SHARE MODE

  • Write locks (exclusive locks) : Do not allow any locks on other threads.

    • SELECT * FROM t_cms_promotion t WHERE t.pro_id = ? FOR UPDATE

Note: If the above SQL statement is locked upstream, the query condition pro_id needs to be indexed