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