SQL Server Concurrency – Limit to Concurrent Table Queries

You should read up on MVCC (Multi Version Concurrency Control), also known as record versioning, record shadowing and even multi generational architecture.

What it means is that when a transaction reads a record, what it actually gets is a copy of the record.

Then another transaction reads the record - gets another copy.

Transaction 1 changes its copy of the record and commits. The real record on disk gets changed.

Transaction 2 changes the record and attempts to commit. The system recognises that a previous transaction has committed a change and rejects the commit attempt.

So, if you have one transaction writing the record, other transactions can read the old record until the first transaction commits. After this commit, all reads will get a copy of the new record.

This is why readers aren't blocked by writers and writers don't block readers. Transactions work with copies of the data until they're ready to commit. Telling the system that a transaction is read only is then obviously a big help to the system - it can take a copy and then not worry about "record-keeping" - in the sense that it doesn't have to keep track of that transaction any more.

The system is based on an optimistic premise - it assumes - not unreasonably in the case of an OLTP system - that the chances of several reads and in particular several changes being made to the same record at once are small. This is not a requirement (desirable but not necessary), but systems which are reading/updating the same records simultaneously will suffer a performance penalty.

From the MVCC page referenced above, IMHO, this is the most important part of that page:

MVCC provides point in time consistent views. Read transactions under MVCC typically use a timestamp or transaction ID to determine what state of the DB to read, and read these versions of the data. Read and write transactions are thus isolated from each other without any need for locking. Writes create a newer version, while concurrent reads access the older version.

or from the PostgreSQL documentation:

data consistency is maintained by using a multiversion model (Multiversion Concurrency Control, MVCC). This means that while querying a database each transaction sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data.

Unless you want to go digging into source code (way above my pay grade), all you need to know is that this system is maintained by lists of pointers to chains of record versions - the system keeps track of which is the latest valid record and the status of other copies that have been read and are about to be modified - and resolves conflicts in the event of transactions trying to overwrite each other in an inconsistent manner.

From the above, it should be easy to see how backups are performed - basically a "checkpoint" or "snapshot" is established - essentially using the same mechanism as for normal transactions - and the database is copied - all old records (which the system will keep because it "knows" that those older records are still "interesting" - i.e. "active", even though they may have been deleted in the meantime) will be copied whereas newer commits will be ignored.

All major RDMSs use MVCC nowadays, with Microsoft SQL Server being the last convert to the paradigm - it became an option in SQL Server 2005. The PostgreSQL (see above) documentation is good, as is the Oracle stuff. If I were to recommend that you read any document, then take a look at this one - it's a Firebird introduction to the concept - Interbase (Firebird's parent) was the second system to use MVCC.