It really depends on your use-case. A very brief summary for the case of Postgres: every tuple has a pair of hidden columns `min_xid` and `max_xid`. Every tran

It really depends on your use-case. A very brief summary for the case of Postgres: every tuple has a pair of hidden columns `min_xid` and `max_xid`. Every transaction get assigned an `xid` that's always increasing (it wraps around on int_max, that's a problem for later), and it can only see rows where `xid > min_xid and (max_xid is null or xid <= max_xid)`. On writes the database is checked for rows with a matching PK where min_xid > xid, if any are found an error is raised. The reality is a lot more complex than this and involves some locks during transaction commits and the vacuum process to clean up dead rows and handle xid wrap-around, but that's the basic idea.