SQLException: java.sql.SQLException: ERROR: WaitOnLock: error on wakeup - Aborting this transaction

From: pgsql-bugs(at)postgresql(dot)org
Reply-to: john(dot)evans(at)2wrongs(dot)com, pgsql-bugs(at)postgresql(dot)org

John Evans (john(dot)evans(at)2wrongs(dot)com) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
SQLException: java.sql.SQLException: ERROR: WaitOnLock: error on wakeup - Aborting this transaction

Long Description
I installed a clean copy of postgresql 7.0.3 on Redhat 6.1 Linux, the output of SELECT version(); is:
PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66

I created a table like so:

CREATE TABLE test_user
(
user_id INTEGER,
username VARCHAR(16),
password VARCHAR(16),
email VARCHAR(255),
sex VARCHAR(1),
year_of_birth INTEGER,
zipcode VARCHAR(6),
country INTEGER,
options INTEGER,
interest VARCHAR(50),
occupation VARCHAR(50)
)
;

I then created 'test' user and GRANTed all on test_user to the user test.

I then set up my (java) database testing client to connect to the database using this driver and this url:
jdbc.driver=org.postgresql.Driver
jdbc.url=jdbc:postgresql://oradev:5432/testdb?user=test&password=a

It then runs these commands:

DELETE FROM test_user
INSERT INTO test_user (user_id, username, password, email, sex, year_of_birth, zipcode, country, options, interest, occupation) VALUES (1, 'user1', 'pass1', 'email1', 'm', 1975, '20191', 1, 1, 'girls', 'doctor')
INSERT INTO test_user (user_id, username, password, email, sex, year_of_birth, zipcode, country, options, interest, occupation) VALUES (2, 'user2', 'pass1', 'email1', 'm', 1975, '20191', 1, 1, 'girls', 'doctor')
INSERT INTO test_user (user_id, username, password, email, sex, year_of_birth, zipcode, country, options, interest, occupation) VALUES (3, 'user3', 'pass1', 'email1', 'm', 1975, '20191', 1, 1, 'girls', 'doctor')
INSERT INTO test_user (user_id, username, password, email, sex, year_of_birth, zipcode, country, options, interest, occupation) VALUES (4, 'user4', 'pass1', 'email1', 'm', 1975, '20191', 1, 1, 'girls', 'doctor')
INSERT INTO test_user (user_id, username, password, email, sex, year_of_birth, zipcode, country, options, interest, occupation) VALUES (5, 'user5', 'pass1', 'email1', 'm', 1975, '20191', 1, 1, 'girls', 'doctor')
INSERT INTO test_user (user_id, username, password, email, sex, year_of_birth, zipcode, country, options, interest, occupation) VALUES (6, 'user6', 'pass1', 'email1', 'm', 1975, '20191', 1, 1, 'girls', 'doctor')
INSERT INTO test_user (user_id, username, password, email, sex, year_of_birth, zipcode, country, options, interest, occupation) VALUES (7, 'user7', 'pass1', 'email1', 'm', 1975, '20191', 1, 1, 'girls', 'doctor')
INSERT INTO test_user (user_id, username, password, email, sex, year_of_birth, zipcode, country, options, interest, occupation) VALUES (8, 'user8', 'pass1', 'email1', 'm', 1975, '20191', 1, 1, 'girls', 'doctor')
INSERT INTO test_user (user_id, username, password, email, sex, year_of_birth, zipcode, country, options, interest, occupation) VALUES (9, 'user9', 'pass1', 'email1', 'm', 1975, '20191', 1, 1, 'girls', 'doctor')
INSERT INTO test_user (user_id, username, password, email, sex, year_of_birth, zipcode, country, options, interest, occupation) VALUES (10, 'user10', 'pass1', 'email1', 'm', 1975, '20191', 1, 1, 'girls', 'doctor')
INSERT INTO test_user (user_id, username, password, email, sex, year_of_birth, zipcode, country, options, interest, occupation) VALUES (11, 'user11', 'pass1', 'email1', 'm', 1975, '20191', 1, 1, 'girls', 'doctor')
INSERT INTO test_user (user_id, username, password, email, sex, year_of_birth, zipcode, country, options, interest, occupation) VALUES (12, 'user12', 'pass1', 'email1', 'm', 1975, '20191', 1, 1, 'girls', 'doctor')
INSERT INTO test_user (user_id, username, password, email, sex, year_of_birth, zipcode, country, options, interest, occupation) VALUES (13, 'user13', 'pass1', 'email1', 'm', 1975, '20191', 1, 1, 'girls', 'doctor')
INSERT INTO test_user (user_id, username, password, email, sex, year_of_birth, zipcode, country, options, interest, occupation) VALUES (14, 'user14', 'pass1', 'email1', 'm', 1975, '20191', 1, 1, 'girls', 'doctor')
INSERT INTO test_user (user_id, username, password, email, sex, year_of_birth, zipcode, country, options, interest, occupation) VALUES (15, 'user15', 'pass1', 'email1', 'm', 1975, '20191', 1, 1, 'girls', 'doctor')
INSERT INTO test_user (user_id, username, password, email, sex, year_of_birth, zipcode, country, options, interest, occupation) VALUES (16, 'user16', 'pass1', 'email1', 'm', 1975, '20191', 1, 1, 'girls', 'doctor')
INSERT INTO test_user (user_id, username, password, email, sex, year_of_birth, zipcode, country, options, interest, occupation) VALUES (17, 'user17', 'pass1', 'email1', 'm', 1975, '20191', 1, 1, 'girls', 'doctor')
COMMIT
SELECT * FROM test_user WHERE user_id = 1
SELECT * FROM test_user WHERE user_id = 12
SELECT * FROM test_user WHERE user_id = 1
SELECT * FROM test_user WHERE user_id = 11
SELECT * FROM test_user WHERE user_id = 1
SELECT * FROM test_user WHERE user_id = 10
SELECT * FROM test_user WHERE user_id = 1
SELECT * FROM test_user WHERE user_id = 2
SELECT * FROM test_user WHERE user_id = 14
SELECT * FROM test_user WHERE user_id = 3
SELECT * FROM test_user WHERE user_id = 3
SELECT * FROM test_user
SELECT * FROM test_user ORDER BY user_id
SELECT MAX(year_of_birth) FROM test_user GROUP BY sex
UPDATE test_user SET sex = 'f'
UPDATE test_user SET sex = 'm' WHERE user_id = 12
UPDATE test_user SET sex = 'm' WHERE username = 'user13'
UPDATE test_user SET sex = 'f' WHERE sex = 'm'
UPDATE test_user SET sex = 'm' WHERE sex = 'f'
UPDATE test_user SET sex = 'm'
DELETE FROM test_user

