We have an application that updates a row in a table. Sometimes Oracle
does not respond for over 10 seconds. This is after an insert. It
seems that there is a lock somewhere. This is a multi user system.
Records can be added 3+ pr. second.
We have a procedure stored that does the actuall insert. Documented
below. As you can see the insert is pretty straight forward. (look at
the last section in the procedure).
What can cause this situation in Oracle;
* Table lock... ? How does Oracle lock tables when updating.
* ..
Do Oralce log whether or not a table has been locked and for how long
?
Thanx. Noddy
---------------
procedure add_user_conten tprovider( v_userid IN varchar,
v_contentprovid erid IN varchar,
v_account_numbe r IN varchar, v_email IN
varchar, v_firstname IN varchar, v_lastname IN
archar,
v_address_line1 IN varchar,
v_address_line2 IN varchar, v_city IN varchar,
v_postalcode IN varchar, v_country IN varchar,
v_phonenumber IN varchar, v_modifier
IN varchar, v_updatestatus OUT varchar)
is
t_status varchar(10):=nu ll;
t_login varchar(255) := null;
begin
/* is the user deleted */
deleted_user(v_ userid,t_status );
if t_status is not null then
v_updatestatus := t_status;
return;
end if;
provider_exists (v_contentprovi derid, t_status);
if t_status is not null then
v_updatestatus := t_status;
return;
end if;
select login_id into t_login
from customer_biller
where biller_login_id = v_contentprovid erid
and account_number = v_account_numbe r;
/* if t_login is not null the agreement exists */
if t_login is not null then
v_updatestatus := '23005';
return;
end if;
/* if not data found the agreement doesn't exist */
exception when no_data_found then
v_updatestatus := null;
/* everything is ok, insert the agreement */
insert into customer_biller
(login_id,bille r_login_id,acco unt_number,emai l,
first_name,last _name,phone,add ress_line_1,
address_line_2, city,zip_code,c ountry, status,
customer_biller _login_id, activation_date ,
request_date, confirm_date, CREATOR, MODIFIER,
PASSPHRASE)
values(v_userid ,v_contentprovi derid,v_account _number,
v_email,v_first name,v_lastname ,v_phonenumber,
v_address_line1 ,v_address_line 2,v_city,v_post alcode,v_countr y,
'PENDING', v_userid, DATE '1900-01-01',
DATE '1900-01-01', DATE '1900-01-01', v_modifier, v_modifier,
'PASSPHRASE');
end;
end;
does not respond for over 10 seconds. This is after an insert. It
seems that there is a lock somewhere. This is a multi user system.
Records can be added 3+ pr. second.
We have a procedure stored that does the actuall insert. Documented
below. As you can see the insert is pretty straight forward. (look at
the last section in the procedure).
What can cause this situation in Oracle;
* Table lock... ? How does Oracle lock tables when updating.
* ..
Do Oralce log whether or not a table has been locked and for how long
?
Thanx. Noddy
---------------
procedure add_user_conten tprovider( v_userid IN varchar,
v_contentprovid erid IN varchar,
v_account_numbe r IN varchar, v_email IN
varchar, v_firstname IN varchar, v_lastname IN
archar,
v_address_line1 IN varchar,
v_address_line2 IN varchar, v_city IN varchar,
v_postalcode IN varchar, v_country IN varchar,
v_phonenumber IN varchar, v_modifier
IN varchar, v_updatestatus OUT varchar)
is
t_status varchar(10):=nu ll;
t_login varchar(255) := null;
begin
/* is the user deleted */
deleted_user(v_ userid,t_status );
if t_status is not null then
v_updatestatus := t_status;
return;
end if;
provider_exists (v_contentprovi derid, t_status);
if t_status is not null then
v_updatestatus := t_status;
return;
end if;
select login_id into t_login
from customer_biller
where biller_login_id = v_contentprovid erid
and account_number = v_account_numbe r;
/* if t_login is not null the agreement exists */
if t_login is not null then
v_updatestatus := '23005';
return;
end if;
/* if not data found the agreement doesn't exist */
exception when no_data_found then
v_updatestatus := null;
/* everything is ok, insert the agreement */
insert into customer_biller
(login_id,bille r_login_id,acco unt_number,emai l,
first_name,last _name,phone,add ress_line_1,
address_line_2, city,zip_code,c ountry, status,
customer_biller _login_id, activation_date ,
request_date, confirm_date, CREATOR, MODIFIER,
PASSPHRASE)
values(v_userid ,v_contentprovi derid,v_account _number,
v_email,v_first name,v_lastname ,v_phonenumber,
v_address_line1 ,v_address_line 2,v_city,v_post alcode,v_countr y,
'PENDING', v_userid, DATE '1900-01-01',
DATE '1900-01-01', DATE '1900-01-01', v_modifier, v_modifier,
'PASSPHRASE');
end;
end;
Comment