Sahil (who is the author of this book on ADO.NET) and I chatted about transactions in MARS after my DotNetRocks show on Friday. I think his curiousity lay in the potential of multiple transactions within one connection in MARS. Rather than theorize, I thought I would just try it in code and my answer is “can’t be done”. You will get a System.InvalidOperation Exception that says “SqlConnection does not support parallel transactions.”
In theory this makes sense. In his MARS FAQ post, Angel (a tester on the ADO.NET team) explains when he likes to leverage MARS:
Q: So if MARS is not for performance what is it good for?
A: I like to use MARS in two core scenarios, 1) When using MARS results in cleaner looking code and 2) when I am using Transactions and need to execute in the same isolation level scope.
Q: When does using MARS result in cleaner looking code?
A: The quintessential MARS example involves getting a datareader from the server and issuing insert/delete/update statements to the database as you process the reader.
Also, it’s good to note that MARS just is, it’s just there. You don’t really turn it on or off (though you can change the SQLConnection setting – but definitely read the FAQ on that point). It just kicks in when needed. It lets us code in a way that is logical. MARS was one of the most requested features for ADO.NET 2.0. It was really hard to get in there, but people were tired of getting an error message when they did what came naturally, try to use one connection for multiple commands.
But MARS won’t prevent you from writing code that will reduce your performance. In many cases, you will actually have much better performance leveraging connection pooling over forcing multiple queries (or executes) on one connection. So you should definitely consider how you take advantage of this feature.
Posted from BLInk!
Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!