Want to learn how database locks actually work?
Check out this incredibly thorough review by database legend Goetz Graefe, which dives deep into how databases use locks to protect your data and the integrity of your transactions.
One of the most interesting distinctions in this paper is between locks and latches. Locks provide concurrency control between transactions--they're heavyweight, are meant to be held for a long time, and support complex scheduling and deadlock detection policies. However, as a result, they're expensive to acquire and release, requiring thousands of CPU cycles.
By contrast, latches protect individual data structures from concurrent accesses by different threads/processes. They're lightweight (tens of CPU cycles per acquire/release), are held only while the data structure is being read or updated, and have minimal scheduling or deadlock detection capabilities and thus must be used very carefully. You might grab a latch before physically modifying a B-tree page in memory to ensure no one else concurrently writes to that page.