Lock Basics
When a transaction is being executed it must be isolated. This means that commands executed on other connections must not be affected by the information that has not yet been committed. This is achieved by placing locks on the affected data.
When a lock is in place, statements on other connections are prevented from modifying the dirty data and, in some cases, from reading the information that is about to change. Locks will be explained in more detail later in a later article in this tutorial. However, it is important to understand the basic effect of locks and their impact on other processes.
To demonstrate the impact of a simple lock, execute the following statements. These start a new transaction and create a new job.
BEGIN TRAN
INSERT INTO Jobs
(JobId, StandardJobId, ContractNumber, VisitDate)
VALUES
('073E2600-2426-4B86-B744-1DA722C73D7C', 3, 1, '2008-03-18')
With the transaction still open, we can simulate another user by creating a second connection. To do so, open a new query window and execute the following statement:
You will see that the SELECT statement does not return any data and does not complete. The statement has been blocked by the locks created by the earlier transaction. Switch back to the original query window and roll back the transaction:
If you now switch back to the SELECT statement's query window, you should find that it has finished executing and returned the Jobs table's data.
The extent of the locks depends upon the amount of data being affected. Often individual rows are locked to minimise the impact on other processes. However, if large amounts of data are being modified, entire pages or tables may be locked to other users. For this reason, you should keep transaction durations short and limit the amount of data modified wherever possible.
8 January 2009