Explain Locks in MySQL

 Explain Locks in MySQL

Locking is an important part of an ACID-compliant database, but excessive locks can lead to performance degradation. Here are some strategies for investigating locks in MySQL.

A major cause of long-running queries that might lead to timeouts or other issues are various types of locks. After finding that you have a large number of threads, or threads that are lasting longer than expected, the next thing to investigate is locks.

locks Types in MySQL:-

MySQL (and most relational databases) have a few different types of locks to limit concurrent access from different sessions, protecting schema and data integrity. In MySQL, specifically:

• Table locks on either base tables or views limit what sessions can read from or write to the table.

• Row locks on individual rows limit what sessions can read or update those rows. If a row has a read or a "shared" lock then no session can modify the row until the lock is released, but any session can read the row. If a row has a write or an "exclusive" lock then only the session holding the lock can modify the row.

Standard SELECT ... FROM statements do not need to obtain row read locks unless the transaction isolation level is set to SERIALIZABLE. That means a row write lock won't prevent row reads from other sessions.

If a statement that modifies rows (e.g. UPDATE or DELETE) has no suitable index, then InnoDB will obtain a write lock on every row in the table.

• Metadata locks on objects (schemas, tables, triggers, etc.) limit what sessions can alter the metadata of the database object.