OleDbConnection does not support parallel transactions

[Niraj]

Hi;

I am trying to use nested transaction but getting
exception

System.InvalidOperationException: OleDbConnection does not
support parallel transactions.

Please help
Thanks

Here is my code...

OleDbConnection conn =
GetConnection();
if( conn.State ==
ConnectionState.Closed )
conn.Open();

OleDbTransaction T1 =
conn.BeginTransaction();

string sqlStr = "INSERT INTO
XFER_REC( XFERLST_NAME, XFERLST_DESCRIPTION, STYLE,
SOURCE )"

+ " VALUES ( 'Test', 'test', 1, 3 )";

OleDbCommand cmd = new OleDbCommand
( sqlStr, conn, T1 );
cmd.CommandType = CommandType.Text;

try
{
cmd.ExecuteNonQuery();

//Let's try to add record
inside child table...
if(conn.State ==
ConnectionState.Closed )
conn.Open();
OleDbTransaction T2 =
conn.BeginTransaction();
sqlStr = "INSERT INTO
XFER_DO_MEASURES(XR_XFER_ID, XDM_ID, DO_MEASURE )"

+ " VALUES ( 71, 25, 111 )";

cmd = new OleDbCommand(
sqlStr, conn, T2 );
cmd.CommandType =
CommandType.Text;

cmd.ExecuteNonQuery();

T2.Commit();
T1.Commit();
}
catch( Exception e )
{
T1.Rollback();

System.Diagnostics.Trace.Write( e.ToString() );
}

 

[Charles Law]

Hi Niraj

How about opening a second connection and starting a transaction on that for
the duration of the nested transaction?

HTH

Charles

 

[Niraj]

Hi Charles;

Thanks for your suggestion.
I was able to overcome parallel transaction exception
but I ran into another exception...

"Could not read the record; currently locked by another
user"

Seems like, In Access 2000 I have to close first
connection & then work on second one.
By doing so, I will not have nested transaction...

Following is part of code...

try
{
cmd.ExecuteNonQuery();
T1.Commit(); //This OleDbTransaction has
completed; it is no longer usable
conn.Close(); //Could not read the record;
currently locked by another user

//Let's try to add record inside child table...
OleDbConnection conn1 = ps.GetConnection();
if(conn1.State == ConnectionState.Closed )
conn1.Open();
OleDbTransaction T2 = conn1.BeginTransaction();
sqlStr = "INSERT INTO XFER_DO_MEASURES
(XR_XFER_ID, XDM_ID, DO_MEASURE )"
+ " VALUES ( 71,
25, 111 )";

cmd = new OleDbCommand( sqlStr, conn1, T2 );
cmd.CommandType = CommandType.Text;

cmd.ExecuteNonQuery();

T2.Commit();

}

Any suggestion?

Thanks again.
Niraj

 

[Kevin Yu]

Thanks to Charles and David for providing good suggestion.

Niraj, Neither ODBC, nor any released OLE DB Provider, exposes Nested
Transaction functionality, even if the back-end data source supports this
feature.

See http://support.microsoft.com/default.aspx?scid=kb;en-us;177138 and
http://support.microsoft.com/default.aspx?scid=kb;en-us;313480 for more
information.

To workaround this problem, you can try to begin the next transaction in
another connection.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[David Browne]

Hi Charles;

Thanks for your suggestion.
I was able to overcome parallel transaction exception
but I ran into another exception...

"Could not read the record; currently locked by another
user"

Yeah, it's locked by your other connection.

This is why using 2 connections can't do everyting that nested transactions
can.


The good news is that it doesn't appear that you actually need nested
transactions.

this code:

OleDbTransaction T2 =
conn.BeginTransaction();
sqlStr = "INSERT INTO
XFER_DO_MEASURES(XR_XFER_ID, XDM_ID, DO_MEASURE )"

+ " VALUES ( 71, 25, 111 )";

cmd = new OleDbCommand(
sqlStr, conn, T2 );
cmd.CommandType =
CommandType.Text;

cmd.ExecuteNonQuery();

T2.Commit();

will run the same with or without a transaction. A single insert statement
is always atomic so running it in its own transaction is redundant.

David

 

[niraj shah]

Hi Kevin;

Thanks for your reply.
I am working with OLEDB.NET and Access 2000.
"Provider=Microsoft.Jet.OLEDB.4.0"

I have modified my code as follow.
Seems like its working but not sure if this right.
Please provide your feedback.

Thanks a lot.
Niraj



OleDbConnection conn = GetConnection();
if( conn.State == ConnectionState.Closed )
conn.Open();

OleDbTransaction T1 = conn.BeginTransaction();

string sqlStr = "INSERT INTO XFER_REC( XFERLST_NAME,
XFERLST_DESCRIPTION, STYLE, SOURCE )"
+ " VALUES ( 'Test', 'test', 1, 3 )";

OleDbCommand cmd = new OleDbCommand( sqlStr, conn, T1 );
cmd.CommandType = CommandType.Text;

try
{
cmd.ExecuteNonQuery();

//Let's try to add record inside child table...
if(conn.State == ConnectionState.Closed )
conn.Open();
OleDbTransaction T2 = T1.Begin();
sqlStr = "INSERT INTO XFER_DO_MEASURES(XR_XFER_ID, XDM_ID, DO_MEASURE
)"
+ " VALUES ( 71, 25, 111 )";

cmd = new OleDbCommand( sqlStr, conn, T2 );
cmd.CommandType = CommandType.Text;

cmd.ExecuteNonQuery();

T2.Commit();
T1.Commit();

}
catch( Exception e )
{
T1.Rollback();
System.Diagnostics.Trace.Write( e.ToString() );
}

 

[Charles Law]

Hi Niraj

I was about to post the same thing in VB.NET but I won't now unless anyone
wants it.

It works for me also.

The test is what happens when you experiment with committing and rolling
back T1 and T2. When you commit both then both updates are performed. When
you rollback just T1 then neither update is performed. If you just rollback
T2 then only the outer update is performed.

I would say that is a tick in the box.

Charles

 

[Charles Law]

Hi Kevin

Of the two articles you have referenced, I believe that only the second
relates to ADO.NET and the .NET Framework. In it, just below half way
(section "Transactions"), it seems to suggest that nested transactions _are_
supported. Is that the way you read it?

Charles

 

[niraj shah]

Hi Charles, Kevin & David

Thanks for your input.
Niraj

 

[niraj shah]

Hi Charles;

Thanks for your help in past.
I have one more Q may be you can help me out.
I am using Visio for database design.
Using Visio I generate Access database.
When I open my Access database & inspect relationship
between table. Cascade Delete constrains does not get
transfer into Aceess DB from Visio?

Can you give me right direction where to look for?
Thanks in advance.

Niraj

 

[Charles Law]

Hi Niraj

Unfortunately, I don't use Visio, so I'm not the best person to help with
this one. Sorry.

Hopefully someone else will respond.

Charles