Home » SQL & PL/SQL » SQL & PL/SQL » Minimal row that is not locked
Minimal row that is not locked [message #217483] Fri, 02 February 2007 08:42 Go to next message
Buchas
Messages: 83
Registered: March 2006
Member
Hi,

I would like to know the minimal row in a table that is not locked for update.

I create a test table for illustration:

CREATE TABLE TMP1 AS SELECT 1 COLL FROM DUAL;
INSERT INTO TMP1 (COLL) VALUES (1);
INSERT INTO TMP1 (COLL) VALUES (2);
INSERT INTO TMP1 (COLL) VALUES (3);
INSERT INTO TMP1 (COLL) VALUES (4);
INSERT INTO TMP1 (COLL) VALUES (5);
COMMIT;

Then UserA executes:

SELECT * FROM APP_USER.TMP1 WHERE COLL = 2 FOR UPDATE;

Then UserB executes:

SELECT * FROM APP_USER.TMP1 WHERE COLL = 4 FOR UPDATE

Then UserC (me) wants to write an SQL query, that would return 1- the minimal number that is not locked. How can I do that?

Help please.

Thanks in advance!

[Updated on: Fri, 02 February 2007 08:44]

Report message to a moderator

Re: Minimal row that is not locked [message #217487 is a reply to message #217483] Fri, 02 February 2007 08:53 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


Very curious to know why you want to do it ?

Is COLL unique ?

Srini


Re: Minimal row that is not locked [message #217492 is a reply to message #217487] Fri, 02 February 2007 09:13 Go to previous messageGo to next message
Buchas
Messages: 83
Registered: March 2006
Member
No, COLL is not unique.

In buisiness situation the user needs to register a blank.
Blank's type + serie + number uniquely identify a blank.
According to the requirements, blanks numbers of specific type and serie should form a set containing no gaps (there can be little gaps at the end of sequence, those must be filled in later).
Once registered, blank is never deleted from the system.

I thought information about rows being locked would help me to create a sequence with no gaps using the row cashing.

Row cash- it is a set of rows, that are already inserted into the table and assigned to specific blank type+serie and have status 'system'.
Lets say some Oracle Job maintains the cash so that there are always some 'system' blanks to select from.
When the user needs to register a blank, he finds not locked 'system' blank with minimal number and locks it...

I think such mechanism would solve concurency problem that arises in no-gap-sequence creating problem.

Hoping I was clear enough Wink

[Updated on: Fri, 02 February 2007 09:14]

Report message to a moderator

Re: Minimal row that is not locked [message #217527 is a reply to message #217492] Fri, 02 February 2007 12:01 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Start with a look at this
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3225867788098

Then these...
http://www.quest-pipelines.com/pipelines/dba/tips06.htm#november

Search for "Which row is locked" here...
http://www.cheblogs.com/roller/page/nimzobenoni?catname=%2FOracle+Performance+Tuning
Previous Topic: Error/Message handling
Next Topic: performance issue
Goto Forum:
  


Current Time: Thu Dec 08 06:17:27 CST 2016

Total time taken to generate the page: 0.08098 seconds