• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2155
  • Last Modified:

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
0
peledc
Asked:
peledc
1 Solution
 
balasubramCommented:
hi,
I think u could try caching the sequence by specifying the cache key word while creating the sequence.
Cache is the number of sequence numbers u want to be cached.
Hope this helps
Bala
0
 
FBIAGENTCommented:
PELEDC,

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
0
 
WadhwaCommented:
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




0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now