I have another issue with transactions (you really see i have not much experice in the arena)

I have this

Transaction t = new Transaction(IsolationLevel.ReadComited, "MyUpdate")

OrderColellection c = OrderColellection(); c.GetMulti();

foreach(OrderLinie ol in c) {
t.Add(ol); ol.Name = "New value"; ol.Save(); }

t.commit();

Now this fails (timesout). This i can undestand since i have set a readlock in the table and tries to read the next line its locked since the previous uncommited save has the lock.

But. Why dosent it work when i set it to: Transaction t = new Transaction(IsolationLevel.ReadUncommitted, "MyUpdate")

Same happen. It locks in second loop (guess since the update is locking the table)

But isnt this wrong? help for ReadUncommitted _ A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored._

Anders

Anderskj1 wrote:

I have another issue with transactions (you really see i have not much experice in the arena)

I have this

Transaction t = new Transaction(IsolationLevel.ReadComited, "MyUpdate")

OrderColellection c = OrderColellection(); c.GetMulti();

foreach(OrderLinie ol in c) {
t.Add(ol); ol.Name = "New value"; ol.Save(); }

t.commit();

Now this fails (timesout). This i can undestand since i have set a readlock in the table and tries to read the next line its locked since the previous uncommited save has the lock.

You really should do: t.Add(c);

foreach(OrderLinie ol in c) {
ol.Name = "New value"; }

c.SaveMulti(); t.Commit();

Btw, the readlock is gone once the data is read, so that's not a problem. I assume another transaction in your application is locking the table (or a dangling process somewhere).

But. Why dosent it work when i set it to: Transaction t = new Transaction(IsolationLevel.ReadUncommitted, "MyUpdate")

Same happen. It locks in second loop (guess since the update is locking the table)

But isnt this wrong? help for ReadUncommitted _ A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored._

Hmm. That indeed should be the case. Odd. You speak of a second loop. I see just one loop, perhaps the problem is with 2 loops doing different work. Could you please past the complete routine?

Hmm. That indeed should be the case. Odd. You speak of a second loop. I see just one loop, perhaps the problem is with 2 loops doing different work. Could you please past the complete routine?

Yes of course (its a bit long)

Infact there are two subrutine. I have used your eariler suggestion about nested transactions: (mybe its easier to read if pasted into an editor...dont know)

Its locks where this is inserted:

// **** THIS GET LOCKS AFTER SECÒND ROUND ****

public class BatchEntity : BatchEntityBase
{
public BatchEntity SplitBatchAndClose(int toEjacNo)
{
    EjaculateCollection col = new EjaculateCollection();
    
    col.GetMulti(null);

    Transaction transaction = new Transaction(IsolationLevel.ReadUncommitted, "Split batch transaction");

    try
    {
        BatchEntity newbatch = new BatchEntity();
        
        newbatch.Batchnr = GetNextBatchNumber(this.Personid);
        
        transaction.Add(newbatch);
        newbatch.Save();

        // Move the ejaculate which should be split to a new batch
        foreach (EjaculateEntity ejaculate in col)
        {
            if (ejaculate.Number >= toEjacNo)
            {
                ejaculate.Batchid = newbatch.Batchid;
            }
        }               

        // Make the current batch closed.
        transaction.Add(this);
        this.Closed = true;
        this.Save(true);

        transaction.Add(col);
        col.SaveMulti(true);

        // Create credits for the closed batch (add it to the transaction)
        CreditManager creditmanager = new CreditManager(this.Personid);

        // Here i pass the current transaction to antoher object
        creditmanager.OnCloseBatch(transaction, newbatch.Batchid);

        transaction.Commit();
    }
    catch (Exception e)
    {
        transaction.Rollback();
        throw e;
    }
    finally
    {
        transaction.Dispose();
    }

    return null;
}
}

public class CreditManager
{
public override void OnCloseBatch(Transaction outer, long batchid)
{
    ITransaction t_inner = outer;

    // if we have a shared transaction ...use it. else create a now one
    if(outer==null) t_inner = new Transaction(IsolationLevel.ReadUncommitted, "Do calc");
    
    IPredicateExpression filter = new PredicateExpression();
    filter.Add(
    PredicateFactory.CompareValue(
    BatchFieldIndex.Batchid,
    ComparisonOperator.Equal, batchid));            

    BatchEntity batch = new BatchEntity(batchid);

    // Make all ejaculates in batch from UNDUE -> DUE
    foreach(EjaculateEntity ejaculate in batch.Ejaculate)
    {
        DonorcreditCollection credits = new DonorcreditCollection();
        
        creditfilter.AddWithAnd(
        PredicateFactory.CompareValue(
        DonorcreditFieldIndex.Credittypeid,
        ComparisonOperator.Equal, CreditTypeMapping.FirstHalf)); 
                                
        // **** THIS GET LOCKS AFTER SECÒND ROUND ****
        credits.GetMulti(creditfilter);

        if(credits.Count > 0)
        {
            t_inner.Add(credits[0]);
            credits[0].Due = true;
            credits[0].Save();
        }
    }
}
}

