How do you achieve isolation in ACID transactions?

Question

what is a concrete implementation I can use for isolating transactions? For example, for atomicity, I can lump multiple statements underneath a BEGIN TRAN and then call COMMIT somewhere down the line. For consistency, I can set types for columns as well as constraints. For durability, I can set a trigger to archive records onto another table, schedule backup jobs or manually back records up myself. What can I do for isolation? Is locking a table a way to achieve this? Do I need to understand what a database "session" is for me to make sense of isolation?

Answer 1

Every transaction, not only exlicit ones (eg those between BEGIN TRANSACTION and COMMIT/ROLLBACK) are isolated from other sessions/users.

Locks are sets automatically by the storage engine at the finest "surface" they can :

• mode : shared for reads, exclusive for writes
• granularity : row, page, partition, table/index, depending of the amount of data used by the transaction
• type : pessimistic or optimistic
• duration : the statement or the transaction depending of the TRANSACTION ISOLATION LEVEL used

TRANSACTION ISOLATION LEVEL can have 4 levels :

• READ UNCOMMITTED : able to read uncommitted values (dirty read, chaos... - no lock)
• READ COMMITTED (the ordinary level for pessimistc mode) : reading only committed values (lock duration is the statement)
• REPEATBLE READ : the same dataset has always the sames values when reading twice or more during the transaction. Locks on rows are maintened to the end (COMMIT/ROLLBACK)
• SERIALIZABLE : the tables is locked to encure no UPDATE nor INSERTs during the transaction such as new row won't modify the global value of the dataset

The higher the isolation level you choose, the less concurrency there will be.

Any SQL command you execute that modify the structure or read or write the data, even those like CREATE, ALTER, DROP, GRANT, REVOKE, EXECUTE... is an explicit transaction by default...