Just a quick bit of knowledge:
In the context of a transactional database, regardless of type (MSSQL, Oracle, MySQL, etc.), the following rules must apply to any transaction:
- Atomic: the entire transaction must be committed. If a single part of the transaction fails the entire transaction must fail.
- Consistent: each transaction that is committed to the database must not violate any keys, relations or constraints. In other words, a transaction in not allowed to leave a database in an inconsistent state.
- Isolation: a transaction must be isolated from any other transaction running at that time. Transactions running concurrently must be executed as if they were sequential. If two transactions can/need to run concurrently they will need to operate isolated from the data that the other is manipulating.
- Durable: once a transaction is committed it must become a permanent part of the database. It must be stored on a medium that can be powered off and the data will remain (i.e. Hard Disk, SSD, SAN, etc.). Durable also means that a confirmation of the committed transaction is provided.