Re: [JDBC] 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 <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(at)postgresql(dot)org)
>
>
>
> !DSPAM:40c88c0d60177625298691!
>
>
--
Dave Cramer
519 939 0336
ICQ # 14675561