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
