A new data analytics application is being implemented in your organization.
Users will run a process that compares result sets before and after executing complex data modifications. The data will always be queried by using the same conditions.
Some tables updated by the process will not need to be compared.
Your need to design a locking strategy for the process that meets the following requirements:
• Enables other processes or users to modify tables that are not being compared
• Prevent other processes from performing data manipulation language activity on the tables that are being compared
What should the strategy include?
A. Use a transaction that uses the WITH (NOLOCK) hint.
B. Use a transaction that uses the WITH (HOLDLOCK) hint.
C. Set the transaction isolation level to READ UNCOMMITED.
D. Set the transaction isolation level to SERIALIZABLE.
Correct Answer: B
Explanation/Reference:
Let’s say we have 2 tables:
CREATE TABLE ImportantTable(
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Name nvarchar (50) NULL,
GO
CREATE TABLE NotImportantTable(
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Name nvarchar (50) NULL,
GO
and we want to imitate this "data analytics" process. ImportantTable table will be compared. Because "the data will always be queried by using the same conditions", we will use the following statement in the beggining and at the end of the process:
SELECT * FROM ImportantTable WHERE Name =’ABC’;
The second table, NotImportantTable, will not be compared. It will only participate in the the process itself and we don’t want to lock this table until the current transaction completes. We want "enable other processes or users to modify the table".
Let’s start from SERIALIZABLE isolation level:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION
--taking result sets before
SELECT * FROM ImportantTable WHERE Name =’ABC’;
--executing complex data modifications
INSERT INTO ImportantTable (Name) VALUES (‘ABC’)
INSERT INTO NotImportantTable (Name) VALUES (‘ABCDEF’)
--taking result sets after
SELECT * FROM ImportantTable WHERE Name =’ABC’
COMMIT TRANSACTION
GO
In this case NotImportantTable table is not locked longer than insert operation lasts and users can insert data to that table without waiting for the end of the transaction. This is because no SELECT statements were issued against NotImportantTable table. However, if the data modifications look a bit different, then we have a problem:
BEGIN TRANSACTION
--taking result sets before
SELECT * FROM ImportantTable WHERE Name =’ABC’;
--executing complex data modifications
INSERT INTO ImportantTable (Name)
SELECT Name FROM NotImportantTable WHERE Name = ‘ABC’
--taking result sets after
SELECT * FROM ImportantTable WHERE Name =’ABC’
COMMIT TRANSACTION
GO
In this case SELECT was made from NotImportantTable table and, as a result, range locks are placed in the range of key values that match the search conditions of the statement. In other words, if a user try to run the following query:
INSERT INTO NotImportantTable (Name) VALUES (‘ABC’)
after the following satement was issued:
INSERT INTO ImportantTable (Name)
SELECT Name FROM NotImportantTable WHERE Name = ‘ABC’
he/she will have to wait till the end of the transaction, which means that the requirements are not met.
We don’t know what exactly will happen during the process. We only know that there will be "complex data modifications". In this case we should use HOLDLOCK which gives us more freedom and flexibility because HOLDLOCK is equivalent to SERIALIZABLE, but applies only to the table or view for which it is specified. In contrast, SERIALIZABLE isolation level has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
So the following script solves all the problem:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED --Default level
GO
BEGIN TRANSACTION
SELECT * FROM ImportantTable WITH (HOLDLOCK) WHERE Name =’ABC’
INSERT INTO ImportantTable (Name)
SELECT Name FROM NotImportantTable WHERE Name = ‘ABC’
SELECT * FROM ImportantTable WHERE Name =’ABC’
COMMIT TRANSACTION
GO
