BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

Programming Concepts

Relational Database Concepts for SQL Server

This is the first in a series of articles describing the use of Microsoft's SQL Server database management system (DBMS). The tutorial is aimed at software developers using SQL Server for data storage. Part one reviews database concepts and terminology.

Transactions

The last concept to be considered in this article is that of transactions. When updating information in a database, it is often necessary to make multiple updates to obtain the correct end result. In the short period between the individual updates, the information in the database could lose integrity.

As an example, a database may be used to hold bank account information. To transfer money between two bank accounts, one account must be debited and the other credited. If one of these updates failed during a transfer, either the bank or its customer would appear to lose money.

To avoid this type of problem, relational database use transactions. A transaction is started before the first update and committed after the final update. Until the transaction is committed, the individual changes do not appear in the database. If an error occurs during the transaction, all of the changes are lost, ensuring that the database's state is still valid. Transactions may also be manually rolled back, undoing all of the changes made since the transaction began.

Transactions must pass the ACID test. The ACID mnemonic is defined as:

  • Atomicity. The data modifications that occur within a transaction are atomic. If the transaction is successful, all changes are committed to the database. If a transaction fails or is rolled back manually, all of the updates are reversed.
  • Consistency. Each command executed by a transaction must result in valid data. Any attempt to create invalid information, breaking the database's consistency, will result in the transaction being rolled back.
  • Isolation. Databases are generally accessible by multiple concurrent users or processes. When several transactions are executing at the same time, each must be isolated from the others. This means that one transaction should not interfere with another and that transactions that operate on the same data should appear to execute in series.
  • Durability. The durability rule states that once a transaction is committed, all of the individual changes to the database are committed and are not lost, irrespective of any external errors or hardware or software failures.

What Next?

In the remainder of the SQL Server Programming Fundamentals Tutorial, we will create, populate and manipulate databases using Microsoft SQL Server. All of the concepts described above, and more, will be used in this series of articles.

18 May 2008