How do you diagnose and fix lock contention and hot rows?

Answer preview

Lock contention happens when many transactions fight for the same row or index page. Hot rows — a global counter, a popular inventory SKU, a session token — become a serial bottleneck even though the database supports thousands of concurrent connections.…