sql server locking mechanism
-
Dear All, I want to have a survey on locking mechanisms in SQL Server. I have studied different locking modes including shared,exclusive, intent shared ,... Besides I have studied different locking modes, table hints and different Isolation levels in SQL server. But I still don't know what should I do if I want to take for example an exclusive lock at a row-level. There are few examples on SQL Server help. Would you please introduce me a reference with examples of these kinds. Thanks in advance
-
Dear All, I want to have a survey on locking mechanisms in SQL Server. I have studied different locking modes including shared,exclusive, intent shared ,... Besides I have studied different locking modes, table hints and different Isolation levels in SQL server. But I still don't know what should I do if I want to take for example an exclusive lock at a row-level. There are few examples on SQL Server help. Would you please introduce me a reference with examples of these kinds. Thanks in advance
MozhdehQeraati wrote:
if I want to take for example an exclusive lock at a row-level.
You do not normally need to do this sort of thing. If you do your insert/update/delete in a consistent manner and use transactions where necessary you can leave the rest to SQL Server.
Bob Ashfield Consultants Ltd
-
MozhdehQeraati wrote:
if I want to take for example an exclusive lock at a row-level.
You do not normally need to do this sort of thing. If you do your insert/update/delete in a consistent manner and use transactions where necessary you can leave the rest to SQL Server.
Bob Ashfield Consultants Ltd
I want to control it myself. I have many clients that do read and write in on a DB server by long time transactions. But I don't know what to do.
-
I want to control it myself. I have many clients that do read and write in on a DB server by long time transactions. But I don't know what to do.
I would firstly look at why you have long transactions - there is probably a design flaw somewhere. SQL Server is designed to work with many clients performing simultaneous updates without the need for the developer to attempt to control locking under anything but very exceptional circumstances. I have been asked about this in the past and it has usually been because the developer either didn't really understand how SQL Server locking works, or they felt they could do a better job than the guys who wrote the code at Microsoft. If you really want to do it yourself then may I wish you luck, and your end users a lot of patience :)
Bob Ashfield Consultants Ltd