How SQL Server 2000 Modifies Data

To guarantee consistency and recoverability of the information, SQL Server considers every data modification as part of a transaction. It will record transaction information, and every modification made as part of every transaction, in a transaction log. In other words, SQL Server first records how the data will be modified, and then it modifies the data. The logical process could be explained as follows:

  1. A data modification is requested.

  2. SQL Server creates a query plan to execute the data modification request.

  3. SQL Server starts a transaction and records it in the transaction log.

  4. SQL Server writes every operation to apply to the data in the transaction log before modifying the data.

  5. After the operations are registered in the transaction log, the affected data is modified in cache, and the pages are marked as dirty. These changes are provisional. They will be permanent only if the transaction completes successfully.

  6. SQL Server applies steps 4 and 5 for every individual operation in the transaction.

  7. If every operation was successful, SQL Server records a transaction commit in the transaction log, and the changes are considered permanent.

  8. If any operation in this transaction failed, SQL Server requests a rollback and will register in the transaction log the operations required to roll back all the changes applied to the modified data pages. The transaction is marked as rolled back in the transaction log.

We will cover transactions in Chapter 13, "Maintaining Data Consistency: Transactions and Locks," but as demonstrated in the preceding points, a transaction is a unit of work that must be completely committed or completely rolled back.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.223.33.157