pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dave Cramer <pg@fastcrypt.com>
To: Shanmugasundaram Doraisamy <shan@ceedees.com>
Cc: pgsql-jdbc@postgresql.org <pgsql-jdbc@postgresql.org>
Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Cc: pgsql-admin@postgresql.org
Subject: Re: [JDBC] Trouble with locking tables - reg.
Date: Thu, 10 Jun 2004 12:36:02 -0400
Message-ID: <1086885362.5639.183.camel@localhost.localdomain> (raw)
In-Reply-To: <40C7D7F9.6030305@ceedees.com>
References: <40C7D7F9.6030305@ceedees.com>

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 <table name>;
>                 [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




view thread (2+ messages)

Message-ID: <1086885362.5639.183.camel@localhost.localdomain>
Permalink:  ../1086885362.5639.183.camel@localhost.localdomain/
Also on:    postgresql.org/message-id/1086885362.5639.183.camel@localhost.localdomain

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: pg@fastcrypt.com, shan@ceedees.com, pgsql-jdbc@postgresql.org, pgsql-general@postgresql.org
  Subject: Re: [JDBC] Trouble with locking tables - reg.
  In-Reply-To: <1086885362.5639.183.camel@localhost.localdomain>
  References: <40C7D7F9.6030305@ceedees.com> <1086885362.5639.183.camel@localhost.localdomain>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox