From shan@ceedees.com Thu Jun 10 16:12:05 2004 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1921BD1B1D5; Thu, 10 Jun 2004 13:12:05 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 11025-05; Thu, 10 Jun 2004 16:11:58 +0000 (GMT) Received: from linux.ceedees.com (unknown [202.9.172.38]) by svr1.postgresql.org (Postfix) with ESMTP id BF988D1B1B3; Thu, 10 Jun 2004 13:11:53 -0300 (ADT) Received: from ceedees.com (vpsd2.ceedees.com [192.168.0.51]) by linux.ceedees.com (8.11.6/linuxconf) with ESMTP id i5AG3Zn06086; Thu, 10 Jun 2004 21:33:35 +0530 Message-ID: <40C7D7F9.6030305@ceedees.com> Date: Thu, 10 Jun 2004 09:09:37 +0530 From: Shanmugasundaram Doraisamy User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4.1) Gecko/20031114 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-jdbc@postgresql.org, pgsql-general@postgresql.org, pgsql-admin@postgresql.org Subject: Trouble with locking tables - reg. Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.6 tagged_above=0.0 required=5.0 tests=DATE_IN_PAST_12_24, HTML_MESSAGE, HTML_TAG_BALANCE_TABLE, LINES_OF_YELLING X-Spam-Level: X-Archive-Number: 200406/133 X-Sequence-Number: 13793 Dear Group, We are using Postgresql 7.3.4 on Redhat 8.0 with Java 1.4.2. We are developing our applications in Java. We call stored procedures from the java program. Order numbers are generated by many departments in the Hospital. We manitain a single table from which to select the order number. The way this works is that the order numbers are released for reuse if the order has been completed. We wrote a procedure in plpgsql with a transaction which locks the table for concurrency problem. When more than one person tries to generate an order number (by running the java program) still there arise the concurrency problem. We tried to check how the procedures with transaction that locks the table works . what we did to check the procedure was as follows we have one database server. we took two computer systems. in both system we opened one terminal (linux). let the value of the order number be 50. [1] in one system's terminal we started the transaction using begin; lock table ; [2] in another system we run the procedure which fetch the order number from the locked table ,display it -increment it - store it in the table again using update statement (not like order number = order number + 1) but like (x =order number +1), again we fetched the value of the order number from the table and display it . the procedure is as follows: CREATE OR REPLACE FUNCTION CHECKING() RETURNS TEXT AS' DECLARE XVAL INTEGER; BEGIN BEGIN LOCK TABLE CHECKING_LOCK; SELECT INTO XVAL X FROM CHECKING_LOCK WHERE Y = TRUE; RAISE NOTICE ''X BEF %'',XVAL; XVAL := XVAL + 1; UPDATE CHECKING_LOCK SET X = XVAL WHERE Y = TRUE; SELECT INTO XVAL X FROM CHECKING_LOCK WHERE Y = TRUE; RAISE NOTICE ''X AFT %'',XVAL; END; RETURN ''OK''; END; 'LANGUAGE 'PLPGSQL'; Now this procedure waits for the other transaction to complete [3] in the other system's terminal i update the field value - increment it by 1 and entered end; to commit the transaction [4] automatically the procedure runs and displays the result As per transaction isolation level ( read committed being the default isolation level) it should be 51 and 52. as when the transaction in the terminal update it to 51 , the transaction in the procedure which was waiting should fetch it as 51 and increment it by 1 (52) and set the field value to 52 and when fetched after update should return it the value as 52. this is what we want. but what is the actual is , The final result the procedure displays is 50 before update and 50 after update. when i verified in the database table it shows the field value as 51. how to make it to our expectation. Your immediate response in this regard is very much appreciate. Thanking you, Yours sincerely, Shan. From pg@fastcrypt.com Thu Jun 10 16:36:14 2004 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8D4C1D1B38B for ; Thu, 10 Jun 2004 13:36:14 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 25284-04 for ; Thu, 10 Jun 2004 16:36:09 +0000 (GMT) Received: from net2.micro-automation.com (net2.micro-automation.com [64.7.141.29]) by svr1.postgresql.org (Postfix) with SMTP id 9D66BD1B389 for ; Thu, 10 Jun 2004 13:36:07 -0300 (ADT) Received: (qmail 7513 invoked from network); 10 Jun 2004 16:36:03 -0000 Received: from dcdsl.ebox.com (HELO ?192.168.1.36?) (davec@64.7.143.116) by net2.micro-automation.com with SMTP; 10 Jun 2004 16:36:03 -0000 Subject: Re: [JDBC] Trouble with locking tables - reg. From: Dave Cramer Reply-To: pg@fastcrypt.com To: Shanmugasundaram Doraisamy Cc: "pgsql-jdbc@postgresql.org" , "pgsql-general@postgresql.org" , pgsql-admin@postgresql.org In-Reply-To: <40C7D7F9.6030305@ceedees.com> References: <40C7D7F9.6030305@ceedees.com> Content-Type: text/plain Organization: Cramer Consulting Message-Id: <1086885362.5639.183.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Thu, 10 Jun 2004 12:36:02 -0400 Content-Transfer-Encoding: 7bit X-AntiVirus: scanned for viruses by AMaViS 0.2.1 (http://amavis.org/) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=3.2 tagged_above=0.0 required=5.0 tests=HTML_MESSAGE, HTML_TAG_BALANCE_TABLE, SUSPICIOUS_RECIPS X-Spam-Level: *** X-Archive-Number: 200406/134 X-Sequence-Number: 13794 Functions can't have transactions inside of them You need to start the transaction then call the function. couple of questions though why not use sequences ? Then you have no concurrency issues. They are guaranteed to be incremented. Also you can use select for update, instead of locking the entire table. Dave On Wed, 2004-06-09 at 23:39, Shanmugasundaram Doraisamy wrote: > Dear Group, > We are using Postgresql 7.3.4 on Redhat 8.0 with > Java 1.4.2. We are developing our applications in Java. We call stored > procedures from the java program. Order numbers are generated by many > departments in the Hospital. We manitain a single table from which to > select the order number. The way this works is that the order numbers > are released for reuse if the order has been completed. We wrote a > procedure in plpgsql with a transaction which locks the table for > concurrency problem. When more than one person tries to generate an > order number (by running the java program) still there arise the > concurrency problem. > > We tried to check how the procedures with transaction that > locks the table works . what we did to check the procedure was as follows > we have one database server. > we took two computer systems. in both system we opened > one terminal (linux). > let the value of the order number be 50. > [1] in one system's terminal we started the transaction > using begin; lock table
; > [2] in another system we run the procedure which fetch > the order number from the locked table ,display it -increment it - store > it in the table again using update statement (not like order number = > order number + 1) but like (x =order number +1), again we fetched the > value of the order number from the table and display it . the procedure > is as follows: > > CREATE OR REPLACE FUNCTION CHECKING() RETURNS TEXT AS' > DECLARE > XVAL INTEGER; > BEGIN > BEGIN > LOCK TABLE CHECKING_LOCK; > SELECT INTO XVAL X FROM CHECKING_LOCK WHERE Y = TRUE; > RAISE NOTICE ''X BEF %'',XVAL; > XVAL := XVAL + 1; > UPDATE CHECKING_LOCK SET X = XVAL WHERE Y = TRUE; > SELECT INTO XVAL X FROM CHECKING_LOCK WHERE Y = TRUE; > RAISE NOTICE ''X AFT %'',XVAL; > END; > RETURN ''OK''; > END; > 'LANGUAGE 'PLPGSQL'; > > Now this procedure waits for the other transaction to complete > [3] in the other system's terminal i update the field value - > increment it by 1 and entered end; to commit the transaction > [4] automatically the procedure runs and displays the result > As per transaction isolation level ( read committed being the > default isolation level) it should be 51 and 52. > as when the transaction in the terminal update it to 51 , the > transaction in the procedure which was waiting should fetch it as 51 and > increment it by 1 (52) and set the field value to 52 and when fetched > after update should return it the value as 52. this is what we want. > but what is the actual is , > The final result the procedure displays is 50 before update and 50 > after update. > when i verified in the database table it shows the field value as > 51. > how to make it to our expectation. > > Your immediate response in this regard is very much appreciate. > Thanking you, > > Yours sincerely, > > Shan. > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > !DSPAM:40c88c0d60177625298691! > > -- Dave Cramer 519 939 0336 ICQ # 14675561