This allows us to lock a record without the need to change it. We will be using SQL Developer to interact with the database and to perform the queries for this tutorial. Locking the same record As usual, we will start by creating a simple dummy table, to hold our data.
Check the create example bellow. After that, we will insert some dummy records, so we have data to work on. We will insert three records and then commit the transaction. Now we will open a new unshared worksheet, in order for us to test the SQL statements in independent transactions.
Check figure 1 on how to do it. You can read more about SQL Developer unshared worksheets in this previous post. Viewed 24k times. Improve this question. Add a comment. Active Oldest Votes. Improve this answer. Tony Andrews Tony Andrews k 20 20 gold badges silver badges bronze badges. Very good; not sure how I missed that. So I guess it just requires any arbitrary column name from the row I agree, it doesn't. I seem to recall having read somewhere long ago that the requirement to specify columns is so that in some future version perhaps Oracle might only lock particular columns in the row.
But my memory is hazy on that. It's a form of self-documenting code, I think: "I only intend to update the column salary " — Jeffrey Kemp. Sign up or log in Sign up using Google.
How could I improve on it? December 06, - am UTC. If you have an unindexed foreign key, this'll be an issue concurrency wise. Dynamic cursor using a db link ht, November 03, - pm UTC. Tom, Is it possible to pass a db link into a stored proc to return usernames in that instance? Hi TOM, I want to delete from one table which is taking too much time. Its primary key is used in many tables as foriegn key and all table are indexed. Please tell me how to reduce time.
November 11, - pm UTC. You don't tell me why it needs to go faster is it blocking something? If not, why do you care? Dear Tom, The query you posted in this link for finding the unindexed foreign keys is working fine in 10g but failing in 9i with the following error "ORA not a group by expression. December 14, - am UTC. HI Tom.
I have noted that the contents of the instead-of-trigger itself is not material to the loss of locking. As such the following trigger is sufficient to demonstrate the situation. Said another way, is it thus so that select for update will not acquire row locks when the select is issued against a view with instead of triggers, or have I made a mistake somewhere in my work?
January 04, - am UTC. I can't argue with your example. I'll check my test cases and see what I was doing wrong. Must have been a real stupid error on my part what other kind are there?
Thanks, this makes me feel way better. Tom, Your script above lists the foreign key columns even though they are part of a composite index. Isn't it suffice to just have the column part of some composite index instead of an index just for itself.
March 18, - pm UTC. March 26, - pm UTC. If you have two sessions that are both updating the same rows, and they haven't even read the data out, don't you also have a serious issue with lost updates? This is a transaction which calls itself recursively when it fails, and is in a deadlock state with another transaction which also calls itself recursively when it fails. That is, is there a limit when 2 such deadlocked procedures will give up?
Is this because of recursive calls or deadlocks? What I am seeing from the tests below is that On Oracle 10g the number of ORA errors recorded before these procedures abort completely with ORA is significantly less than the number of errors recorded in Oracle 11g.
For Oracle 11g with K ORA errors in the logs For Oracle 10g with 5K ORA errors in the logs What inspired me to investigate this, is failures I got in batch jobs after running the same recursive procedure in 10 different sessions simultaneously updating the same table kind of DIY parallelism which caused deadlocks.
Normally these 10 procedures, would complete the batch job and update the table even when producing deadlocks. When I look at the logs I see that they fail when they produce excessive amount of deadlocks? Is there a limit in Oracle on the amount of ORA errors that it could generate, before giving up?
Thank you very much for all your help. June 09, - am UTC. Where are your rollbacks? If you get the deadlock on the second update, the first update will have "worked" and won't have rolled back at all. You'll be doing the same exact work over and over and over again. I'm always confounded by this sort of a question - basically "an error happens, we know it is because of a design flaw in our application, but how can we make Oracle ignore this error so our flawed thing - which we admit is flawed - can run to completion a few hours or days from now" and recursion doesn't seem to be the way to go here for i in Potentially infinite recursion such as you have is bad because: o it is potentially infinite.
You have programmed an infinite loop without using a loop Your logic needs to be reworked as it quite simply has zero chance of working in real life.
Tom: Is this an internal bug or something with script? Any workarounds? February 15, - pm UTC. Chandu, June 15, - am UTC. Tom ,I'm a rookie in terms of posting if there is any mistake in my post that is not intentional. Below is my stored procedure which is call by an applivation. June 17, - pm UTC. Where A. The two records it gets will match the where clause - but they WON'T be the oldest records except by pure accident. I'm not surprised this gets deadlocks. Each process would get some random set of records and then start processing them - they are processing similar data - some of the same rows.
You don't think that the R2 and R1 cursors contain the same result set do you? Please say no Tell us, in english - not code that has some questionable things in it, exactly and precisely what the specification of this routine is to be. Then we can tell you how to do it. There can be multiple admins who can perform the task. Im sorry if this is not related to the current thread,, Thanx a lot.
July 05, - am UTC. It seems that every question similar to mine has to do with blocking. The user is trying to lock a specific row in a table. Thanks in advance for your help.
July 12, - am UTC. What do you think of a script that can find foreign keys across all users that could cause blocking locks? July 28, - pm UTC. I did not verify this query is correct - just that the concept is sound.
Hi Tom, So when do select for update nowait, it would throw exception if another session already acquired the row,but there is no predefined exception for error ORA resource busy and acquire with NOWAIT specified or timeout expired.
If we want to catch this error, we would have to check SQLCODE, but if later Oracle changes the code, our application will stop working, what suggestion do you have? August 30, - pm UTC. I would rank the chance of a code change very very very low on the probability scale, very very low - they don't really tend to change these due to the massive backwards compatibility issues.
If I have foreign key on one column col1 , and I have index on 3 columns col1, col2, col3 , query will not consider it as a problem. Should I have second index just on col1 if it is foreign key, or it is fine if I have index that include that column? September 15, - am UTC. Here is the case that I have. I have PK that consists of 5 columns. Each of FK is on only one column. C6 is not indexed at all. Only FK on c1 do not need index. September 16, - pm UTC. Your index on c1,c2,c3,c4,c5 already covers c1 - you don't need another index on c1.
I'm repeating myself now, I already wrote that yesterday!! Hi Tom, I was aware about "for update". I executed below query. How come it did not lock the records of table a in session2.
0コメント