I'm performing some tests to evaluate frameworks for our DAO layer. One of the test cases is to replicate a table lock and check how the framework behaves in such situation. This tests are done using Java and mainly JPA, using Hibernate and EclipseLink as JPA vendors, also we're using Hibernate alone and we have a set of interfaces that acts as bridge to the frameworks to ease our tests against any other ORM framework.
I have successfully generated a table lock test case against our MySQL 5.5.11 database, recognized because the application running the test case just got frozen for 50 seconds (default value for lock wait time in MySQL) and then a SQLException related to table lock wait timeout exception appeared in the logs. Then I switched the configuration of the tests to connect to Postgres 9.0 and re executed the test, I could replicate the table lock but now the application just got frozen for 10 minutes, and no exception was thrown, so I have to stop the test execution.
I was searching about how to change this time for Postgres 9.0 and got a result for Postgres 9.3 here: Client Connection Defaults under lock_timeout. Then I checked the documentation for 9.0 and such parameter is not available. Do you know how can I change this in postgres configuration or in my Java client application, probably a JPA or Hibernate or EclipseLink specific configuration just to check the table lock exception from database? The worst scenario is that a table lock appears in production environment and we cannot recover from this unless the application is stopped (which is what we're testing along with the frameworks).
Try statement_timeout - it's been around for ages.
SET statement_timeout=1000;
-- try to take lock, and get an exception 1000ms later