Table/Record Locking Issues

[asingh]

When two people are logged into the same database and one person has executed a select query on one, many or all records of a table, the other user is unable to update any of the returned values.

This can also occur if the one person has two SQL windows connected to the same database and is attempting an update on a table in one window while having selected data in the other one.

Is there a way to avoid this problem or is it a feature of the application?

I am posting this on behalf of some users who will provide me a screen shot shortly.

I am not sure if this issue has been covered before in the forums.

 

[Marco Kalter]

This can only happen if you execute a "select for update" statement, which will implicitly lock all fetched records. A normal select statement will not lock the records.

Are you using a select ... for update statement?

 

[asingh]

Hi,

Unfortunately they are not using a SELECT ... FOR UPDATE statement.

Here is a description of 2 scenarios where the users have encountered this problem.

--------------------------------------------------

SCENARIO 1
1/ On one PL/sql developer sql window I was running this statement

select * from uabpymt where uabpymt_prem_code = '0470706'
and uabpymt_origin = 'RVI_UPD'
order by uabpymt_activity_date desc FOR UPDATE;

2/ On another window - was running
INSERT INTO interface.UZRRRPY (
UZRRRPY_PYMT_DATE ,
UZRRRPY_CUST_CODE ,
UZRRRPY_PREM_CODE ,
UZRRRPY_PYMT_SOURCE ,
UZRRRPY_REAS_CODE_RVSL ,
UZRRRPY_TENDER_TYPE ,
UZRRRPY_TENDER_AMOUNT ,
UZRRRPY_PYMT_REF ,
UZRRRPY_PYMT_TRANS_ID ,
UZRRRPY_RVSL_TRANS_ID ,
UZRRRPY_COMPANY_CODE ,
UZRRRPY_INVOICE_ID )
VALUES ('20122003',489474,'0470706','CSS','ACDD','DDEB',-54.00,'DDEB',
555555041,25454816,'01',58888308);

This is a trigger table that updates the table UABPYMT

SCENARIO 2

I was running the following select

select * from uzrpnls
where uzrpnls_coap_cust_code is null
and uzrpnls_end_date = to_date('31/12/2099','dd/mm/yyyy')
order by uzrpnls_prem_code

A user on another machine was trying to update uzrpnls and my select did not allow him to do this.

--------------------------------------------------

The users have noted that closing the select statement window has allowed things to function normally.

 

[Marco Kalter]

Scenario 1 does use a select for update. This causes the problem.

Perhaps scenario 2 does something similar?