Enlistment does not work with pooled connections [DNET91]

[firebird-automations]

Submitted by: Barry Schwarz (bschw)

Assigned to: @carlosga

Attachments: Program.cs

Enlistment of pooled connections fails because the internal connections has no owning connection when the enlistment is attempted.

I have updated files that provide a solution to this problem. They also handle putting the internal connection back in the pool when the enlistment is done. I'll be happy to send these to whoever is assigned this bug.

[firebird-automations]

Commented by: @carlosga

That should be fixed in 2.1.0 RC 1 could you, please, give a try with it ??

[firebird-automations]

Commented by: Barry Schwarz (bschw)

I found a few problems with the 2.1 RC 1 release. I have source code that code for my version of the modifications and a program that illustrates the problems. Where can I send or put these so you can see?

My modifications don't suffer from these problems:

1) The internal connection is placed back in the pool when FbConnection.Close() is invoked. This is bad because another thread or implicit tranascation may reuse the internal connection before the transaction is commited.

2) If a connection is setup for enlistment, it throws a NullReferenceException if Transaction.Current is null.

All versions of the code have this problem:

If Open() is called and the enlistment fails, the FbConnection thinks it is open despite the fact that Open() threw an exception.
Some exception handling code needs to be placed around the inner connection's EnlistTransaction call to put the FbConnection back in the closed state if the enlistment fails. The sample program compensates for this but does not test for a fix.

There are a few other features I need. I would rather contribute code for these features than maintain my own modified version of the .Net client. These features are:

a) Two phase commits using calls to isc_prepare_transaction so I can mix firebird transactions with other databases. At a minimum, I need this for embedded databases. b) I woud like to tell an FbTransaction to use FbTransactionOptions.RecVersion instead of FbTransactionOptions.NorecVersion when configured with IsolationLevel.ReadCommitted. I don't want to use IsolationLevel.ReadUncommitted in tranactions with other databases and I need the looser locking.

Let me know how I can work with you to get all of this fixed or done.

                                     Barry

[firebird-automations]

Commented by: @carlosga

>1) The internal connection is placed back in the pool when FbConnection.Close() is invoked. This is bad because >another thread or implicit tranascation may reuse the internal connection before the transaction is commited.

Huummm in first place this is not going to change, every thread should have is own connection so i think there shouldn't be problem there.

> 2) If a connection is setup for enlistment, it throws a NullReferenceException if Transaction.Current is null.

I have committed a change to SVN that should allow to avoid this one.

> If Open() is called and the enlistment fails, the FbConnection thinks it is open despite the fact that Open() threw an exception.

I will try to test this but in first palce the exception should be catched in FbConnection.Open and the connection state should be changed to Closed.

>a) Two phase commits using calls to isc_prepare_transaction so I can mix firebird transactions with other databases. >At a minimum, I need this for embedded databases.

Not sure about how to achieve this, i'm not sure if isc_prepare_transaction will play well with the way TransactionScope should work ( aka my doubt is if we can add connections in a dynaic way to the prepared transaction )

>b) I woud like to tell an FbTransaction to use FbTransactionOptions.RecVersion instead of FbTransactionOptions.NorecVersion >when configured with IsolationLevel.ReadCommitted. I don't want to use IsolationLevel.ReadUncommitted in tranactions with >other databases and I need the looser locking.

I haven't understood this well, sorry, couldn't you using the overload that takes the FbTransactionOptions ??

i think it could be nice to discuss this on the devel list to hear people opinion :) can you send the email there too, please ?? XD

[firebird-automations]

Commented by: Barry Schwarz (bschw)

You mentioned I should post this stuff somehere else. Where is that?

The Program.cs file shows the connection pooling problem. The internal connection cannot be placed back in the pool until both its enlistement ends and it is closed.

The code looks right for exceptions thrown in Open. I don't know why I though that problem happened before.

I have modified code that calls isc_prepare_transaction in the Prepare method of FbTransaction. I call FbTransaction.Prepare from FbEnlistmentNotification.Prepare. This seems to work.

------------------------------------------------------------------------------------------------------------------------------ >b) I woud like to tell an FbTransaction to use FbTransactionOptions.RecVersion instead of FbTransactionOptions.NorecVersion >when configured with IsolationLevel.ReadCommitted. I don't want to use IsolationLevel.ReadUncommitted in tranactions with >other databases and I need the looser locking.

I haven't understood this well, sorry, couldn't you using the overload that takes the FbTransactionOptions ??

I have code that doesn't expose the FbTransaction or FbConnection directly to the client. We just tell people to wrap calls to certian objects in a transaction scope and we do the rest. We can use the overload instead but we would have to rewrite a large portion of the enlistment code a level up form where it is now. This would involve telling the FbConnection not to enlist and writing our own IEnlistmentNotification around an FbConnection and FbTransaction. This seems like a lot of wasted effort. I'd rather just have a connection option that tells the connection to use RecVersion with ReadCommitted. I don't want to use ReadUncommitted because another database may actually read uncommitted data which is not what I want at all.

[firebird-automations]

Modified by: Barry Schwarz (bschw)

Attachment: Program\.cs \[ 10412 \]

[firebird-automations]

Commented by: @carlosga

> You mentioned I should post this stuff somehere else. Where is that?

In the .net provider devel list, you can subscribe here:

https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

>The Program.cs file shows the connection pooling problem. The internal connection cannot >be placed back in the pool until both its enlistement ends and it is closed.

Huummm ok i will try to review this this weekend.

>I have modified code that calls isc_prepare_transaction in the Prepare method of FbTransaction. >I call FbTransaction.Prepare from FbEnlistmentNotification.Prepare. This seems to work.

Can you explain in the .net provider devel list what you have done, please ??

>I have code that doesn't expose the FbTransaction or FbConnection directly to the client. We just tell >people to wrap calls to certian objects in a transaction scope and we do the rest. We can >use the overload instead but we would have to rewrite a large portion of the enlistment code >a level up form where it is now. This would involve telling the FbConnection not to enlist and >writing our own IEnlistmentNotification around an FbConnection and FbTransaction. This seems like >a lot of wasted effort. I'd rather just have a connection option that tells the connection to >use RecVersion with ReadCommitted. I don't want to use ReadUncommitted >because another database may actually read uncommitted data which is not what I want at all.

Comment this in the .net provider devel list, if people didn't object we can do the change.

[firebird-automations]

Modified by: @carlosga

Fix Version: 2\.1\.0 RC 2 \[ 10180 \]

[firebird-automations]

Modified by: @carlosga

status: Open \[ 1 \] =\> Closed \[ 6 \] resolution: Fixed \[ 1 \]