The test client runs these commands N times in N threads simultaneously where my typical settings are 10 times in 10 threads. If I run them sequentially (1 thread) then everything works beautifully, but if I run them in a 10x10 then I get a bunch of these errors:

java.sql.SQLException: ERROR: WaitOnLock: error on wakeup - Aborting this transaction
at org.postgresql.Connection.ExecSQL(Connection.java:403)
at org.postgresql.jdbc2.Statement.execute(Statement.java:273)
at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:73)
at com.twowrongs.test.db.TestClient.run(TestClient.java:250)
at java.lang.Thread.run(Thread.java:479)

These errors only happen on the update statements. There will be a different number of these errors each time I try the test, even if I don't change the number of iterations or threads or the statements executed. There are 6 update statements so at 10 threads x 10 iterations that's 600 updates executed and I get anywhere from 17-89 failures (all of the identical kind above), although it's usually around 20.

I thought perhaps that the database just wasn't given enough resources to do what it needed, so I tuned it by the following options to postmaster in the init scripts. I get the same results without these options, with perhaps more of the errors...

-i -p 5432 -B 8192 -o -S 8192 -o -F

FWIW, I had to increased the shmmax on this system from 32M to 128M in order to specify the 8192 buffers.

This machine is a dual PIII 600mhz system with 2 gigabytes of RAM. It is also running Oracle but it only takes up about 1 G of space so there should be more than enough left for postgres. The Oracle instance is also idle 99% of the time (and I've checked to make sure it is specifically when I'm running my tests).

If you need any more information, let me know.

Sample Code
If you want the specific TestClient java code that is being executed, let me know and I can probably provide it, but I don't believe that it has anything to do with that code as it works fine with other databases running the same tests.

No file was uploaded with this report