Global Transacted Connection Question

[Ray Booysen]

Hi All

In our project, I have a globally transacted connection. Is this a
"proper" way of doing things. All movement through this connection is
transacted and the connection is opened and closed as required.

Can anyone think of any pitfalls, pros, cons of this approach?

Regards
Ray Booysen

 

[Marina]

The only issue is making sure that the transaction is properly handled, and
committed or rolled back when necessary, etc.

As long as you are doing that, you should be fine.

 

[Ray Booysen]

Hi Marina

Thanks so much for your help. Much appreciated.

Regards

 

[Sahil Malik [MVP]]

Ray,

I don't like this idea (sorry just being honest).

You have no good reason to hold a global instance of a connection. You
should let connection pooling do that job for you.

Regards transactions - in that situation you will need to maintain an open
connection instance for the life time of the transaction. However, that
doesn't have to be a "global" instance.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Ray Booysen]

Hi Sahil

Maybe I should explain myself better.

The connection is not held open at all. All that is available is a
class that holds a connection. I can open and close this connection and
the transactions are handled seamlessly. I am not in need of
check-points, so this makes it easier.

The clients just refer to that global connection instance. For the
lifetime of the application (forms-based) the connection is instanciated
by only open when the business layer asks for data. Once the data has
been requested, the connection is closed.

Regards
Ray

 

[Ray Booysen]

The only downside to this that I have found is that I cannot have
concurrent data access. I.e. if a datareader is open, another datareader
cannot access the database as the connection state is "Open, fetching".

 

[Sahil Malik [MVP]]

Right Ray, but still SqlConnection in itself is not a very heavy duty object
unless it is held open unecessarily long. Now with connection pooling
enabled, and you closing the connections ASAP, there is *very* little
downside (slightly more memory usage). But look at the upside - you never
have to worry about that one contentious resource - the global connection
object. This means,

a) You will scale better as more concurrent requests will simply open more
connections as needed.
b) You won't shoot yourself in the foot in a multi threaded environment and
won't have to implement locks all over the place.
c) You wont' be faced with two peices of your code trying to run a command
at the same time and getting connection busy errors.
d) You won't *have to* use MARS in future. I mean, nothing wrong with using
it, but my personal view is, you need to be careful and wise when trying to
use MARS. Lots of things to watch out for.

A better architecture, IMO would be centered around Connection Pooling.

--

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Marina]

The idea is let's say you have a transaction. Your code is calling various
methods all over the place.

You can either: pass the connection and transacton around to every single
method that needs them, or you can have it in one central location where all
the methods can access it when needed and participate in the transaction.

This is not at all related to how long the connection stays open, or clean
up code for the connection, etc.

 

[Sahil Malik [MVP]]

But it is about a design pattern.

Let us agree on one thing. Having a UI or external entity involved in a
transaction is a bad idea. This is because then you have a resource locked
due to the transaction for an indefinite time.

So then what you get due to that is .. ONE method in which you can wrap
everything. That method is typically a "Save" method, or similar. Behind the
"Save" There might be a zillion layers, and gazillion routines, but the
Connection needs to stay within that scope. Not Global, not outside that.

What do you think?

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Ray Booysen]

In my case the UI doesn't have any reason to touch the connection or
transaction. It doesn't use it.

Not sure I understand what you mean in your second paragraph.

Regards

 

[Cor Ligthert [MVP]]

Let us agree on one thing. Having a UI or external entity involved in a
transaction is a bad idea. This is because then you have a resource locked
due to the transaction for an indefinite time.

Not discussing about that it is not the best method to have the
dataretrieving actions in the UI, am I curious why you tell that by that a
resource is locked.

This two statement without any sense in the UI locks it really very short
myconnection.open();
myconnection.dispose();

This is for me the same as would it be done in a whatever seperate class.

Cor

 

[Sahil Malik [MVP]]

Well, think of it this way, you are saving data because some external entity
caused the data to change. Typically a user, or an external system.

Because a transaction locks resources, and because the external resource and
it's interference is indeterminiate in nature (you don't know how long they
might take), it is advisable not to involve that external data changing
stimulus .. as a part of your transaction.

In other words, for all practical purposes, you can seperate your
transactional code to that one method, that the external stimulus will
call - and that should be the scope of your transaction object, no more.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Sahil Malik [MVP]]

..... I'm not sure I understand you there boy. :-/

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Marina]

I think that is what we are saying. And then we are saying that this method
places the connection/transaction in such a location that any other method
it calls can access it there, and can just assume it will be there. Then
this 'Save' or whatever cleans everything up when everything is done.

 

[Sahil Malik [MVP]]

Thats cool then .


- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx