November 18, 2020 at 1:28 am
under active monitor, I found there was very amount of cumulative wait time(sec) for Lock, now I want to know how many ways to reduce lock or avoid lock while encoding SQL statement, and if there is any way to reduce lock for the database or SQL server setting. many thanks!
November 18, 2020 at 2:28 pm
Locks are a required part of SQL Server. You wouldn't want 2 queries updating a single piece of data at the exact same time. Much better for them to run one after the other so you reduce the chance of corrupt data going in or an unknown value being the final one if both queries were run at the same time.
But the best way to reduce the lock time is to have quick transactions. What I mean is have your transaction change one piece of data (row) at a time and then wait for other things to use that data and then move onto the next row. This is often not realistic though, so the trick is to keep changes small and fast, like don't update or delete 1 million rows in a single transaction.
There are also multiple types of locks. SELECT statements for example will usually take out a shared lock. This means that as long as no other queries are trying to change the data (insert, update, delete), other queries can be run against the same objects. So if you have a shared lock on table ABC and I try to select from it, I will not be blocked. BUT if you are selecting from the table and it takes 30 seconds to get your data and I am wanting to change the data (insert, update, delete), I will need to wait for you to finish your SELECT before I can start changing data.
NOTE - the above assumes you are using the default isolation level.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 18, 2020 at 4:51 pm
Waiting for locks is normal in SQL Server (or any other relational db).
Whether the waits are excessive is a different q.
Since you've provided no details, to reduce bad effects of locks generically:
1) Use as short a transaction as possible. You might have to do multiple mods in a transaction -- that's why explicit trans exist, after all -- but do all "prep" work possible -- lookups, etc. -- needed for the mods before you begin the trans. That is, reduce as much as possible the actual work done within the active trans itself.
2) properly tune the table indexes so that locks are as limited as possible
3) if you have tables that are generally modified together, modify them in the same order in every trans. Say tables A, B and C have related data. In trans that update them, always update A first, then B and then C. Or C first, then A and then B. The specific order doesn't matter as long as it is consistent.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 19, 2020 at 12:46 am
Locks are a required part of SQL Server. You wouldn't want 2 queries updating a single piece of data at the exact same time. Much better for them to run one after the other so you reduce the chance of corrupt data going in or an unknown value being the final one if both queries were run at the same time.
But the best way to reduce the lock time is to have quick transactions. What I mean is have your transaction change one piece of data (row) at a time and then wait for other things to use that data and then move onto the next row. This is often not realistic though, so the trick is to keep changes small and fast, like don't update or delete 1 million rows in a single transaction.
There are also multiple types of locks. SELECT statements for example will usually take out a shared lock. This means that as long as no other queries are trying to change the data (insert, update, delete), other queries can be run against the same objects. So if you have a shared lock on table ABC and I try to select from it, I will not be blocked. BUT if you are selecting from the table and it takes 30 seconds to get your data and I am wanting to change the data (insert, update, delete), I will need to wait for you to finish your SELECT before I can start changing data.
NOTE - the above assumes you are using the default isolation level.
noted thank you!
November 19, 2020 at 12:58 am
Waiting for locks is normal in SQL Server (or any other relational db).
Whether the waits are excessive is a different q.
Since you've provided no details, to reduce bad effects of locks generically:
1) Use as short a transaction as possible. You might have to do multiple mods in a transaction -- that's why explicit trans exist, after all -- but do all "prep" work possible -- lookups, etc. -- needed for the mods before you begin the trans. That is, reduce as much as possible the actual work done within the active trans itself.
2) properly tune the table indexes so that locks are as limited as possible
3) if you have tables that are generally modified together, modify them in the same order in every trans. Say tables A, B and C have related data. In trans that update them, always update A first, then B and then C. Or C first, then A and then B. The specific order doesn't matter as long as it is consistent.
ScottPletcher, Thanks for you kind help!
I know the said ways to reduce or avoid lock, seems that we have the following ways if we don't too much care data problem
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply