Re: Trouble with locking tables - reg. - Mailing list pgsql-jdbc

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