Обсуждение: transaction started without BEGIN??? Please help!
Hi! I have a huge problem here. On a busy server (a web system, mainly java servlets/jsp running on tomcat) running postgresql-8.0.2, all of a sudden, one of the postgres processes gets into the state "idle in transation" (revealed by `ps axU pgsql'). We use transaction very sparesly, so this is a mystery to me. I've swithed on logging of all statements, and also added the transaction id. part of the log is attached. As you can see, the transaction id is incremented, for every query, until suddenly, it is not incremented anymore, but stays the same. It seems to me, from lookin at the logs, that postgres enters a transaction although no BEGIN statement was issued? I hope I'm doing a misinterpretation of the logs here? So, in a couple of minutes, all connections are taken and a hundred postgres procs sit here waiting for the transaction to finish so they can issue their inserts. First, there shouldn't be a transaction here at all, how come it starts? And how come there's no BEGIN in the log, if one is actually started? My solution when this happens (has happened a couple of times a day on a busy server) is to kill the process that is reported "idle in transaction". That way we loose data, I'd rather get it to commit. Is there a way to do that? Here are all the logging directives in postgresql.conf: log_destination = 'syslog' silent_mode = true log_line_prefix = '<%x>' log_statement = 'all' The attached log was grepped on the problematic PID, btw. postgresql-8.0.2 running on FreeBSD 4.10. jdbc 8.0.311 The icu patch is applied (http://people.freebsd.org/~girgen/postgresql-icu/) but I can't see how that would interfere with transaction handling? Thanks, Palle
Вложения
One thing I can think of is some kind of inadvertent SQL injection. Somebody put ; begin; into a string that got sent to the server unescaped? The only other thing I can think of is if there was a hole in the logic in one of your explicitly started transactions that allowed the handle to be returned to the pool with a transaction open. I don't know anything about java or tomcat, but it should be possible. Good luck. On 4/21/05, Palle Girgensohn <girgen@pingpong.net> wrote: > Hi! > > I have a huge problem here. On a busy server (a web system, mainly java > servlets/jsp running on tomcat) running postgresql-8.0.2, all of a sudden, > one of the postgres processes gets into the state "idle in transation" > (revealed by `ps axU pgsql'). > > We use transaction very sparesly, so this is a mystery to me. I've swithed > on logging of all statements, and also added the transaction id. part of > the log is attached. As you can see, the transaction id is incremented, for > every query, until suddenly, it is not incremented anymore, but stays the > same. It seems to me, from lookin at the logs, that postgres enters a > transaction although no BEGIN statement was issued? I hope I'm doing a > misinterpretation of the logs here? > > So, in a couple of minutes, all connections are taken and a hundred > postgres procs sit here waiting for the transaction to finish so they can > issue their inserts. > > First, there shouldn't be a transaction here at all, how come it starts? > And how come there's no BEGIN in the log, if one is actually started? > > My solution when this happens (has happened a couple of times a day on a > busy server) is to kill the process that is reported "idle in transaction". > That way we loose data, I'd rather get it to commit. Is there a way to do > that? > > Here are all the logging directives in postgresql.conf: > > log_destination = 'syslog' > silent_mode = true > log_line_prefix = '<%x>' > log_statement = 'all' > > The attached log was grepped on the problematic PID, btw. > > postgresql-8.0.2 running on FreeBSD 4.10. jdbc 8.0.311 > > The icu patch is applied > (http://people.freebsd.org/~girgen/postgresql-icu/) but I can't see how > that would interfere with transaction handling? > > Thanks, > Palle > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > > >
--On torsdag, april 21, 2005 13.43.27 +0000 Ian Harding <harding.ian@gmail.com> wrote: > One thing I can think of is some kind of inadvertent SQL injection. > Somebody put > > ; begin; > > into a string that got sent to the server unescaped? > > The only other thing I can think of is if there was a hole in the > logic in one of your explicitly started transactions that allowed the > handle to be returned to the pool with a transaction open. I don't > know anything about java or tomcat, but it should be possible. Thanks for the input. I'm leaning towards exactly this type of behaviour. BTW, how come the BEGINs are not logged? Is there no way to log the start of a transaction? I thought there would be a BEGIN, but there's not. /Palle > > Good luck. > > On 4/21/05, Palle Girgensohn <girgen@pingpong.net> wrote: >> Hi! >> >> I have a huge problem here. On a busy server (a web system, mainly java >> servlets/jsp running on tomcat) running postgresql-8.0.2, all of a >> sudden, one of the postgres processes gets into the state "idle in >> transation" (revealed by `ps axU pgsql'). >> >> We use transaction very sparesly, so this is a mystery to me. I've >> swithed on logging of all statements, and also added the transaction id. >> part of the log is attached. As you can see, the transaction id is >> incremented, for every query, until suddenly, it is not incremented >> anymore, but stays the same. It seems to me, from lookin at the logs, >> that postgres enters a transaction although no BEGIN statement was >> issued? I hope I'm doing a misinterpretation of the logs here? >> >> So, in a couple of minutes, all connections are taken and a hundred >> postgres procs sit here waiting for the transaction to finish so they can >> issue their inserts. >> >> First, there shouldn't be a transaction here at all, how come it starts? >> And how come there's no BEGIN in the log, if one is actually started? >> >> My solution when this happens (has happened a couple of times a day on a >> busy server) is to kill the process that is reported "idle in >> transaction". That way we loose data, I'd rather get it to commit. Is >> there a way to do that? >> >> Here are all the logging directives in postgresql.conf: >> >> log_destination = 'syslog' >> silent_mode = true >> log_line_prefix = '<%x>' >> log_statement = 'all' >> >> The attached log was grepped on the problematic PID, btw. >> >> postgresql-8.0.2 running on FreeBSD 4.10. jdbc 8.0.311 >> >> The icu patch is applied >> (http://people.freebsd.org/~girgen/postgresql-icu/) but I can't see how >> that would interfere with transaction handling? >> >> Thanks, >> Palle >> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 3: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly >> >> >> >>