Thread: Cost of opening and closing an empty transaction

Cost of opening and closing an empty transaction

From
Jon Leighton
Date:
Hello!

I'm one of the developers of the Ruby on Rails web framework.

In some situations, the framework generates an empty transaction block.
I.e. we sent a BEGIN and then later a COMMIT, with no other queries in
the middle.

We currently can't avoid doing this, because a user *may* send queries
inside the transaction.

I am considering the possibility of making the transaction lazy. So we
would delay sending the BEGIN until we have the first query ready to go.
If that query never comes then neither BEGIN nor COMMIT would ever be sent.

So my question is: is this a worthwhile optimisation to make? In
particular, I am wondering whether empty transactions increase the work
the database has to do when there are several other connections open?
I.e. does it cause contention?

If anyone has any insight about other database servers that would also
be welcome.

Thanks!

Jon Leighton

--
http://jonathanleighton.com/


Re: Cost of opening and closing an empty transaction

From
Claudio Freire
Date:
On Fri, Sep 21, 2012 at 7:46 AM, Jon Leighton <j@jonathanleighton.com> wrote:
> So my question is: is this a worthwhile optimisation to make? In
> particular, I am wondering whether empty transactions increase the work
> the database has to do when there are several other connections open?
> I.e. does it cause contention?

I found myself on a similar situation, with a different framework
(SQLAlchemy), and it turned out to be worthwhile, mainly because
regardless of the load generated on the database, which may or may not
be of consequence to a particular application, the very significant
saving of at least 4 roundtrips (send begin, receive ack, send commit,
receive ack) can be worth the effort.

In particular, my application had many and very likely such cases
where no query would be issued (because of caching), and we were able
to reduce overall latency from 20ms to 1ms. Presumably, the high
latencies were due to busy links since it was all on a private (but
very busy) network.

Now, from the point of view of what resources would this idle
transaction consume on the server, you will at least consume a
connection (and hold a worker process idle for no reason). If you want
high concurrency, you don't want to take a connection from the
connection pool unless you're going to use it, because you'll be
blocking other clients.


Re: Cost of opening and closing an empty transaction

From
"Albe Laurenz"
Date:
Jon Leighton wrote:
> I'm one of the developers of the Ruby on Rails web framework.
>
> In some situations, the framework generates an empty transaction
block.
> I.e. we sent a BEGIN and then later a COMMIT, with no other queries in
> the middle.
>
> We currently can't avoid doing this, because a user *may* send queries
> inside the transaction.
>
> I am considering the possibility of making the transaction lazy. So we
> would delay sending the BEGIN until we have the first query ready to
go.
> If that query never comes then neither BEGIN nor COMMIT would ever be
sent.
>
> So my question is: is this a worthwhile optimisation to make? In
> particular, I am wondering whether empty transactions increase the
work
> the database has to do when there are several other connections open?
> I.e. does it cause contention?
>
> If anyone has any insight about other database servers that would also
> be welcome.

The one thing that will be the same for all databases is that
saving the two client-server roud trips for BEGIN and COMMIT
is probably worth the effort if it happens often enough.

The question which resources an empty transaction consumes
is probably database specific; for PostgreSQL the expense is
not high, as far as I can tell.

Yours,
Laurenz Albe




Re: Cost of opening and closing an empty transaction

From
Ross Reedstrom
Date:
N.B. I realize this is an ancient email, but there's a significant issue that
didn't get raised. Opening a transaction and leaving it idle can be a major
pain on a MVCC database like PostgreSQL.  The reason is that this is the
dreaded 'idle in transaction' state.  If these tranactions become long lived
(waiting for a form submit, etc.) they can easily become oldest transaction in
the cluster, forcing the system to keep data for snapshots that far back. I'm
not an Oracle expert, but I understand this is an issue there as well, since
they have to keep replay logs to recreate that state as well. So besides the
wasted round trips, the issue of idle open transactions can be significant.

Ross
--
Ross Reedstrom, Ph.D.                                 reedstrm@rice.edu
Systems Engineer & Admin, Research Scientist        phone: 713-348-6166
Connexions                  http://cnx.org            fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E  F888 D3AE 810E 88F0 BEDE


On Mon, Sep 24, 2012 at 11:48:23AM +0200, Albe Laurenz wrote:
> Jon Leighton wrote:
> > I'm one of the developers of the Ruby on Rails web framework.
> >
> > In some situations, the framework generates an empty transaction
> block.
> > I.e. we sent a BEGIN and then later a COMMIT, with no other queries in
> > the middle.
> >
> > We currently can't avoid doing this, because a user *may* send queries
> > inside the transaction.
> >
> > I am considering the possibility of making the transaction lazy. So we
> > would delay sending the BEGIN until we have the first query ready to
> go.
> > If that query never comes then neither BEGIN nor COMMIT would ever be
> sent.
> >
> > So my question is: is this a worthwhile optimisation to make? In
> > particular, I am wondering whether empty transactions increase the
> work
> > the database has to do when there are several other connections open?
> > I.e. does it cause contention?
> >
> > If anyone has any insight about other database servers that would also
> > be welcome.
>
> The one thing that will be the same for all databases is that
> saving the two client-server roud trips for BEGIN and COMMIT
> is probably worth the effort if it happens often enough.
>
> The question which resources an empty transaction consumes
> is probably database specific; for PostgreSQL the expense is
> not high, as far as I can tell.
>
> Yours,
> Laurenz Albe
>
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>