Re: [ADMIN] 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 = 10 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. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Postgresql UPDATE LOCKS unrelated rows.
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 = 10 Regards, Ravi -Original Message- From: Stephan Szabo [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2003 8:32 PM To: Ravi T Ramachandra Cc: [EMAIL PROTECTED] 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 1 > 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. *** ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
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 1 > 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? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
