Using Transactions in C#

[daniel schmied]

Hi,
i have some wired behaviour using c#, ado and sql server.

There are two tables on sql server: Order and Orderdetails.
(Order_ID is primary key on Table Order and foreign Key on
Orderdetails.)

I have two c# Classes calles Order and Orderdetails. They contain two
Methods for inserting Orders or Orderdetails on Database.
(Parameterized with an sql connection an an transaction.)

On the main programm a transaction is started an calling
Order.Insert(...)
Then, i do serveral Orderdetails.Insert(...) before committing the
transaction.

This works as supposed, but there is one big issue i cant figure out:

I start debugging the app and opend query-analyzer. After calling
Order.Insert(...)-but not yet committed the transaction - i am able to
do a "select * from order where order_id = 1".
BUT: After calling Orderdetail.Insert(...)in the code -but not yet
committing the transaction- i cant do a "select * from orderdetail
where order_id = 1), because Query Analyzer is waiting until the
transaction is commited.

Why that? I mean it did work for Order.Insert; why not for
Orderdetail.Insert???

When i use the command "select * from orderdetail with (readpast)
where order_id = 1" it asap returns data. (There are about 1000
Datarows in the Tables; that with order_id = 1 has been already
inserted. The isolationlevel is set to IsolationLevel.ReadUncommitted)

Can anyone help?

Thanks,
bye DS

 

[Guest]

I assume that you already checked this but just in case ... did you make sure
the transaction that the order is inserted within does not commit before
trying the insert of order details? In other words, if you have 2 separates
transactions, you might see this.

Another thing to test is to put a breakpoint on the insert order line of
code. While the execution is paused, run your query in Query Analyzer and it
should be in a wait state waiting for the transaction to commit. If not, then
you are in implicit transaction mode.

 

[daniel schmied]

Hi,

the transaction does not commit before trying to insert order details.
(Also i checked in SQL Profiler, they got the same SPID.)

Also test with breakpoint like you suggested:
Call Insert-Method of Order (not yet commited)-->Select is possible on
Table Order.
Call Insert-Method of Order Details (not yet commited)-->Select is not
possible on Table Order Details.

I would like to allow select while transaction is active. (Just like
it is possible in table Order.)

As Isolation Level for the transaction i tried ReadCommited and
ReadUncommited. -Same result.

What do you mean with implicit transaction mode?

Anyone some Ideas on this??

Thanks,
DS

 

[daniel schmied]

Really nobody any kind of idea?

Pain here....

Hi,

the transaction does not commit before trying to insert order details.
(Also i checked in SQL Profiler, they got the same SPID.)

Also test with breakpoint like you suggested:
Call Insert-Method of Order (not yet commited)-->Select is possible on
Table Order.
Call Insert-Method of Order Details (not yet commited)-->Select is not
possible on Table Order Details.

I would like to allow select while transaction is active. (Just like
it is possible in table Order.)

As Isolation Level for the transaction i tried ReadCommited and
ReadUncommited. -Same result.

What do you mean with implicit transaction mode?

Anyone some Ideas on this??

Thanks,
DS

 

[Guest]

Can you post the code for the transaction? Might just need a second pair of
eyes.

// John Papa
// http://codebetter.com/blogs/john.papa

 

[daniel schmied]

Hello,

i will post some code below. Just figured out another issue, maybe
that could be the right point to start.

From the main class (a webservice):

SqlConnection scon = new SqlConnection(strConnection);
strans = cm.scon.BeginTransaction(IsolationLevel.ReadCommitted);

int tmerr = oOrder.update(cm.scon,oOrder.Notification_ID, strans);
if(tmerr == 0)
{
strans.Rollback();
if(cm.scon.State == ConnectionState.Open) cm.scon.Close();
}
else
{
strans.commit();
if(cm.scon.State == ConnectionState.Open) cm.scon.Close();

}

--->
"int tmerr" holds the order-id which actually has been updated, but
not yet committed (For example 1500). the update-method executes a
simple stored proc which does a simple "update table order set......."

Right after calling "oOrder.update" -but not yet commit- i checked
Query-Analyzer and played around:

if i do the following statement, Query-Analyzer waits until above
transaction commits: "select * from order where client_id = 5". The
order-id 1500 is not associated to row corresponding client_id = 5. So
i dont understand why SQL is blocking here. (the resultset would not
contain a row with order-id 1500)

Very strange is, if i execute exactly the same query (after calling
oOrder.update, but not yet commit) exept the where-clause: "select *
from order where client_id = 5 AND order-id <> 1500" it works.

Is this an issue with indexing.....dont understand whats going on
here?!?

Thanks,
DS