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…