Home » SQL & PL/SQL » SQL & PL/SQL » is "cursor for update " cause locking
is "cursor for update " cause locking [message #254734] Fri, 27 July 2007 14:15 Go to next message
RAY_HT
Messages: 153
Registered: May 2005
Location: Giza
Senior Member

dear all
i'm facing some thing i don't know the reasone
i made function to calculate customer invoice it's huge function
but i start it with cursor for update which get the invoice record to use some data and update on it .
it's woking well but
when it running the users of this system couldn't update on the customer data even for deferent customer that it's running on
when it finish they can update the customer data .
my question is :
is "cursor for update" cause database locking on all record that the cursor retrive or not.
if no why this locking happened

thanks
Re: is "cursor for update " cause locking [message #254738 is a reply to message #254734] Fri, 27 July 2007 14:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is "cursor for update" cause database locking on all record that the cursor retrive

Yes.
For instance, session 1:
SESSSION1> declare
  2    cursor cc is select nbr from t for update;
  3  begin
  4    open cc;
  5  end;
  6  /

PL/SQL procedure successfully completed.

session 2:
SESSSION2> update t set nbr=10 where nbr=1;

blocked until:
SESSSION1> rollback;

Rollback complete.

Then:
1 row updated.

SESSION2> 

Session 1 didn't even fetch or update anything but all rows were locked.

Regards
Michel
Re: is "cursor for update " cause locking [message #254739 is a reply to message #254734] Fri, 27 July 2007 14:58 Go to previous messageGo to next message
RAY_HT
Messages: 153
Registered: May 2005
Location: Giza
Senior Member

is that mean the next cursor cause locking for all record in all this tables ??????? and how prevent that

CURSOR bill_cur
IS
SELECT /*+ INDEX_DESC(C) */
q.ID, q.billingrun_id, q.file_no, q.meter_status_code,
q.previous_reading, q.current_reading, q.USAGE,q.no_of_month,
q.customer_account_id, q.usage_value, q.sanitary,
q.maintenance, q.revenue_stamp, q.receipt_stamp,
q.gov_stamp, q.contract_stamp, q.installment,
q.avg_usg, q.sanitary_status_code, q.add_val,
q.meter_max,q.no_of_units, q.no_of_room, q.deduct_val, q.total_due,
q.figures_in_words, c.national_cal, c.cal_value,
NVL (d.maintenance_charge, 0) maintenance_charge,
q.cal_method_code, q.meter_max_docno
FROM bl_billing q,
bl_d_area a,
bl_d_meter_status c,
bl_d_meter_size d,
bl_customer_account e
WHERE p_sec = q.section_code
AND p_branch = q.branch_code
AND a.code = q.area_code
and a.section_code = q.section_code
and a.branch_code = q.branch_code
AND p_groupid = a.GROUP_ID
AND a.code BETWEEN p_area_from AND p_area_to
AND q.file_no BETWEEN p_file_no_from AND p_file_no_to
AND q.large_customer_code IS NULL
AND c.code = q.meter_status_code
AND e.ID = q.customer_account_id
AND d.code(+) = e.meter_size_code
AND NOT EXISTS (
SELECT 'X'
FROM bl_d_meter_status f
WHERE 5 = f.national_cal
AND f.code = c.code)

FOR UPDATE OF q.usage_value,
q.sanitary,
q.maintenance,
q.installment,
q.amount_due,
q.total_due,
q.figures_in_words,
q.add_val,
q.deduct_val,
q.cal_method_code,
q.revenue_stamp,
q.receipt_stamp,
q.gov_stamp,
q.contract_stamp;
Re: is "cursor for update " cause locking [message #254744 is a reply to message #254739] Fri, 27 July 2007 15:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With the cursor you gave, you lock all the rows you select in bl_billing table.
WHy do you want to prevent for this.
If you select for update is this not to lock the rows?

Regards
Michel
Re: is "cursor for update " cause locking [message #254749 is a reply to message #254734] Fri, 27 July 2007 16:20 Go to previous messageGo to next message
RAY_HT
Messages: 153
Registered: May 2005
Location: Giza
Senior Member

so it should lock the selected row
means if i select 100 rows in this cursor from 10000 in bl_billing table it should lock the selected 100 rows only
but what's happen is locking for the record more than the 100 selcted rows , also there is locking in other tables than bl_billing table
Re: is "cursor for update " cause locking [message #254765 is a reply to message #254749] Sat, 28 July 2007 00:57 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you said you want to update only bl_billing table then it locks only the rows of this table.
It does not lock rows that are not selected.
If you don't think so, post a test case that we can reproduce to see what happens.

Regards
Michel
Previous Topic: Removing underscores
Next Topic: query between dates (was: anyone can resolve this query)
Goto Forum:
  


Current Time: Fri Dec 09 17:24:57 CST 2016

Total time taken to generate the page: 0.14576 seconds