Re: Postgresql UPDATE LOCKS unrelated rows.

On Fri, 19 Sep 2003, Ravi T Ramachandra wrote:

> Here is the table structure.  FYI, we tried the experiment with mysql
> and the concurrency is working fine.
>
> create table TABLE1( TABLE_ID IDENTITY NOT NULL ,
>                         VA_ID SMALLINT NOT NULL,
>
>                         V_STATES_ID SMALLINT NOT
> NULL,
>                         V_VOL VARCHAR(6),
>                         V_OBJ_ID INT,
>                         V_CELL SMALLINT,
>                         V_POOL VARCHAR(255),
>                     FOREIGN KEY(VA_ID) REFERENCES
> TABLE2(VA_ID) ON DELETE CASCADE,
>                     FOREIGN KEY(V_STATES_ID)
> REFERENCES V_STATES(V_STATES_ID),
>                     PRIMARY KEY(TABLE_ID));
> We were trying to update V_VOL using following statement:
>
> UPDATE TABLE1 SET V_VOL = 'ABCD' WHERE TABLE_ID = 100000

If both of those had the same va_id or v_states_id and you're using 7.3.2,
then it's probably waiting on the other transactions locks on the
referenced table.  It doesn't need to check if you're not changing the
referencing value, but it does until a later 7.3 version I think so you
may want to upgrade to 7.3.4 and try it there.