Wt::Dbo::Transaction best practices

Added by Mark Petryk 6 months ago

What are the 'best practices' for employing the Dbo Transaction object?

I find that in many of my processes, when I'm doing lots of database updates, I will often start up a Transaction before the loop, and leave it open throughout the modify() loop, potentially touching 1000's of items, and then finally the Transaction drops out of existence.

This works for most cases, but this doesn't seem like a 'best practice'. It seems like this transaction would start, and many records would update, and none of those updates would be pushed to the database until that transaction closes.

Should I be instead opening the transaction only when I need it, thus causing updates record-by-record?

I know the Transaction is needed... but, how should I be thinking about how and where and when to use it?


Replies (7)

RE: Wt::Dbo::Transaction best practices - Added by Mark Petryk 6 months ago

...and to further my question, what about 'nested' transaction?

What if I start up a large query, and hold that transaction outside the loop, but inside the loop, some sub-process opens another transaction? What does that mean, really?

void subProcess( Wt::Dbo::ptr<MyItemClass> item )
{
  Wt::Dbo::Transaction t(*session());

  item.modify()-> somefield = somethingelse;
}

Wt::Dbo::Transaction t(*session());
auto items =
  session()-> find< MyItemClass >()
  .where( "somefield = ?").bind( somevalue )
  resultList();
for( auto item : items )
{
  subProcess( item );
}

So, the main process has an open transaction, and then the sub-process opens a transaction (and then terminates it)...

Is this a bad practice?
Should I be paying closer attention to the open transactions?
How can I find out if there's a straggling open transaction somewhere?

RE: Wt::Dbo::Transaction best practices - Added by lm at 6 months ago

There are several aspects of which I'm aware with regard to transaction placement and scope.

The most import thing is "correctness". If you're doing an update that should not be observed half-way through, put it in a transaction. For instance, if you increment your "client count" in one transaction then actually add the client in another transaction, there is a period of time during which the data are inconsistent. You can avoid this by making sure both actions are in a transaction.

If you have 1000s of actions that are each "complete" and can take their own transaction, there is still a potential reason to group them up into one transaction: performance. It can be costly to open and close transactions.

On the other hand, if you have 1000000s of actions it may be that you can't practically do it in one transaction. When you open a transaction and make changes within it, the database needs to keep track of those changes separate from the committed data. The database has to maintain both copies of the data: the originals and your updates. At best, it can only get rid of one or the other when you commit or rollback. The amount of space that a database has for this kind of accounting is sometimes called "rollback" space. If the rollback space is insufficient for the number of changes you are doing in one transaction, you can have problems during the transaction. Breaking the transaction into several smaller ones may make a change succeed where it otherwise would have failed with insufficient rollback space.

"What does that mean, really?" I think it means nothing: the inner transactions don't have any effect. I don't know how to tell if there's an open transaction; perhaps there's some Wt::Dbo::Session API for it, but I don't think it's an issue unless you're noticing performance problems (1000s of nested transactions might have an effect on database performance?).

RE: Wt::Dbo::Transaction best practices - Added by Mark Petryk 6 months ago

Hi LM, thank you!

What about 'nested' transactions, where one transaction is open, and some sub-routine runs and opens another transaction?

Are these transactions then stacked but separate? Are the modify() that occur in that second open transaction contained within that transaction?

That's the element I have a hard time following. The transaction seems to be a separate thing from the actual updates.

RE: Wt::Dbo::Transaction best practices - Added by lm at 6 months ago

In my experience, the inner transaction opening and closing has no effect: the changes are not committed nor rolled back until the outer transaction commits or rolls back.

RE: Wt::Dbo::Transaction best practices - Added by Koen Deforche 6 months ago

Hey,

The design is so that in a function, of which you don't want to assume how and when it will be called, and if that function requires a transaction, it should use one. At least that makes the function work in all situations. If the calling function wants to group multiple things in one transaction oand/or has its own database manipulations, then he can do so by declaring a transaction as well. The nested transaction does not propagate to the database, and its overhead is a simple count. Only the outer transaction creates an actual transaction in the database. That transaction has the meaning to propagate all the changes only atomically to the database when it commits. There are several performance tradeoffs on transactions, in our experience (mostly posrgresql). Longer transaction are usually more efficient for the local task but may cause database locks that stall concurrent transactions. As you involve more objects in the transaction you may also end up with database deadlocks.

Regards,
Koen

RE: Wt::Dbo::Transaction best practices - Added by Mark Petryk 6 months ago

Thank you, sir!

That absolutely perfectly explains everything.

RE: Wt::Dbo::Transaction best practices - Added by Mark Petryk 6 months ago

...and the documentation says as much of what you just said... next time I'll go there first.

Sorry for the noise.

(1-7/7)