All you need to know about Database transaction concurrency control — locks, Isolation level, MVCC

Explained in 15 mins, all of them.

Begin

When talking about database transactions, I often see developers(including myself) confused with the below locks:

  • S(shared) lock, X(exclusive) lock
  • Pessimistic lock, Optimistic lock
  • table lock, row lock, page lock, record lock, gap lock, next-key lock

And these Isolation levels:

  • Read uncommitted, Read Committed, Repeatable read, Serializable

And these names:

  • Phantom Read, Dirty Read, Dirty Write

Also this concept: MVCC

If you also hate the above names of locks, you are not alone.

Let’s explore them all in this single post.

Be pessimistic or optimistic

So first thing, this is an idea, not an application. for example pessimistic lock, The idea is that before I want to touch this system resource (not have to be database records), it could be a file or network port, I want to assume that everyone else who uses this resource will affect my work. so I want to lock them to prevent others from making changes. and optimistic lock is another way wrong, which means I am okay to share the resource with other processes who need it, as long as not affecting my job, when conflict happens we can resolve it on the spot.

Shared or Exclusive

I was also confused by the word “shared lock”, if it is shared, why lock? so just remember that Shared is for reading, during reading, lock it for writing, meaning that I allow another process to read this resource like I am doing, and let’s prevent whatever process from writing it.

Exclusive lock is easy to understand: I am using it, locking it, and no one should be able to access(read or write) it.

So far good? Let’s move on to the isolation level.

Problems — Concurrency transaction

As backend developers, we are often asked this question (even if we are not DBA): Do you know Dirty Read, Dirty Write, or Phantom Read? how it can happen and how to prevent it?

Website