There various type of locks in MySQL database implementation. It's critical to understand them and know how each of them works if you need to tune the database performance or make right choices for example which storage engine to use?

Shared locks

Also called read locks, because the read operations often use this type of lock. Articles often use letter "S" to refer to shared locks. When a thread acquire shared lock on a table, other threads can read it but can't write it.

Each read operation should acquire a shared lock and they can work concurrently, but will prevent write operations.

Exclusive locks

Also called write locks, when a write operation is issued, an exclusive is used. The short name is "X".

An exclusive lock will prevent others read or write to the table, that is, before the lock is freed, only one thread can access the table, all the subsequent threads must wait.

For storage engine like MyISAM which uses table level locking, the write is sequential, thus it should be used to save read heavy data, for example the blog post content.

In most database, when you read you need to acquire a shared lock, when you changes you need an exclusive lock. Both shared locks and exclusive locks are regular table locks.

Intention locks

Intention locks are indications, they indicate there are regular locks somewhere. For example each time a thread want to acquire a shared lock(S) on a row in a table, it must first acquire an intent shared lock(IS) on the table.

Intent locking reduces the processing needed to manage the locks, while allowing a high degree of compatibility among concurrent locks. So intention locks are used to manage other regular locks and make them compatible with each other.

Intention locks are introduced to make it easier to detect the conflict between table lock and row level lock.

For example, a thread acquire an exclusive lock on the whole table, then any row in that table should not be read or written by others, whether they are shared or exclusive locks. Without intention locks, system need to check the compatibility between row level locking and table level locking separately. With intention locks, only need to check the compatibility between intention locks.

The rule of intention locks are:

Before a transaction can acquire an S lock on a row in table t, it must first acquire an IS or stronger lock on table t. Before a transaction can acquire an X lock on a row, it must first acquire an IX lock on table t.

If thread A holds an exclusive lock on a table, then no locks on row in the table can be acquired, with intention lock, system only need to check the compatibility between table lock and intention lock, not between row level lock and table lock. This speed up the conflict checking.

Intention locks don't conflict with each other but a table lock block both IS and IX and vice-versa.

Without intention locks, something may get complicated. For example, in a transaction, several row update operations needs to be done, since row level lock should not block other row level lock, the table should not be locked. In the middle of this transaction, another transaction may need to lock the table(schema change for example), because no one has locked the table, it can acquire the table lock. This conflict with the previous transaction. In other words, there are no easy way to detect conflict between table locks and row level locks. See InnoDB Locking Explained with Stick Figures - SlideShare But it easy to detect conflict between intention locks and table locks.