We can set the transaction isolation level on a JDBC connection in order to control the level of visibility of read/write access to the common records between concurrent transactions. One way databases implement this is by placing different types of locks on the records/tables.
But are there any other ways a database implements these transaction levels?
I don't want to go too deep into the theoretical stuff which can be read in a lot of very good books about the design of database management systems.
In databases following the pessimistic approach of aquiring and releasing locks, all four isolation levels are implemented using locks. There is a bunch of optimistic approaches (for example, FOCC - forward optimistic concurrency control as well as BOCC - backward...) which keep track of the data an DML statement has read or written to and if a transaction is about to be commited it checks these read and write sets against all other transactions. If these sets are disjoint, the transaction can be written and commited. If the read set of the transaction collides with the write set of another (committed) transaction, the database system has to roll back the transaction. This can be done without using locks. These optimistic approaches are well studied and can be applied without a great performance penalty if collisions are more or less exceptional and lilltle in their number.
