Re: Trouble with locking tables - reg.

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