SQL Server Performance Forum – Threads Archive

Column Level Locking in SQL Server

Hi, I have an idea related to column level locking that can be helpful in reducing the blocking of select and update queries. Below is the detail of scenario in which column level locking can be beneficial. Table structure: Table1 (fieldA, fieldB, fieldC, fieldD) DML statements: First transaction:
UPDATE table1 SET fieldA = @val1 WHERE fieldB = @val2 Second transaction: (running simultaneously with First transaction)
UPDATE table1 set fieldC = @val3 WHERE fieldD = @val4 If some of rows required by Second transaction are already updated by First transaction then SQL server will block the Second transaction until First transaction not completes. But actually data updated by both UPDATE statements are not affecting to each other. So this type of blocking can be removed by just placing a column lock (lock on column schema not on all values of column). The detail of process is as below: The UPDATE statement of First transaction will place update lock on fieldA and fieldB Table1 (fieldA (U), fieldB (U), fieldC, fieldD) Other UPDATE (and SELECT also) statements will check whether there is a lock already placed on required fields. If yes then wait until the first transaction not completes but if not locked then execute simultaneously with placing there own lock. In the above scenario the UPDATE statement of Second transaction requires fieldC and fieldD that are not locked. During the execution of second transaction the table schema will be like this: Table1 (fieldA (U), fieldB (U), fieldC (U), fieldD (U)) At some extent it can reduce blocking. Please share your view. Thanks & Regards,
Subhash

– If you consider that a row is a complete unit of data, many times columns will be updated not only because of their own value, but because of the combination of column values or due to predicates of a related table/row. Considered this, row level locking would be the most granular/atomic level for locking.
– How would you isolate the column on wich to place the lock?
– It would also place a huge overhead on your lock manager, to implement column level locking. – Also keep lock escalation in mind. This process is triggered by lock manager pressure.

There is no column level locking machanism in sql server but MS may introduced in future versions…. http://www.awprofessional.com/articles/article.asp?p=26890&rl=1
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Subhash
You have to be clear or post a reply down whether you get any information from the above replies, not simply by editing your own 1st post. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Below is example to explain what i what to say. Table: Employee (ID(PK), Name, DOB) ID Name DOB
———————————————
1 Sidd 10/06/1980
2 Veenu 05/02/1978
3 Aman 04/05/1990 Name and DOB are functionally dependent on ID and not dependent on each other. A query from a transaction T1 is: UPDATE Employee SET Name = ‘Neenu’ WHERE ID = 2 Simultaneously run the following query from transaction T2 UPDATE Employee SET DOB = ’04/02/1978′ WHERE ID = 2 The execution of T1 doesn’t affect the T2 so it should not block to T2. For that purpose there should be a check on UPDATE or SELECT query whether the desired data column (Not row) are already locked or not. that can be done by placing a lock on schema like below.
locking after update statement of T1 would be like this: ID Name(U) DOB
————————————————
1 Sidd 10/06/1980
(U)2 Veenu 05/02/1978
3 Aman 04/05/1990 Now UPDATE statement of T2 will check whether the required column (DOB) is locked or not. In this case this is not locked hence it should be executed. Below is answer of alzdba’s doubts.
* Update column lock will be placed on all computed or related columns.
* The columns that are being updated (SET clause) will be locked.
* As the lock is only on schema and not on all data values. so the overhead is negligible.
* As the overhead would not depend on the number or rows, there is no question of escalation. Thanks
Subhash

]]>