Trouble with locking tables - reg.

[Shanmugasundaram Doraisamy]

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 1: subscribe and unsubscribe commands go to ma*******@postgresql.org