Thread: Postgresql UPDATE LOCKS unrelated rows.
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 ?
Regards,
Ravi
**************************Disclaimer************************************ Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individualor 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 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.