*Here is some of my thoughts. \
I have limited knowledge on this. I could be wrong on some statements.*
### in_short
- **Lock**: the normal-lock-`UU` you know, when you concurrently access **data** `OO` in a database -- the lock `UU` locks another thread out.
- **Latch**: this is the inside-lock-`VV` when the Database need to concurrently access a **Page** (ie: a Node in B-Tree) -- the lock (latch) `VV` locks another thread out.
### in_detail
#### Lock
> **Lock**: the normal-lock-`UU` you know, when you concurrently access **data** `OO` in a database -- the lock `UU` locks another thread out.
- eg: \
one transaction `TA` from a thread `T1` is modifying a Data `OO`, \
the other transaction `TB` from a thread `T2` is trying to modify on Data `OO` too. \
`TA` acquires the lock `UU` before its modification, \
`TB` now **is locked out by lock `UU`** & should wait for `TA` finishes its modification on `OO` & then start to modify `OO`.
#### Latch
> **Latch**: this is the inside-lock-`VV` when the Database need to concurrently access a **Page** (ie: a Node in B-Tree) -- the lock (latch) `VV` locks another thread out.
- know_prerequisite: \
a Database is normally implemented in **B-Tree**, \
& each **Node** of a B-Tree normally corresponds to a Page on the Disk
- eg: \
one transaction `TC` from a thread `T3` is modifying a Data `OO` (just **one** transaction -- or not even transaction relevant) \
->> \
**Internally**, the database does a lot of things: \
the Branch_Node inside the B-Tree may need to be Splatted / Merge; \
_ the Leaf_Node inside the B-Tree _ 's Data may need to be modified; \
so, the Database internally may **use Multiple threads `TD1`, `TD2`**, to change the **Nodes** on the B-Tree. \
--> \
When a Node `PP` (correspond to a Page on Disk) is accessed **concurrently** -- \
`TD1` acquires the lock (latch) `VV` before its modification, \
`TD2` now **is locked out by lock (latch) `VV`** & should wait for `TD1` finishes its modification on Page `PP` & then start to modify `PP`.
#### Lock & Latch
- now think of a case where the above 2 cases are ***combined*** \
-- you can encounter both Lock & Latch in a concurrent modification (you used 2 threads) to a data OO \
*(but just saying, in reality, you dont really care about the internal of a Latch, its transparent to Database Users)*
#### Transparency for Database User
- You **need** to know **how the Lock works** (eg: so you can setup the isolation level) \
But you **dont** need to know how the **Latch** works. \
The Latch is **internal** of the Database, its only used by the Database (Database Designers)
- You **dont** need to know the internal of how the Data is stored. \
eg, not the following:
- Database is implemented in B-Tree.
- the Data `OO` is internally stored on a Leaf_Node.
- the Data `OO` might be in the midway inside buffer / page cache / on disk / whatever ...
#### Reference
> - ... two threads from the same transaction ...
>
> - Any **page** has to be **latched** to allow safe **concurrent** access to it.
>
> -- *Database Internals: A Deep Dive into How Distributed Data Systems Work - Chapter05 - Locks Latches*