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?
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 :
TRANSACTION ISOLATION LEVEL can have 4 levels :
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...