Reliability and Concurrency

SQL Server 2000 uses the concept of transactions and transaction logging to ensure consistency and the ability to recover information stored in the database. Transaction is the smallest unit that can be an INSERT or UPDATE single operation, or a set of existing operations within the statement BEGIN and COMMIT transaction. If the transaction contains multiple operations, all transactions must complete or cancel all.

SQL Server ensures all work units are consistent. This is done by writing the data before and after the transaction in a transaction log. Logs can be used to restore the database to a consistent state if a rollback application process, or the application will recover the data due to system failure.

SQL Server supports Microsoft Distributed Transaction Coordinator (MS DTC) so that the integrity of the transaction can be applied to distributed operations that affect multiple installations of SQL Server by using two-phase commint way.

Concurrency control on the SQL Server is updated by implementing the concept of dynamic locking. This concept of locking strategy for analyzing each transaction. SQL Server uses several types of locking, for use on a record or a collection of records or a table.

With dynamic locking, SQL Server can choose the type of locking used by the operation involved.