Microsoft SQL Server 2012 Bible

Summary

A transaction is a logical unit of work. Although the default SQL Server transaction isolation level works well for most applications, there are several means to manipulate and control the locks. To develop a serious SQL Server application, your understanding of the ACID database principles, SQL Server's transaction log, and locking contribute to the quality, performance, and reliability of the database.

Major points from this chapter include the following:

• Transactions must be ACID: atomic (all or nothing), consistent (before and after the transaction), isolated (not interfering with another transaction), and durable (once committed always committed).
• SQL Server transactions are durable because of the write-ahead transaction log.
• SQL Server transactions are isolated because of locks or snapshot isolation.
• Using traditional transaction isolation readers block writers, and writers block readers and other writers.
• SQL Server offers four traditional transaction isolation levels: read uncommitted, read committed, repeatable read, and serializable. Read committed, the default transaction isolation level, works well for most OLTP databases.
• Do not use read uncommitted (or the nolock hint) unless you are absolutely sure the side effects will not negatively affect your application data.
• Snapshot isolation involves reading the before image of the transaction instead of waiting for the transaction to commit. Using snapshot isolation, readers don't block writers; writers don't block ...