Обсуждение: Postgresql UPDATE LOCKS unrelated rows.

On Thu, 18 Sep 2003, Ravi T Ramachandra wrote:

>
> Hi,
>
> We are using postgresql 7.3.2 on lunux platform.   We performed locking
> test as follows via JDBC:
>
> Started 2 concurrent transaction with autocommit false and isolation
> READ_COMMITTED :
>
> Transaction 1:
> ===========
> Update row 1
> Sleep for 30 seconds
> commit:
>
> Transaction 2:
> ===========
> Updated row 10000
> commit
>
> The second update had to wait for the first update operation to finish.
> We also made sure that the 2 unrelated rows are not on the same page.
>
> Is this expected behavior ?
> Does update lock the whole table ?

Generally not, but you really haven't given alot of information.  What is
the table layout for the table in question?  Are there foreign keys
involved?

Hi Stephan,

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

Regards,
Ravi

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: Thursday, September 18, 2003 8:32 PM
To: Ravi T Ramachandra
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Postgresql UPDATE LOCKS unrelated rows.



On Thu, 18 Sep 2003, Ravi T Ramachandra wrote:

>
> Hi,
>
> We are using postgresql 7.3.2 on lunux platform.   We performed
locking
> test as follows via JDBC:
>
> Started 2 concurrent transaction with autocommit false and isolation
> READ_COMMITTED :
>
> Transaction 1:
> ===========
> Update row 1
> Sleep for 30 seconds
> commit:
>
> Transaction 2:
> ===========
> Updated row 10000
> commit
>
> The second update had to wait for the first update operation to
> finish. We also made sure that the 2 unrelated rows are not on the
> same page.
>
> Is this expected behavior ?
> Does update lock the whole table ?

Generally not, but you really haven't given alot of information.  What
is the table layout for the table in question?  Are there foreign keys
involved?


**************************Disclaimer************************************

Information contained in this E-MAIL being proprietary to Wipro Limited is
'privileged' and 'confidential' and intended for use only by the individual
 or entity to which it is addressed. You are notified that any use, copying
or dissemination of the information contained in the E-MAIL in any manner
whatsoever is strictly prohibited.

***************************************************************************

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.