Locking issue

[Andrew Jaimes <andrewjaimes@xxxxxxxxxxx>]

Hi everyone,

I am running 'PostgreSQL 9.1.4, compiled by Visual C++ build 1500, 64-bit' on a Windows Server and I am having some Locking issues. Maybe anyone can let me know what is wrong with my example:

Imagine that we have two tables (t_users and t_records)

 

t_users contains 1 row per each user

t_records is a regular transactional table which contains a field userid with the user that created/updated the row.

 

CREATE TABLE t_users(userid        VARCHAR(10),

                     loginattempts INTEGER,

                     CONSTRAINT pk_t_users PRIMARY KEY (userid));

 

CREATE TABLE t_records(recordid    INTEGER,

                       description VARCHAR(100),

                       userid      VARCHAR(10)

                       REFERENCES t_users(userid) MATCH SIMPLE

                       ON UPDATE NO ACTION ON DELETE NO ACTION);

                 

INSERT INTO t_users (userid, loginattempts) VALUES ('andrew',0);

 

 The user logs in and starts a background process that contains a long TRANSACTION which updates/inserts rows in t_records with the user's id.  This process keeps the transaction open for 1 hour while it works with code like:

                  

/* Session #1 */

BEGIN TRANSACTION

 

/* Big loop */

INSERT INTO t_records (recordid, description, userid) VALUES (1,'Record #1','andrew');

 

/*… SOME CODE HERE */

 

/*.... */

 

/* Once the loop ends, it will COMMIT/ROLLBACK  the transaction */

ROLLBACK / COMMIT

/* END of Session #1 */

 

The user logs out and then tries to log back in after 30 minutes.  The login hangs because we are not be able to update records on t_user  (for userids used on Session#1 ) until the transaction on Session#1 is done:

 

/* SESSION #2 */

 

UPDATE t_users  SET loginattempts = 1 WHERE userid = 'andrew'

 

/*END SESSION #2*/

 Any comments or feedback will be appreciated.

Regards,

Andrew Jaimes