Transaction and Lock

Transaction and Lock used to ensure consistency and integrity of the database. Transaction is a logical unit that contains a set of commands. Lock prevents conflict between users so that users can not read or modify data that is being modified by another user. Transactions use the lock to prevent users changing data that is being used by the transaction.

Each SQL Server database has a transaction log that records all transactions and database modifications made by the transaction. Log supports three operations :

• The process of recovery from single transactions
  If an application executes a ROLLBACK command or SQL Server detects an error, the records in the log is used to return to the data before the transaction begins.
• Recovery process all the transactions that have not been completed at the time SQL Server starts
  If SQL Server stops suddenly for some reason, the database may be in a state is experiencing a change that has not been written into the data file. If SQL Server starts, the recovery process will be run on each database.
• Restore the database to its original state after a failure
  Once there is a failure on the hard disk, you can restore the data on the situation before damage occurs.
  

Transaction log, not a table but is a separate file or several files in the database. Cache of the log, is a separate place in the database. Forms of data in the log is not the same as the format of page data. You can create the transaction log into multiple files. You can also set the log file growth size of the log thus avoiding uncontrolled logging.

Transaction log can be regarded as a serial string of log records. Log sequence number (LSN) identifies each log. New log record is written at the end of the log with higher LSN than the previous LSN record.