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
