Hello.  I have a database with a record that has two columns locked.  descrip1 and descrip2.  they are both nvarchar(max) columns.  These are the only two columns of the record that remain locked.  I am certain no user is accessing the record.  I have even moved a backup of the database to my testing computer and the lock still exists.  How do I remove the lock from these two columns on that particular record. 
 
I can edit these two columns on other records.  I have researched "Unlock" on MSDN but it doesn't seem to apply to t-sql.  Any help would be greatly appreciated.
 
Thanks.  Gary.



thanks gary

hi,
it is actually not possible just 2 columns to be locked.. SQL Server manages locks at row, page, or table level and not "vertically".. 
lock management is "automatically" held by SQL Server depending on transaction level.. as soon as a specific row has to be "modified", an exlusive lock is tried against the resource.. SQL Server will try to use row level locking by default, but can escalate to higher type of lock if the lock manager detects it is cheaper to gain a page or table lock instead of multiple row level locks.. if the exclusive lock is granted, the update (or DML operation) is performed, or a timeout is returned in case of unsuccess if the row/page/table is already locked by another task.. again, depending on the isolation level, the lock will be then released as soon as performed or maintained as required.. but, of course, when no one is performing actions against the database, no lock should be issued..
as pointed out, at update time, SQL Server requires an exclusive lock, which is incompatible with all other type of locks, where 2 shared locks (in order to read the very same row from 2 or more different tasks) are compatible... readers block writers and writers block readers, but (usually) readers do not block readers.. here can you find the lock compatibility matrix..
 
you say you can "edit" those columns, and this means they are not locked... how did you detect locks on these columns?
regards



http://www.asql.biz  - DbaMgr2k - DbaMgr and further SQL Tools

Hi Garry, are you talking about encrypted column?  
 



Regards...Emanuel Peixoto. Press Yes if the post is useful.

 
Hello
 
I thank you very much for the help.  Oddly, the two fields were the only fields locked in the record.  I could change any of the other fields.  I have worked hard and finally determined that it was related to my Access Front End.  Once I added a timestamp field to my table, the locks were removed.  
It was my mistake in not including that Access was my front end.  I did not realize that it might be causing the issue.
 
Apparently Access will automatically look at the timestamp field to determine whether anyone has modified the SQL record.  Without the timestamp field Access was getting confused.  This only happened occasionally.  I very much appreciate your answer because it led me to the understanding that SQL would not lock individual columns and therefore it must be something with Access.
 
 



thanks gary