September 10, 2024 at 11:16 am
Hi,
We have 4 stps. the flow of the stored procedure (stp) is as below
stp A is main stp that is called by the application. Inside A, stp B C and D are called within Begin Tran. Also in each stp B C D explicit transaction is included.
Below is the flow of stp A
Begin tran of stp A
Stp B - Insert record in table 1 --> fetch cross DB data --> insert records in table 2 --> insert records in table 3 --> update table 1
Stp C - Insert record in table 1 --> insert or update records in table 4 from table 2 --> update table 1
Stp D - Insert record in table 1 --> insert or update records in table 5 from table 3 --> update table 1
Commit Tran of Stp A
Initially we got deadlock error on table 1. hence to take care, we introduced tablock on table 1, table 2 and table 3.
I want to know whether it is properly done and what are the best practices.
Regards,
Saumik
September 10, 2024 at 7:54 pm
My opinion - query hints are rarely best practice and often lead to other issues.
If I understand things right, you are taking out an exclusive lock on table 1 when your query begins and only releasing it when stp A commits. If this is right, then nobody can use the table until your query completes. If it takes 1 minute to run stp A, that means there is 1 whole minute where nobody can touch table 1 (for example).
Just to confirm, do you NEED the explicit transaction? There are cases where you do obviously, but just wondering if in your scenario that is required? One example of why you'd want the explicit transaction is that if stp B, C, or D failed, you want to roll the entire thing back. BUT if you are not doing any error handling, just COMMIT on success and on error do nothing, I'd question if the explicit transactions are needed. I really hope you have some rollback on error happening, otherwise if there are any errors, you are going to be stuck with open transactions AND a stuck table lock!
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.
September 11, 2024 at 7:19 am
Yes there is interdependency between the procedures and yes error handling and roll back is done. In each procedure explicit transaction is done with its error handling. Table 1 is in stp B , C and D.
my concern here is will taking tablock further introduce any deadlock as multiple instance of application is calling all these stp simultaneously. so if 1st instance is calling, stp A where all tablock of the tables are done then stp B C D are called and simultaneously another 2nd instance is calling stp A, the 2nd instance will have to wait until 1st instance release the tablock. similarly for 3rd instance calling and so on and so forth.
Which dmv will help me to do all the analysis.
Hope i have cleared my concern.
September 11, 2024 at 7:59 am
To resolve deadlocks you should really start by looking at the deadlock graphs; I am unsure why you think a tablock is a good idea. There are plenty of resources. eg Capturing Deadlocks in SQL Server - Brent Ozar Unlimited®
September 11, 2024 at 11:08 am
we captured the cause of deadlock and hence introduced tablock. any other option kindly revert.
September 11, 2024 at 2:40 pm
My opinion (like I stated above and like Ken implied) is that query hints are RARELY needed. There are use cases for them, and I do use "OPTIMIZE FOR" more often than I care to admit, but telling SQL to take out a specific lock I never do.
The best way to reduce deadlocks is to reduce the duration of exclusive locks. Adding query hints may be your fastest to implement solution, but I suspect there is a better way to handle what you are trying to do. There is always more than one way to do things in SQL.
Is there any way you can reduce the number of transactions? What I mean is do you NEED a transaction on SP A? If so, do you NEED the transaction in A to cover SP B, C, and D? I recommend keeping transactions short and specific. If it is not 100% required, then I don't keep it in there.
I would say that re-writing the query to reduce lock time will result in a better solution than adding query hints that are going to block the other queries running against that system. That being said, sometimes a deadlock is recommended action. If the deadlocks are not that common (for example, 1 deadlock for every 10,000 runs of the stored procedure or deadlocks happen less than once per quarter), it MAY be better to leave them as is since it is less blocking AND you can just add retry logic into the application. On deadlock, increment the "retry" counter by 1 and attempt to run the SP again. Have a limit on the number of times it can fail (3 for example) before presenting the error to the end user. If deadlocks are happening multiple times per day, then I'd look at how to rewrite the logic to reduce locking which will reduce the chance of deadlocks.
On top of that, I don't see from your logic why you'd take a table lock out on table 3 when you are just SELECTING from table 3? A shared lock should be sufficient for that and a table lock is likely going to be overkill.
But, my opinion, the best way to reduce the chance of a deadlock is to reduce the time you lock an object.
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.
September 11, 2024 at 4:04 pm
Begin tran of stp A
Stp B - Insert record in table 1 --> fetch cross DB data --> insert records in table 2 --> insert records in table 3 --> update table 1
Stp C - Insert record in table 1 --> insert or update records in table 4 from table 2 --> update table 1
Stp D - Insert record in table 1 --> insert or update records in table 5 from table 3 --> update table 1
Commit Tran of Stp A
By "fetch cross DB data" does it mean using a linked server connection? Also, why must the process repeatedly UPDATE table 1? Instead of updating 1 row over and over could you not just write out the information in multiple rows and then use a query to summarize to the same result? Inside a TRANSACTION it's best to be as minimal as possible
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 12, 2024 at 6:20 am
transactions should be as short as possible, performing lookup work mid transaction will always cause concurrency issues.
What makes you think locking the whole table is better when a row or page lock would have been taken.
Separate out your code tasks is best advice here
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 18, 2024 at 10:20 am
Are you saying that Stp B, Stp C and Stp D also contain BEGIN TRAN...COMMIT/ROLLBACK pairs and the whole lot is wrapped in a BEGIN TRAN...COMMIT/ROLLBACK in Stp A? If so, the transaction model may not be doing what you expect; see the link below for a detailed explanation.
https://www.brentozar.com/archive/2023/02/can-you-nest-transactions-in-sql-server/
September 23, 2024 at 4:20 am
Just to get a clear idea, is this a SQL task executed after hours? If you run the process during the normal day hours, yes, you could see deadlocks challenging online users. In any case, I recommend you simplify your process design to minimize dead locks. That is your database inserts or updates or deletes should be performed in proper order.
If you have a heavy processing system, you could see many deadlocks failing the victim processes.
You could have a staging table to records all the updates/deletes/inserts for nightly updates to the main table.
There are other ways, of course.
DBASupport
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply