peledc
asked on
lock wait on sequence
Oracle version 7.3.4
Sun Solaris 2.6 6 CPUs lots of memory
application type OLTP
concurent users: 400
One a day or more I get a lock wait on the following:
"select seq_name.nextval from dual;"
This happens when around 30 users are concurently requesting the seuence. The lock is for around 4 minutes.
There is also lots of buffer gets.
V$lock tables shows one row with blocking=1 but ID1 or ID2 are not to be found in OBJ$.
The sequence cash is 300.
Init.ora parameters:
sequence_cash_entries=3500
sequence_cash_hash_buckets =50
Has any one encountered this in the passed?
Any Id on a possible solution?
Thanks
Peled Alon
Sun Solaris 2.6 6 CPUs lots of memory
application type OLTP
concurent users: 400
One a day or more I get a lock wait on the following:
"select seq_name.nextval from dual;"
This happens when around 30 users are concurently requesting the seuence. The lock is for around 4 minutes.
There is also lots of buffer gets.
V$lock tables shows one row with blocking=1 but ID1 or ID2 are not to be found in OBJ$.
The sequence cash is 300.
Init.ora parameters:
sequence_cash_entries=3500
sequence_cash_hash_buckets
Has any one encountered this in the passed?
Any Id on a possible solution?
Thanks
Peled Alon
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I guess you are using seq_name.nextval with for update
for example
if session A issues command
select seq_name.nextval from dual for update;
session B
select seq_name.nextval from dual for update;
Session B will wait till the session A commits or rollback. Once session A issues commit or rollback session B gets the result.Session B is locked till then.
So do not use for update with the sequence at all. Otherwise you will face the same problem
Goodluck
Sam
for example
if session A issues command
select seq_name.nextval from dual for update;
session B
select seq_name.nextval from dual for update;
Session B will wait till the session A commits or rollback. Once session A issues commit or rollback session B gets the result.Session B is locked till then.
So do not use for update with the sequence at all. Otherwise you will face the same problem
Goodluck
Sam
I don't think the lock wait is on "select seq_name.nextval from dual;"
You must be looking at the wrong place. First of all, the statement is a SELECT statement; it only fetches the next sequence number; no lock is required.
It must have been some other SQL statement which utilizes the sequence.
I would search through the application for places (stored procedures/functions, PL/SQL blocks, etc.) which uses the sequence.
Good luck