Downsides of committing by default?

[Theron Luhn]

I’m using a cookiecutter-based Pyramid+zope.sqlalchemy+SQLAlchemy stack.  More and more lately I’ve been skipping the ORM and using Core for write operations, and I frequently run into issues where I forget to mark_changed and zope.sqlalchemy ROLLBACKs by default:

By default, zope.sqlalchemy puts sessions in an ‘active’ state when they are first used. ORM write operations automatically move the session into a ‘changed’ state. This avoids unnecessary database commits. Sometimes it is necessary to interact with the database directly through SQL. It is not possible to guess whether such an operation is a read or a write. Therefore we must manually mark the session as changed when manual SQL statements write to the DB.

The docs go on to describe how I can change the behavior to COMMIT by default:

If this is a problem you may register the events and tell them to place the session in the ‘changed’ state initially.

My question:  Is there any downside to COMMITing by default?  I assume there’s a good reason why the default is to ROLLBACK.  I’m using PostgreSQL, but I’d be interested in hearing about how this affects RDBMSs too.

— Theron

[Michael Merickel]

My understanding is that it’s a micro optimization that a rollback is slightly more efficient than a commit. I’m not aware of any good arguments against defaulting to changed. Would need to ask the zope.sqlalchemy folks for some insights. 

- Michael

On May 2, 2022, at 17:55, Theron Luhn <the...@luhn.com> wrote:

I’m using a cookiecutter-based Pyramid+zope.sqlalchemy+SQLAlchemy stack.  More and more lately I’ve been skipping the ORM and using Core for write operations, and I frequently run into issues where I forget to mark_changed and zope.sqlalchemy ROLLBACKs by default:

--
You received this message because you are subscribed to the Google Groups "pylons-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pylons-discus...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/pylons-discuss/598CAFEC-A22F-47E1-8AFE-7B06F29EC5CF%40luhn.com.

[Jonathan Vanasco]

IMHO it's not a micro-optimization. RDBMs systems will often take a performance hit on the COMMIT vs rollback when there are multiple simultaneous transactions, and it can cause issues on clustered/replicant systems.

I often forget about this too. The techniques that have worked for me:

* using sqlalchemy events to issue a mark_changed on the `before_execute` event. sometimes i'll have the event parse the statement sent to the backend for an insert/update/etc.
* never using `session.execute` directly. instead i use a helper function that takes the session and execute params, it then does the work and applies mark changed.

I've done a few other things too, but those are the ones I recall the most