Active Transactions

• SQL commands do not execute instantly, they stay active for an amount of time that ranges from milliseconds to hours, depending on the command, the data involved and the level of server activity. Transactions may simply read information in the database or they may add new information. Those that add or change data put a lock on affected tables in the database. The lock prevents other transactions from overwriting data; they must wait until the transaction completes. Transactions that stay active longer may hold up pending transactions.
  
  

Transaction Logging

• The database server keeps track of completed transactions in a file called a transaction log. If a bad transaction causes problems, an administrator can back it out using the records in the log file. Well-written SQL commands leave the database in a complete state after a transaction finishes; relevant tables have all the data they need and records in the tables are up-to-date. If the SQL commands are less complete, it makes the use of the transaction log more difficult; in this case, the administrator may resort to fixing the database manually.
  
  

SQL Statements

• A database programmer can create a transaction using a series of SQL statements beginning with BEGIN TRANSACTION and ending with COMMIT TRANSACTION. These statements tell the database server that together they constitute a complete transaction. If the database server encounters errors as it processes the transaction, the server can automatically back it out with a ROLLBACK TRANSACTION statement. The use of these SQL statements makes better use of the transaction log file and helps preserve the database’s consistency.
  
  

Performance Monitoring

• A major part of a database administrator’s job is to keep the database server running efficiently. To do this, she uses tools such as performance monitoring software. The software displays statistics such as the number of active and pending transactions, the number of table locks and the server’s CPU time and memory usage. By examining these statistics, the administrator can spot bottlenecks and fine-tune the server’s software to get the most performance out of it.