SQLException: java.sql.SQLException: ERROR: WaitOnLock: error on wakeup - Aborting this transaction - Mailing list pgsql-bugs

From: pgsql-bugs@postgresql.org
Reply-to: john.evans@2wrongs.com, pgsql-bugs@postgresql.org

John Evans (john.evans@2wrongs.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
geta 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
thetest, even if I don't change the number of iterations or threads or the statements executed.  There are 6 update
statementsso at 10 threads x 10 iterations that's 600 updates executed and I get anywhere from 17-89 failures (all of
theidentical 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
followingoptions to postmaster in the init scripts.  I get the same results without these options, with perhaps more of
theerrors... 

-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
about1 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'tbelieve 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