A database transaction is a series of SQL commands.

Database transaction properties: ACID

Atomicity

A transaction must be an atomatic unit of work; either all of SQL commands are performed, or none of them is performed.

Consistency

When completed a transaction, all data in the database must stay in a consistent state.

Isolation

Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions.

Durability

When completed a transaction, the modifications persist in the database permanently.

4 categories of isolation level:

  1. Read uncommitted

Lock releases after modification. It avoids the lost update problem, but may produce Dirty Read, because it can read record that uncommitted and the records may rollback to the original ones.

  1. Read committed

Lock releases after committing transaction. It can only read records that have already committed. It avoid dirty read but it may produce norepeatable read, which means it may return different results for two same query commands, because one query happened before committing and the other happened after committing.

  1. Repeatable read (MySQL default)

It avoids norepeatable read because it returns same query results within the same transaction. However, it may produce phantom read.

  1. Serializable
  2. low efficiency, but high safety

References:
https://technet.microsoft.com/en-us/library/ms190612(v=sql.105).aspx