Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How reduce concurrent locking?
- Thread starter andshr
- Start date
It depends on the scenarios, but a ROWLOCK will only lock that key value, and if there are 10 rows in a page for instance then the system could maintain other ROWLOCK's based on other keys. Whereas, with a PAGELOCK the system would have the page locked that all 10 rows are in and hold it until the lock is released.
The "it depends" means that SQL Server will escalate the locks based on the data and the scenario. If it is holding a ROWLOCK for a transaction and that transaction does more rows, it could escalate the lock type to a PAGELOCK by itself.
I've done a lot of work in medical practices and the problem isn't so much the volume of work causing locking problems as it is the process flow of the work. 2-3 people all trying to get work done for the same patient at the same time. The doctor wants to input notes about the visit, at the same time that the nurse wants to do something for the patient, at the same time that the patient is trying to check out at the front desk.
But the "in general" answer would be "don't provide any hints and let SQL Server manage the locks for you as it usually guesses better based on the data being worked on." We envision a scenario and try to build around that 1 scenario and often make the other situatins worse.
The "it depends" means that SQL Server will escalate the locks based on the data and the scenario. If it is holding a ROWLOCK for a transaction and that transaction does more rows, it could escalate the lock type to a PAGELOCK by itself.
I've done a lot of work in medical practices and the problem isn't so much the volume of work causing locking problems as it is the process flow of the work. 2-3 people all trying to get work done for the same patient at the same time. The doctor wants to input notes about the visit, at the same time that the nurse wants to do something for the patient, at the same time that the patient is trying to check out at the front desk.
But the "in general" answer would be "don't provide any hints and let SQL Server manage the locks for you as it usually guesses better based on the data being worked on." We envision a scenario and try to build around that 1 scenario and often make the other situatins worse.
- Status
Similar threads
