Thread: Automatic transactions in SELECT...
Hi all, We're in the process of deploying 9.2.1 to support an internal application we've been working on for the last few months. One of our programmers has recently complained that we get occasional hangs because "postgres automatically runs SELECT statements in a transaction that requires an explicit commit". That makes little sense and sounds like a dangling transaction, but in fairness I wanted to confirm that there isn't a set of defaults or flags that might cause the appearance of this behavior in version 9. I've been unable to find any references on this and would greatly appreciate comments or pointers to docs that would help to clarify the issue. Thanks, -Tom
Tom Lisjac <netdxr@gmail.com> writes:
> We're in the process of deploying 9.2.1 to support an internal
> application we've been working on for the last few months. One of our
> programmers has recently complained that we get occasional hangs because
> "postgres automatically runs SELECT statements in a transaction that
> requires an explicit commit".
> That makes little sense and sounds like a dangling transaction, but in
> fairness I wanted to confirm that there isn't a set of defaults or flags
> that might cause the appearance of this behavior in version 9.
There's no such behavior in the server. If you're using any client
library above the level of libpq, it might be trying to be "helpful"
by managing transactions --- but you've not said what your client-side
programmming environment is, so it's hard to speculate about that.
regards, tom lane
On Fri, Feb 8, 2013 at 6:16 PM, Tom Lisjac <netdxr@gmail.com> wrote: > We're in the process of deploying 9.2.1 to support an internal > application we've been working on for the last few months. One of our > programmers has recently complained that we get occasional hangs because > "postgres automatically runs SELECT statements in a transaction that > requires an explicit commit". > > That makes little sense and sounds like a dangling transaction, but in > fairness I wanted to confirm that there isn't a set of defaults or flags > that might cause the appearance of this behavior in version 9. I've been I believe that the easiest way find out what is wrong is to ask the programmer for more information of what exactly happens since the start of the transaction and till the moment when it hangs. He probably refers to the SELECTs that might occur during FK checks. They look like this: SELECT 1 FROM ONLY public.table1 x WHERE $1 OPERATOR(pg_catalog.=) column FOR SHARE OF x If so than it make sense. > unable to find any references on this and would greatly appreciate > comments or pointers to docs that would help to clarify the issue. > > Thanks, > > -Tom > > > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice -- Sergey Konoplev Database and software architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988 888 1979 Skype: gray-hemp Jabber: gray.ru@gmail.com
On 02/08/2013 08:23 PM, Tom Lane wrote: > Tom Lisjac <netdxr@gmail.com> writes: >> We're in the process of deploying 9.2.1 to support an internal >> application we've been working on for the last few months. One of our >> programmers has recently complained that we get occasional hangs because >> "postgres automatically runs SELECT statements in a transaction that >> requires an explicit commit". >> That makes little sense and sounds like a dangling transaction, but in >> fairness I wanted to confirm that there isn't a set of defaults or flags >> that might cause the appearance of this behavior in version 9. > There's no such behavior in the server. If you're using any client > library above the level of libpq, it might be trying to be "helpful" > by managing transactions --- but you've not said what your client-side > programmming environment is, so it's hard to speculate about that. > > regards, tom lane Hi Tom, Thanks for the quick reply... and good point about the layers between the app and db. The team is using Java/JBoss/Hibernate, so a great deal of "helpfulness" is possible where a setting somewhere in that stack might be the root cause. I'm in a management role on this and not that close to the details, but I've worked with Postgres as a developer, respect it greatly and don't want it unjustly blamed for a client-side error. Best regards, -Tom
Tom Lisjac <netdxr@gmail.com> writes:
> On 02/08/2013 08:23 PM, Tom Lane wrote:
>> There's no such behavior in the server. If you're using any client
>> library above the level of libpq, it might be trying to be "helpful"
>> by managing transactions --- but you've not said what your client-side
>> programmming environment is, so it's hard to speculate about that.
> Thanks for the quick reply... and good point about the layers between
> the app and db. The team is using Java/JBoss/Hibernate, so a great deal
> of "helpfulness" is possible where a setting somewhere in that stack
> might be the root cause.
Hm. That stuff is outside my expertise, but I'm fairly sure that the
JDBC driver does have some notion of transaction management, and if it
doesn't then JBoss or Hibernate probably do. Your issue is almost
surely in that stack somewhere.
The pgsql-jdbc list would be the best place to get help about the JDBC
driver. Dunno how much they know about JBoss or Hibernate though.
regards, tom lane
On 02/08/2013 09:17 PM, Tom Lane wrote: > Tom Lisjac <netdxr@gmail.com> writes: >> On 02/08/2013 08:23 PM, Tom Lane wrote: >>> There's no such behavior in the server. If you're using any client >>> library above the level of libpq, it might be trying to be "helpful" >>> by managing transactions --- but you've not said what your client-side >>> programmming environment is, so it's hard to speculate about that. >> Thanks for the quick reply... and good point about the layers between >> the app and db. The team is using Java/JBoss/Hibernate, so a great deal >> of "helpfulness" is possible where a setting somewhere in that stack >> might be the root cause. > Hm. That stuff is outside my expertise, but I'm fairly sure that the > JDBC driver does have some notion of transaction management, and if it > doesn't then JBoss or Hibernate probably do. Your issue is almost > surely in that stack somewhere. > > The pgsql-jdbc list would be the best place to get help about the JDBC > driver. Dunno how much they know about JBoss or Hibernate though. > > regards, tom lane Hi Tom, Again, thanks for your reply. The light bulb came on when you mentioned "helpful" client-side libraries and I remembered that the team is using a thick and fairly complex set of Java frameworks. That's where we'll start our troubleshooting efforts next week... and we *won't* be spending 30K for another database, which someone actually suggested. :o) Best regards, -Tom
Tom Lisjac wrote on 09.02.2013 03:16: > We're in the process of deploying 9.2.1 to support an internal > application we've been working on for the last few months. One of our > programmers has recently complained that we get occasional hangs because > "postgres automatically runs SELECT statements in a transaction that > requires an explicit commit". This behaviour is true for most (if not all) DBMS - defintely for Oracle, SQL Server and MySQL. If no transaction is active, _any_ (DML) statement (including SELECT) will start a new transaction. This can either be controlled via explicit transaction handling (BEGIN ... END) or the autocommit property of the connection. What exactly are those "occasional hangs"? How do they manifest themselves? The select doesn't lock the rows that were selected (unless a FOR UPDATE was used). Regards Thomas
On 02/09/2013 02:07 AM, Thomas Kellerer wrote: > Tom Lisjac wrote on 09.02.2013 03:16: >> We're in the process of deploying 9.2.1 to support an internal >> application we've been working on for the last few months. One of our >> programmers has recently complained that we get occasional hangs because >> "postgres automatically runs SELECT statements in a transaction that >> requires an explicit commit". > > This behaviour is true for most (if not all) DBMS - defintely for > Oracle, SQL Server and MySQL. > > If no transaction is active, _any_ (DML) statement (including SELECT) > will start a new transaction. > This can either be controlled via explicit transaction handling (BEGIN > ... END) or the > autocommit property of the connection. From a little googling, I believe the behavior you've described is called "implicit" or "autocommit" transactions. A previous posting said that Postgres doesn't support this, but perhaps that was regarding the "explicit commit" that I included in my description. I don't see a way for a SELECT in such a transaction to generate a hang from the descriptions of the autocommit connection property I've seen. > > What exactly are those "occasional hangs"? How do they manifest > themselves? > I wish I had more specifics. I'm working from a rambling paper that claims Postgres uniquely runs SELECT statements inside transactions which is causing the app to hang when there's no explicit commit/rollback provided afterward. The paper also claims that "other databases" don't behave that way and have "solved this problem long ago". None of that makes any sense and my goal is to understand and clarify the real issues so we can steer the team toward solving the problem instead of throwing rocks at Postgres. As Tom Lane pointed out, the JDBC/JBoss/Hibernate client-side frameworks we're running could be contributing to this problem by being overly helpful with transaction management. A google for "hibernate hangs on select" returns a large number of hits, so this appears to be a good area to start troubleshooting. Thanks for your reply. -Tom
Tom Lisjac <netdxr@gmail.com> wrote: > I wish I had more specifics. I'm working from a rambling paper > that claims Postgres uniquely runs SELECT statements inside > transactions which is causing the app to hang when there's no > explicit commit/rollback provided afterward. The paper also > claims that "other databases" don't behave that way and have > "solved this problem long ago". None of that makes any sense and > my goal is to understand and clarify the real issues so we can > steer the team toward solving the problem instead of throwing > rocks at Postgres. > > As Tom Lane pointed out, the JDBC/JBoss/Hibernate client-side > frameworks we're running could be contributing to this problem by > being overly helpful with transaction management. A google for > "hibernate hangs on select" returns a large number of hits, so > this appears to be a good area to start troubleshooting. As Tom said, there is no way to turn on "chained" transactions (or turn off "autocommit") on the server side -- all multi-statement transactions must be explicitly started and ended by the client. So it is impossible for the problem to be anywhere except the client side. I have seen a couple points of confusion by application programmers which might contribute to what you describe, which I'll explain in hopes that it will help you sort things out. (1) If autocommit is off in the PostgreSQL JDBC driver, it tracks whether an explicit transaction is pending on the connection, and if you execute a statement when a transaction is not pending it starts one by executing a BEGIN statement before executing the requested statement. This transaction will stay open until you invoke the Connection.commit() method. Some programmers seem to feel that if they issue a simple SELECT in this mode it should not start a transaction, but that would leave transaction boundaries ill-defined. What if you want to run multiple SELECT statements and see a consistent view of the data, or use the results of the first query to determine whether some other action should occur -- without keeping the transaction open, you can't assume it is *still* true when you get to the next statement. Assuming that a simple SELECT was not the first statement in a transaction would be a fragile assumption based on what functions, rules, and triggers can do. If nothing else, they may acquire locks which need to be held to ensure the integrity of a subsequent operation. Unless the JDBC driver honors the autocommit setting even for a simple SELECT, there are useful things you just can't do. (2) I have seen programmers try to deal with this by setting autocommit on before they run some SELECT statements, and set autocommit off for cases which they determine need that setting. So far so good. If they do that correctly, all is well. But then they forget to commit() some transaction(s) which leads many problems. The work of the transaction is not visible to other transactions until commit, locks are held until commit (blocking other transactions), and the work of vacuum processes is compromised because old versions of rows can't be cleaned up until any transaction still able to see the rows commit. So they add an unconditional commit somewhere to ensure that this doesn't happen. If the point where they put the unconditional commit can be reached with either autocommit setting, the problem point is here: http://docs.oracle.com/javase/7/docs/api/java/sql/Connection. html#commit%28%29 Note the documentation says: | This method should be used only when auto-commit mode has been | disabled. | Throws: | SQLException - [...] if this method is called on a closed | conection or this Connection object is in | auto-commit mode To solve this problem, the code like this: connection.commit(); would need to be changed to: if (!connection.getAutoCommit()) connection.commit(); Believe it or not, I have heard of programmers who refuse to deal with this conforming behavior because some other driver ignores the API requirement. Personally, I tend to think the API makes sense because it makes it harder to accidentally use non-transactional behavior when you really wanted transactional behavior; you have to make the mistake in both places or the JDBC driver keeps you honest. The behavior required by the API catches some sloppy programming and generates an error for it rather than letting incorrect behavior go unnoticed. The problem could be somewhere else in the client-side stack, but since I have seen these issues I figured I should pass along the info in hopes that it will help. -Kevin
On 02/09/2013 10:51 AM, Kevin Grittner wrote: > Tom Lisjac <netdxr@gmail.com> wrote: > >> I wish I had more specifics. I'm working from a rambling paper >> that claims Postgres uniquely runs SELECT statements inside >> transactions which is causing the app to hang when there's no >> explicit commit/rollback provided afterward. The paper also >> claims that "other databases" don't behave that way and have >> "solved this problem long ago". None of that makes any sense and >> my goal is to understand and clarify the real issues so we can >> steer the team toward solving the problem instead of throwing >> rocks at Postgres. >> >> As Tom Lane pointed out, the JDBC/JBoss/Hibernate client-side >> frameworks we're running could be contributing to this problem by >> being overly helpful with transaction management. A google for >> "hibernate hangs on select" returns a large number of hits, so >> this appears to be a good area to start troubleshooting. > As Tom said, there is no way to turn on "chained" transactions (or > turn off "autocommit") on the server side -- all multi-statement > transactions must be explicitly started and ended by the client. > So it is impossible for the problem to be anywhere except the > client side. > > I have seen a couple points of confusion by application programmers > which might contribute to what you describe, which I'll explain in > hopes that it will help you sort things out. > > (1) If autocommit is off in the PostgreSQL JDBC driver, it tracks > whether an explicit transaction is pending on the connection, and > if you execute a statement when a transaction is not pending it > starts one by executing a BEGIN statement before executing the > requested statement. This transaction will stay open until you > invoke the Connection.commit() method. Some programmers seem to > feel that if they issue a simple SELECT in this mode it should not > start a transaction, but that would leave transaction boundaries > ill-defined. What if you want to run multiple SELECT statements > and see a consistent view of the data, or use the results of the > first query to determine whether some other action should occur -- > without keeping the transaction open, you can't assume it is > *still* true when you get to the next statement. Assuming that a > simple SELECT was not the first statement in a transaction would be > a fragile assumption based on what functions, rules, and triggers > can do. If nothing else, they may acquire locks which need to be > held to ensure the integrity of a subsequent operation. Unless the > JDBC driver honors the autocommit setting even for a simple SELECT, > there are useful things you just can't do. > > (2) I have seen programmers try to deal with this by setting > autocommit on before they run some SELECT statements, and set > autocommit off for cases which they determine need that setting. > So far so good. If they do that correctly, all is well. But then > they forget to commit() some transaction(s) which leads many > problems. The work of the transaction is not visible to other > transactions until commit, locks are held until commit (blocking > other transactions), and the work of vacuum processes is > compromised because old versions of rows can't be cleaned up until > any transaction still able to see the rows commit. So they add an > unconditional commit somewhere to ensure that this doesn't happen. > > If the point where they put the unconditional commit can be reached > with either autocommit setting, the problem point is here: > > http://docs.oracle.com/javase/7/docs/api/java/sql/Connection. > html#commit%28%29 > > Note the documentation says: > > | This method should be used only when auto-commit mode has been > | disabled. > > | Throws: > | SQLException - [...] if this method is called on a closed > | conection or this Connection object is in > | auto-commit mode > > To solve this problem, the code like this: > > connection.commit(); > > would need to be changed to: > > if (!connection.getAutoCommit()) > connection.commit(); > > Believe it or not, I have heard of programmers who refuse to deal > with this conforming behavior because some other driver ignores the > API requirement. Personally, I tend to think the API makes sense > because it makes it harder to accidentally use non-transactional > behavior when you really wanted transactional behavior; you have to > make the mistake in both places or the JDBC driver keeps you > honest. The behavior required by the API catches some sloppy > programming and generates an error for it rather than letting > incorrect behavior go unnoticed. > > The problem could be somewhere else in the client-side stack, but > since I have seen these issues I figured I should pass along the > info in hopes that it will help. > > -Kevin Hi Kevin, Thanks for the detailed information in this excellent posting. The clarifications you've provided will be very useful to us in our next troubleshooting steps and anyone else that visits the list archives with similar questions. The compelling volume of information at this point should be enough to convince our team to focus on the client side stack. Many thanks to everyone who responded with insights and suggestions! Best regards, -Tom