Обсуждение: Problem dropping a table
I have a java application that is trying to dynamically drop a set of tables. Problem is, when it gets to a particular table and I execute the "drop table foo cascade" command from a prepared statement, the query never returns. It just hangs indefinitely. I presume that it is waiting on a lock for this table, but there is nothing that I know of that should be locking this table. So, I have two issues - the first, how do I prevent myself from hanging indefinitely? I tried 'setQueryTimeout' on the prepared statement, but it doesn't seem to have any effect. Still hangs indefinitely. Is setQueryTimeout implemented in the driver? I first posted on the regular mailing list, and Tom answered that the server should be supporting it. The second, how can I track down what is locking this table? I presume that it is some of my code somewhere... maybe a prepared statement that I didn't get closed - but I can't find it. I've checked my code twice for any reference to this table, and every use of it is properly closing the result sets and the prepared statement. It seems like maybe the driver isn't releasing a lock when it is supposed to? Any ideas? Thanks, Dan -- ***************************** Daniel C. Armbrust Biomedical Informatics Information Services Mayo Clinic Rochester Harwick 8-36 (507) 538-1549 daniel.armbrust@mayo.edu *****************************
Daniel Armbrust wrote: > I tried 'setQueryTimeout' on the prepared statement, but > it doesn't seem to have any effect. Still hangs indefinitely. Is > setQueryTimeout implemented in the driver? I first posted on the > regular mailing list, and Tom answered that the server should be > supporting it. I think that setQueryTimeout currently does nothing (it's just a hint anyway, so this behaviour is legal -- it's just that noone has had time to do a proper implementation). You could manually execute a "set statement_timeout" query to get a similar effect. > The second, how can I track down what is locking this table? I presume that it is some of my code somewhere... maybe aprepared statement that I didn't get closed - but I can't find it. I've checked my code twice for any reference to thistable, and every use of it is properly closing the result sets and the prepared statement. It seems like maybe the driverisn't releasing a lock when it is supposed to? Merely having a prepared statement or resultset referencing the table does not hold locks. The only thing that holds locks (AFAIK) is an open transaction that did something requiring a lock. So perhaps you have an open transaction on another connection that used the table but has not yet called commit()/rollback(), or you have a concurrently executing query that holds the lock? -O
Oliver Jowett wrote: > > Merely having a prepared statement or resultset referencing the table > does not hold locks. The only thing that holds locks (AFAIK) is an open > transaction that did something requiring a lock. So perhaps you have an > open transaction on another connection that used the table but has not > yet called commit()/rollback(), or you have a concurrently executing > query that holds the lock? > > -O > Well, thats good (but utterly confusing to me) to know... I'm not running any transactions, and nothing else is going on concurrently that I'm aware of. But it gives me a new direction to go hunting in. I've certainly spent enough time checking for unclosed resultsets and prepared statements. I know that the lock is being held by my code, I just don't know where. Funny thing is, this code is written to work on multiple databases - and I don't have any issues at all on DB2, MySQL, Oracle, MSAccess, or Hypersonic DB. Just Postgres, in this one set of queries. Which is starting to make me think I've uncovered a driver bug... I'm going to spend more time today trying to figure out which exact query of mine is establishing the lock. Dan -- **************************** Daniel Armbrust Biomedical Informatics Mayo Clinic Rochester daniel.armbrust(at)mayo.edu http://informatics.mayo.edu/
Dan Armbrust wrote: > Oliver Jowett wrote: > >> >> Merely having a prepared statement or resultset referencing the table >> does not hold locks. The only thing that holds locks (AFAIK) is an >> open transaction that did something requiring a lock. So perhaps you >> have an open transaction on another connection that used the table but >> has not yet called commit()/rollback(), or you have a concurrently >> executing query that holds the lock? >> >> -O >> > Thanks for your tip, I finally stopped looking in all the wrong places, and found the problem. In order to stream a large result set, I had called setAutoCommit(false) in one portion of the code. The code that was supposed to turn autocommit back on was not being called - hence, I had a transaction that was hung open. The only remaining oddity is that the open transaction was preventing me from removing a table that I never accessed in the transaction... Anyway, I finally got everything working the way it is supposed to on my end. Thanks, Dan -- **************************** Daniel Armbrust Biomedical Informatics Mayo Clinic Rochester daniel.armbrust(at)mayo.edu http://informatics.mayo.edu/