Mohan Saraswatipura explains how database concurrency works in Postgres:
Concurrency control is an essential aspect of database systems that deals with multiple concurrent transactions. PostgreSQL employs various techniques to ensure concurrent access to the database while maintaining data consistency using atomicity and isolation of ACID (stands for Atomicity, Consistency, Isolation and Durability – https://en.wikipedia.org/wiki/ACID) properties.
The majority of the article focuses on Multi-Version Concurrency Control, which is also the concurrency option which would be least well-known to SQL Server users.