CODEWITHSUNDEEP
sqloracle-databasesequence
JOTN
JOTN

Reputation: 6317

Can you lock an Oracle sequence?

Is it possible to lock an Oracle sequence so any session trying to use nextval on it blocks until I'm done with my script?

I'll explain what I'm doing in case there's a different way. I'm preparing a schema to do bidirectional replication with streams. I want to make sure all the primary key sequences produce unique values. I do this by incrementing the sequence by 1 until the last digit is 1 and then change the increment to 100. On the other server I do the same until the last digit is 2. That way server 1 always produces primary keys XXXXX01 and server 2 XXXXX02.

The problem is this is a 24x7 database, and I can't stop all activity while I'm adjusting the sequences. If I can get an exclusive lock for a short time I can do it reliably.

Upvotes: 3

Views: 7841

Answers (5)

Florian_1990
Florian_1990

Reputation: 119

It seems you actually can lock a sequence.

SELECT SEQ_NAME.nextval FROM dual FOR UPDATE [NOWAIT];

Tested on Oracle 11 XE. Also have a look at https://www.experts-exchange.com/questions/20181432/lock-wait-on-sequence.html and https://geraldonit.com/oracle/database/oracle-database-locktypes/.

Upvotes: -1

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60272

If you just want to temporarily stop sessions from getting a value from the sequence, you could just revoke access to it. Unless, of course, your users are logging in as the owner of the sequence.

REVOKE SELECT ON mysequence FROM username;

Note: it's not a lock per se; instead of waiting, they'll get an Oracle error (invalid identifier, I think).

Upvotes: 1

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60272

I personally like the idea of using separate non-overlapping sequences as Ronnis has suggested.

Another option to consider is a composite primary key.

  1. If you're able to add a column to the data model, you add an identifier that is set differently depending on which server the process is running on.

  2. If you cannot add a column, you could instead change the column data type to VARCHAR2, and set it as a composite; e.g. on server 1, you would concatenate '1-' to the sequence value, e.g. '1-103450' would therefore not conflict with '2-103450' generated on server 2.

Either way, this doesn't suffer from any problems with adding more servers in the future.

Upvotes: 2

Ronnis
Ronnis

Reputation: 12833

The simplest possible solution (in my opinion) is to use odd numbers for server 1 and even numbers for server 2. This also removes the need for serialization.

create sequence server1_seq increment by 2 start with 1;
create sequence server2_seq increment by 2 start with 2;

This would produce a series of values like:

Server 1  Server 2
--------  --------
   1          2
   3          4
   5          6 
   7          8
   9         10

This solution can further be extended to handle more than two servers by using larger increments (and different starting values for each server).

Having said that, do NOT use this approach if you intend to add more servers over time. You would have to take all databases offline and rebuild the tables from scratch.

Upvotes: 3

user123664
user123664

Reputation:

No, you cannot lock a sequence. You could try to re-create the sequences in the required status. It will cause a problem for a short while.

Upvotes: 2

Related Questions