Home » SQL & PL/SQL » SQL & PL/SQL » SELECT...FOR UPDATE
SELECT...FOR UPDATE [message #260052] Fri, 17 August 2007 05:06 Go to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Hi,

Oracle 10.2.0.3.0 EE, single instance running on Solaris 10.

Question regarding locking and select for update:-

Table1_col1 Table1_col2
1 a
2 b
4 d

I want to be able to have multiple oracle sessions that are able to modify Table1_col2 values for Table1_col1 values in (1,2,3,4).
If a Table1_col1 value does not exist (such as '3') I want to be able to insert this into Table1 with values for col1,col2, and ensure that other oracle sessions wait for this to be commited to the db so that they would see that col1 = '3' exists.

I would like to achieve this without locking the complete table.

Is this possible? Ideas, views gratefully accepted. Thanks in advance,

Ken.

Re: SELECT...FOR UPDATE [message #260053 is a reply to message #260052] Fri, 17 August 2007 05:13 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
You would have to lock the whole table. Effectively you are saying there can be no updates while rows are being inserted into the table. The only way to ensure that would be to lock the entire table.
Re: SELECT...FOR UPDATE [message #260069 is a reply to message #260052] Fri, 17 August 2007 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create a unique constraint on col1.

Regards
Michel
Re: SELECT...FOR UPDATE [message #260073 is a reply to message #260052] Fri, 17 August 2007 06:19 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
MIchel,
Please explain.
Thanks in advance,
Ken.
Re: SELECT...FOR UPDATE [message #260082 is a reply to message #260073] Fri, 17 August 2007 06:39 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Search search search. Oracle documentation has all the information.
Re: SELECT...FOR UPDATE [message #260085 is a reply to message #260073] Fri, 17 August 2007 06:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain what?
If you have a unique constraint and insert 3 then anyone that wants to use 3 must wait you commit.

Regards
Michel
Re: SELECT...FOR UPDATE [message #260086 is a reply to message #260052] Fri, 17 August 2007 06:54 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Michel,

If session A inserts a row (col1=3) into a table and doesn't commit, then no other sessions with the correct privileges can access this row.
Once the row is committed then other sessions with the correct privileges can access this row.

Having a unique constraint has no use in this example. A unique constraint stops 'duplicate' values being inserted into a column.

Regards,

Ken.
Re: SELECT...FOR UPDATE [message #260087 is a reply to message #260052] Fri, 17 August 2007 06:55 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
S.Rajaram,
I have searched tahiti.oracle.com and metalink.com for info. Also I have googled.
Ken
Re: SELECT...FOR UPDATE [message #260089 is a reply to message #260087] Fri, 17 August 2007 07:04 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Are you sure? Check this out.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses002.htm#g1053592
Search for the word unique.
Re: SELECT...FOR UPDATE [message #260101 is a reply to message #260052] Fri, 17 August 2007 07:44 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
S.Rajaram,
Please re-read the thread.
Thanks,
Ken.
Re: SELECT...FOR UPDATE [message #260103 is a reply to message #260052] Fri, 17 August 2007 07:46 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
What exactly is the problem here ? Are you trying to stop gaps in the sequence numbers or prevent a number being used more than once ?

Re: SELECT...FOR UPDATE [message #260107 is a reply to message #260103] Fri, 17 August 2007 08:04 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

Table1_col1 Table1_col2
1 a
2 b
4 d

I want to be able to have multiple oracle sessions that are able to modify Table1_col2 values for Table1_col1 values in (1,2,3,4).
If a Table1_col1 value does not exist (such as '3') I want to be able to insert this into Table1 with values for col1,col2, and ensure that other oracle sessions wait for this to be commited to the db so that they would see that col1 = '3' exists.

Sorry mate you have lost me completely. All i can understand from the above paragraph is you don't want to lock the entire table but at the same time you don't want to insert duplicates in table1_col1. If not, Could you please tell me what you are trying to achieve ?

Regards

Raj
Re: SELECT...FOR UPDATE [message #260121 is a reply to message #260086] Fri, 17 August 2007 09:04 Go to previous message
Michel Cadot
Messages: 64136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
A unique constraint stops 'duplicate' values being inserted into a column.

Is this not what you want?
I quote you:
Quote:
If a Table1_col1 value does not exist (such as '3') I want to be able to insert this into Table1 with values for col1,col2, and ensure that other oracle sessions wait for this to be commited to the db so that they would see that col1 = '3' exists.

If this not what you want then you have to better explain what you want.

Regards
Michel
Previous Topic: Matrix query from a single table
Next Topic: User Defined errors
Goto Forum:
  


Current Time: Thu Dec 08 06:12:45 CST 2016

Total time taken to generate the page: 0.20300 seconds