February 3, 2020 at 6:55 pm
I've searched on this topic, but haven't quite been able to get the answer I'm looking for. I have a situation where our ERP system database ends up with uncommitted implicit transactions that sit there indefinitely and end up causing excessive tlog growth that eventually fills the disk.
I am working with the vendor to help identify the pieces of their code that might be contributing to this scenario. I believe I understand what implicit transactions are and how they work, but what I'm having trouble getting the answer to is what causes them.
I'm basically looking for a discrete list of ways an implicit transaction can begin (is started, is caused, etc) so we can narrow down what is going on and determine if this is an issue with the way their app calls SQL or if it is something on the SQL side that I need to address.
February 3, 2020 at 7:16 pm
SET option might be one thing to check into as this can affect the issues you are having. Check the following documentation for details on this: SET IMPLICIT_TRANSACTIONS (Transact-SQL)
As the article mentions, make sure to check ANSI_DEFAULTS setting as well.
Sue
February 3, 2020 at 7:42 pm
Okay, so if I'm understanding this correctly, the implicit options option is not checked in my connection properties for the server then by default implicit transactions are off when connections are opened. So, then the only other reason I'd see an implicit transaction when running DBCC OPENTRAN is for some code in the app to execute "SET IMPLICIT_TRANSACTIONS"?
February 3, 2020 at 9:09 pm
Directly setting SET IMPLICIT_TRANSACTIONS can be set in the application code, stored procedures so it's one thing to check.
Sue
February 3, 2020 at 9:14 pm
Unless this is nearly always needed I would leave it as it is and declare / set this within a proc.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
February 3, 2020 at 9:14 pm
Stored procs, good call. I'll check there! And I know this seems obvious, but if I run dbcc opentran or look in sys.dm_tran_active_transactions and see "implicit_transaction" as the name, then something somewhere has executed "SET IMPLICIT_TRANSACTIONS"...there's no other thing that it could be...am I correct?
February 3, 2020 at 9:18 pm
The honest answer is that I am not certain, I would need to look at this closer. Sue may have some insight here.
Basically implicit transactions is the difference (imo) of more structured code. I prefer to utilize BEGIN TRAN COMMIT, etc. In maintaining this habit you will forego some mistakes by having 'looser cofe'. Again that is just me and I can't say that my approach is better than your own.
I find in doing this I do not forget to catch possible errors and end up with fewer breaks on larger projects. It also makes the code easier to read and segment into logical operations (visually).
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
February 3, 2020 at 9:43 pm
Stored procs, good call. I'll check there! And I know this seems obvious, but if I run dbcc opentran or look in sys.dm_tran_active_transactions and see "implicit_transaction" as the name, then something somewhere has executed "SET IMPLICIT_TRANSACTIONS"...there's no other thing that it could be...am I correct?
It's a possibility. The setting can also be set with the connection and some do different settings by default depending on what driver/provider the application is using. What you seem to want is a definitive list of exactly when implicit transactions would show and I don't have one. There are too many weird things that can happen in our world but my first suspicion would be that setting.
Sue
February 3, 2020 at 9:50 pm
The honest answer is that I am not certain, I would need to look at this closer. Sue may have some insight here.
Basically implicit transactions is the difference (imo) of more structured code. I prefer to utilize BEGIN TRAN COMMIT, etc. In maintaining this habit you will forego some mistakes by having 'looser cofe'. Again that is just me and I can't say that my approach is better than your own.
I find in doing this I do not forget to catch possible errors and end up with fewer breaks on larger projects. It also makes the code easier to read and segment into logical operations (visually).
Hey Jeffrey -
It's not about whether to use it or not its about trying to track down issues with uncommitted transactions and implicit transactions. With the problems Greg is experiencing, I doubt he'd mess with turning on the setting after the issues he's seen. It's can end up being quite an evil thing if everything is not managed correctly, which unfortunately happens (too often). Tracking it down isn't always too fun either.
Nice to see you back up here again -
Sue
February 3, 2020 at 10:08 pm
Greg Goss wrote:Stored procs, good call. I'll check there! And I know this seems obvious, but if I run dbcc opentran or look in sys.dm_tran_active_transactions and see "implicit_transaction" as the name, then something somewhere has executed "SET IMPLICIT_TRANSACTIONS"...there's no other thing that it could be...am I correct?
It's a possibility. The setting can also be set with the connection and some do different settings by default depending on what driver/provider the application is using. What you seem to want is a definitive list of exactly when implicit transactions would show and I don't have one. There are too many weird things that can happen in our world but my first suspicion would be that setting.
Sue
Another thing I should have added is if they tell you they turn it on in some area, procedure, whatever and then turn it off (I've seen that one before), if they don't have robust error handling in where they turn it back off and get an error, it's likely the culprit and it stayed on. Once that's on, so many things become implicit transactions - a select, insert, update, grant, etc. It's ugly.
Sue
February 4, 2020 at 2:04 pm
Sue,
You are correct in that I wanted a definitive list, but I'm perfectly fine knowing that it can be a little fluid. Based on what you are saying and what I'm observing, I think you're putting me on the right path. On any given day, I can randomly look in sys.dm_tran_active_transactions, I might see 10-15 transactions, of which 3 to 4 are implicit. Most of them don't spend a lot of time in there (as expected). So that being the case, it seems like the ERP system code is setting that option at some point and then having issues in a rare cases. I'll gather some examples for the vendor and take your advice to start looking for places where that option might be set and then not un-set or look for some patterns that point to a need for more robust error handling.
Thank you so much for your time!
February 4, 2020 at 4:25 pm
Thanks for the feedback and please post back if you get anywhere with this. The similar issue I tracked down before took quite awhile to catch as it was happening in the hitting the error scenario. We caught it by tracing user errors and working from there. You can use extended events to capture the same if you wanted to try that using error_reported. Make sure to filter out the changed language setting and changed database context that come through with capturing that event. Play with it in some non-prod environment and you'll see what I mean about those two.
Sue
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply