-
Table Level Locking
I am running Oracle9.2 on Linux - can I specify locking at the table level ? If yes, then how and if not, then can I do it at the schema level(so all tables in the schema will acquire that locking mechanism)?
Thanks.
-
No idea what you are trying to do.
But a "alter table table_name enable table lock;" will lock the table.
-
We have an issue with Data contention and the problem is with dirty reads - normally to solve this, one would use SELECT...FOR UPDATE in the SQL to acquire a Lock while accessing the table. Instead of doing this, I was wondering if it is possible to specify the locking mechanism at the table definition or schema definition time so that any SELECT query against that table will automatically put the required lock on that table ?
-
Originally posted by rshivagami
the problem is with dirty reads
Oracle doesn't have dirty reads. You always get a consistant view of the data.
Introducing locking is a great way to limit your scalability and only allow one user to use the entire table at a time.
Jeff Hunter
-
Basically what is happening here is that there is a table XX and I read from a value from this table(lets say 100) and use that value as to insert into another table YY as a Primary key, increment the value to 101 in table xx, and then do a commit. Now after I have read from XX but before doing my commit, another instance of the appserver is reading from table xx(the same value 100) and trying to insert it into table YY and I am running into 'Primary Key Violated on Table YY' errors.
Hence I was wondering if I can put a lock on Table XX (the sole purpose of this table is to store values that will be used as Primary keys) as the code cannot be changed anymore...
Any ideas/suggestions will be greatly helpful.
Thanks.
-
Sounds like a great single user system.
Jeff Hunter
-
We acquired this table from someone else and Tabel XX is used like a sequence - Using oracle sequences was my original suggestion but that involves lot of code change, so I was wondering if it is possible from the DB end. Has anyone ever done anything like this before ?
-
You don't need to lock the table.
When the first session already acquired a lock on the row by executing the command, "select run_num from t10 for update;", the 2nd session will wait till the 1st session commits/rollbacks.
Pessimistic locking mechanism works fine in your case.
Tamil
-
Thanks or the reply Tamil. The thing is that we are not using a SELECT FOR UPDATE and the code cannot be changed anymore. The code is just using a SELECt statement and another UPDATE. So I am looking to achieve it at the database end.
-
Not possible at the DB level unless serializable is set to true;
Tamil
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|