ACID Properties ?

ACID Properties in SQL Server ensures Data Integrity during a transaction. The SQL ACID is an acronym for Atomicity, Consistency, Isolation, Durability.

Consistencyà If the transaction completed successfully, then it will apply all the changes to the database.

If there is an error in a transaction, then all the changes that already made will be rolled back automatically. It means the database will restore to its state that it had before the transaction started.

Isolationà Every transaction is individual, and One transaction can’t access the result of other transactions until the transaction completed. Or, you can’t perform the same operation using multiple transactions at the same time

Durabilityà Once the transaction completed, then the changes it has made to the database will be permanent. Even if there is a system failure, or any abnormal changes also, this SQL acid property will safeguard the committed data

Dirty Reads –

When a transaction is allowed to read a row that has been modified by an another transaction which is not committed yet that time Dirty Reads occurred. It is mainly occurred because of multiple transaction at a time which is not committed.

These levels are different ways of isolating/separating execution of different transactions from each other.

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

Syntax:

SET TRANSACTION ISOLATION LEVEL     { READ UNCOMMITTED  Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent

 other transactions from modifying data read by the current transaction.

 READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction

 from reading rows that have been modified but not committed by other transactions.

 When this option is set, it is possible to read uncommitted modifications, which are called dirty reads.

    | READ COMMITTED (DEFAULT) This isolation level guarantees that dirty reads do not occur in your transaction

    | REPEATABLE READ  

       SQL Server not only guarantees that dirty reads do not happen in your transaction, 

       but it also guarantees that if you issue  two DML statements  against the same table with the same WHERE clause (one query could be a select and

       the next could be an update), both queries will return the same results.

   | SNAPSHOT         

       SQL Server 2005 introduces the snapshot isolation level in addition to the four isolation levels defined by the SQL-99 standard.                                               it protects against all of the previously mentioned concurrency problems, just as the serializable isolation level does

   | SERIALIZABLE     The Serializable isolation level provides the strictest transaction isolation. This level emulates serial transaction 

                  execution for all committed transactions; as if transactions had been executed one after another, serially,

                  rather than concurrently

  This isolation level guarantees that if you issue two DML statements against the same table with the same WHERE clause, 

      both queries will return exactly the same results, including the same number of rows. To protect the transaction from inserts, 

      SQL Server will need to lock a range of an index over a column that is included in the WHERE clause with shared lock

    }

    STATEMENT ISOLATION LEVEL

    {READ COMMITTED SNAPSHOT

    } [ ; ]

Types of Concurrency Problems

There are several concurrency problems that can occur in a database management system when multiple users access the same data. The following is a short explanation of each concurrency problem.

Lost Update

A lost update can be interpreted in one of two ways. In the first scenario, a lost update is considered to have taken place when data that has been updated by one transaction is overwritten by another transaction, before the first transaction is either committed or rolled back. This type of lost update cannot occur in SQL Server 2005 because it is not allowed under any transaction isolation level.

The other interpretation of a lost update is when one transaction (Transaction #1) reads data into its local memory, and then another transaction (Transaction #2) changes this data and commits its change. After this, Transaction #1 updates the same data based on what it read into memory before Transaction #2 was executed. In this case, the update performed by Transaction #2 can be considered a lost update.

Dirty Read

If data that has been changed by an open transaction is accessed by another transaction, a dirty read has taken place. A dirty read can cause problems because

 it means that a data manipulation language (DML) statement accessed data that logically does not exist yet or will never exist (if the open transaction is rolled

 back). All isolation levels except for read uncommitted protect against dirty reads.

Non-Repeatable Read

If a specific set of data is accessed more than once in the same transaction (such as when two different queries against the same table use the same WHERE clause) and the rows accessed between these accesses are updated or deleted by another transaction, a non-repeatable read has taken place. That is, if two queries against the same table with the same WHERE clause are executed in the same transaction, they return different results. The repeatable read, serializable, and snapshot isolation levels protect a transaction from non-repeatable reads.

Phantom Reads

Phantom reads are a variation of non-repeatable reads. A phantom read is when two queries in the same transaction, against the same table, use the same WHERE clause, and the query executed last returns more rows than the first query. Only the serializable and snapshot isolation levels protect a transaction from phantom reads.

Using Locks to Solve Concurrency Problems

SQL Server uses locks stored in memory as a way to solve concurrency problems. There are several types of locks that are used. You can find more information about the available lock types in the article “Lock Compatibility” in SQL Server 2005.

• • Shared or S-locks Shared locks are sometimes referred to as read locks. There can be several shared locks on any resource (such as a row or a page) at any one time. Shared locks are compatible with other shared locks.

  • Exclusive or X-locks Exclusive locks are also referred to as write locks. Only one exclusive lock can exist on a resource at any time. Exclusive locks are not compatible with other locks, including shared locks.

  • Update or U-locks Update locks can be viewed as a combination of shared and exclusive locks. An update lock is used to lock rows when they are selected for update, before they are actually updated. Update locks are compatible with shared locks, but not with other update locks. Lesson 2, “Designing Transactions and Optimizing Locking,” discusses update locks further.

All isolation levels always issue exclusive locks for write operations and hold the locks for the entire duration of the transaction. In the next sections, you will look at how shared locks are handled by the different isolation levels. To see which locks are currently being held, you can query the sys.dm_tran_locks dynamic management view or execute the sys.sp_lock system stored procedure.

RAID (redundant array of independent disks) levels 0, 1, and 5 are typically implemented with SQL Server.

MS recommends a RAID 1 for Log files, and a RAID 5 for database files.

The RAID configuration that is best for your database files depends on several factors, including performance and recoverability needs. RAID 10  is the recommended RAID system for transaction log, data, and index files. If you have budget restrictions, keep transaction log files in a  RAID 10 system, and store data and index files in a RAID 5 system. (Microsoft SQL Server Implementation and Maintenance)

Here is a quick rundown of some of the more popular RAID options:

Always place log files on RAID 1+0 (or RAID 1) disks.

Consider configuration of TEMPDB database

• • Make sure to move TEMPDB to adequate storage and pre-size after installing SQL Server.

  • Performance may benefit if TEMPDB is placed on RAID 1+0 (dependent on TEMPDB usage).

  • For the TEMPDB database, create 1 data file per CPU