When the lock up occurs, could you check in the enterprise manager of SqlServer which processes lock the particular table DonorCredit (have locks on that table). ?

The situation what I can imagine is: thread A reads one set and write a row, thread B writes a row, thread A then tries to read again, but locks on B's row. B tries to read again but locks on A's row.

If you place an index on the field DonorCredit.Credittypeid you should not run into locks at all, as sqlserver will do an index scan, not a table scan.

I must say I found this loop in CreditManager a bit odd, as you loop over EjaculateEntity objects but no value of that entity is used inside the loop. So you can move the getmulti outside the loop, do it once.

I had cut to much. The ejaculate is used in the loop:

Sorry for my ignorance. But how do I see locks in Enterprise man? And what excatly do you mean my indexing credittypeid?

foreach(EjaculateEntity ejaculate in batch.Ejaculate) { DonorcreditCollection credits = new DonorcreditCollection();

IPredicateExpression creditfilter = new PredicateExpression();

    creditfilter.Add(
    PredicateFactory.CompareValue(
    DonorcreditFieldIndex.Ejaculateid,
    ComparisonOperator.Equal, ejaculate.Ejaculateid));

                                        creditfilter.AddWithAnd(
    PredicateFactory.CompareValue(
    DonorcreditFieldIndex.Credittypeid,
    ComparisonOperator.Equal, CreditTypeMapping.FirstHalf)); 

    credits.GetMulti(creditfilter);

    if(credits.Count > 0)
    {
        t_inner.Add(credits[0]);
        credits[0].Due = true;
        credits[0].Save();
    }
}

Edit. I just saw in the debugger that the transaction participants list is 400 entity objects! Is that normal or can that be the cause?

I see that a Lock is timing out (after a coupe of second after the tran is started)

Then the application locks for about 20 ses and timeout

SQLTransaction SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;BEGIN TRANSACTION [Split batch transaction] Lock:Timeout Lock:Cancel SQLTransaction SQL:BatchCompleted IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION SQLTransaction SQLTransaction

Anderskj1 wrote:

I had cut to much. The ejaculate is used in the loop:

Sorry for my ignorance. But how do I see locks in Enterprise man? And what excatly do you mean my indexing credittypeid?

Open enterprise manager, open the node 'Maintenance' in the sqlserver tree node, open 'current activity', then click open processinfo or locks/object and you can see which process locks which objects and which locks have been granted. This can help find deadlocks.

by adding an index, I meant adding a table index on the credittypeid column. Open the table in design mode in enterprise manager and add the index on the column (normal index). This will make sure sqlserver will issue an index scan, not a table scan for searching for rows.

---

foreach(EjaculateEntity ejaculate in batch.Ejaculate) { DonorcreditCollection credits = new DonorcreditCollection();

IPredicateExpression creditfilter = new PredicateExpression();

    creditfilter.Add(
    PredicateFactory.CompareValue(
    DonorcreditFieldIndex.Ejaculateid,
    ComparisonOperator.Equal, ejaculate.Ejaculateid));

                                        creditfilter.AddWithAnd(
    PredicateFactory.CompareValue(
    DonorcreditFieldIndex.Credittypeid,
    ComparisonOperator.Equal, CreditTypeMapping.FirstHalf)); 

    credits.GetMulti(creditfilter);

    if(credits.Count > 0)
    {
        t_inner.Add(credits[0]);
        credits[0].Due = true;
        credits[0].Save();
    }
}

I think you're doing something very inefficient. Now you execute batch.Ejaculate.Count times a query, while you can fetch them all at once in a GetMulti call: you loop over the id's already in SplitBatchAndClose, so you can add all id's to an arraylist there, and pass it to OnCloseBatch, for a FieldCompareRange predicate fetch. Then just loop over the collection and set Due to true.

Another much faster approach is to use UpdateMulti. This way you can update rows in the database directly as you just need to update a single field: just set all creditentity's due flag to true if they meet certain criteria, which you formulate in a predicate expression, based on the ejaculateid's and if count > 0. You then add the empty creditsCollection you create to execute the UpdateMulti() call to the transaction and the update is done inside the transaction, making the routine much faster and avoids any deadlocks. If you need help setting up this routine, let me know.

Edit. I just saw in the debugger that the transaction participants list is 400 entity objects! Is that normal or can that be the cause?

Oh that doesn't matter You can have thousands if you'd like, every participant's actions are ran inside a transaction and sqlserver simply logs these actions in the transaction log. As long as that one is large enough, you have no problems running large transactions in one batch.