Re: concurrency problem

>INSERT INTO rcp_patient_visit_monitor (
>                    entry_no, patient_id, visit_date,
> is_newpatient,
> visit_type, is_medical,
>                    is_review, is_labtest, is_scan,
> is_scopy, is_xray,
> weight, height)
>                    VALUES ((SELECT
> coalesce(max(entry_no)+1, 1) FROM
>
rcp_patient_visit_monitor),?,current_timestamp,?,?,?,?,?,?,?,?,?,?)

You are only working on one table so you sholdn't have
to manage a transaction or deal with explicit locking.
Just let the database handle this for you with a
sequence. Your concurrency issues will disappear.

1) create a sequence:

create sequence entry_no_sequence


2) set the new sequence's value to your table's
current entry_no value (n):

select setval('entry_no_sequence',n)


3) recreate your table so that the entry_no will get
it's value from calling nextval() on your new
sequence:

entry_no integer not null default
nextval('entry_no_sequence')


Thereafter, when an insert is made on your table, the
enry_no field will get its value from the sequence and
the sequence will be incremented. You would then drop
entro_no from your insert statement and it would
become something like:

INSERT INTO rcp_patient_visit_monitor (
patient_id, visit_date, is_newpatient, visit_type,
is_medical, is_review, 
is_labtest, is_scan, is_scopy, is_xray, weight,
height)
VALUES (?,current_timestamp,?,?,?,?,?,?,?,?,?,?)




__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com