Thank U
Abhijit Deb Roy, November 27, 2002 - 9:13 am UTC
Thanks a lot.....
Ur answer is really very helpful...
NOTE:
By the way, Can I get a soft copy of your book anywhere... (I don't know where to get your book that U mentioned....)
November 27, 2002 - 9:44 am UTC
(sure, I'll just put it up on a website so everyone could download it for free -- hmmm, think about it...)
Any bookstore would have it or be able to order it. A link to it (for the ISBN -- the code the bookstore would want) is on my home page.
There is an indian reprint out there.
That heavy thing....
Robert, November 27, 2002 - 10:14 am UTC
>>...a soft copy of your book anywhere
...been wishing for something like that too, boy would that be great for searching & lookup stuff !
It's on my Christmas wish list so I can keep a copy at work.
Tom, you understand your book is like an encyclopedia to us developers, right ?
November 27, 2002 - 10:50 am UTC
and I didn't organize it alphabetically or anything ;)
"select for update" not always enough to prevent "lost update"
Freek D' Hooge, November 28, 2002 - 2:20 am UTC
Tom,
you say: "You use "for update" to prevent LOST UPDATES."
This is true for this application, because the check is after we have obtained a lock on the record. But in case of an application where a user alters a value on the screen, the user may not have seen the latest values for this record.
It was my understanding that when performing the "select for update", one would have to give the old values of the fields as well (at least the fields one can alter on the its screen)
Again, in this particular application, the "select for update" is enough to prevent that a user works with an 'old' credit limit.
greetings
Freek D
November 28, 2002 - 8:00 am UTC
Yes, if you have my book "expert one on one Oracle" -- i explain this in great detail. in a client/server style app -- you would:
select the data out, display to user
as user goes to type over a field -- before they actually change it you:
select * from T where <columns> = <values on screen> and primary key = value
for update nowait
if 0 rows then Error, already changed, you must requery before changing
if ora-54 then Error, row locked by another session, you must wait
if 1 row returned, then OK, you got it, update it as you want
online version of your book
Hrishy, November 28, 2002 - 2:36 am UTC
Hi Tom
what we meant by soft copy was like..can you ask wrox publishers to give a CD of the book..as the book is quite heavy with 1000 pages..and by the way even if you do manage to put up a online version even then there are ocasions when everything on good old paper always feels better..
Probably in the next edition can we hope to see a CD along with the online version of the book
November 28, 2002 - 8:01 am UTC
No CD, but they are planning on making it available online to people who bought the book.
putting on CD, it would take what about 5 seconds for someone to mount that on a webserver somewhere...
Toms Book
Andrew Gilfrin, November 28, 2002 - 4:30 am UTC
On the subject of Toms book I suggest anybody who hasnt got it buys it now. If your not convinced you need it you must must must read the first chapter which is avaliable from the link on this websites home page and then you will know why you must have this book.
I have used it many times already to show people at my company why things they have "known" for years are actually wrong. One of the great things Tom does both in the book and on this site is give detailed, working and accurate evidence to what he has said many other books just say "It works like this so you should use it" but Tom gives you the evidence and information as to why you use it.
5 minutes to mount the CD
hrishy, November 28, 2002 - 8:24 am UTC
Hi Tom
well i agree somebody might place the entire contents on a Cd on a webserver...i agree but what about asktom.oracle.com which is
a)much wider knowledge base then expert one to one..Tom you gotta understand one thing here...unknowingly you have become the "LINUS TROVALDS" of oracle. Distrubuting and diseeminating oracle knowledge...you gotta convince your publishers that by putting your book free /or sharing it online would only increase its sales not decrease it .It would serve as advertisng campaign and morever many of us would like not to let go of crispy old paper just because the web has made heavy in roads into our daily lives...
think about it :-D..well am i logical or i have gone a bit far ;-D
Nah, it wouldn't.
Jer Smith, November 28, 2002 - 4:09 pm UTC
We'd like to believe that putting things up for free online would increase sales of a book, but it doesn't. Ask the folks at Coriolis (where a lot of the books were available for free) or ask O'Reilly about their books that are also available for free (Linux Networking, Samba).
As a fact, though, it's pretty hard to put a book online (even for members-only) that can't be sucked down with wget, or barring that, Perl's LWP package. Not to say that it isn't possible, but it usually involves making it really annoying to read.
There are only about seven Oracle books outside the docs that are really worth buying, so my advice is: cough up the money and purchase them.
select for update , does the web change everything ?
Hrishy, January 06, 2003 - 12:56 am UTC
Hi Tom
The above solution of using select for update works well in a client server environment.But when we move to the web we are sometimes faced with this peculiar problem.
Application level users connect to the database as user JDBC there are around 50 application users .when two users want to update the same row in the table i suggested that we use select for update.so the other user will get a message saying that this row is being cuurently edited by other user would you like to open in read only mode.(this message would be displayed in the other users browser after trapping the appropriate oracle error).The problem here stems from the fact that this is web based environment..and the users who has locked the rows might actually (do somethin funny) kill the browser and go home and since the session time out value is set to 8hrs..the select for update would lock up the rows for 8hrs. and other users who want to work may never get a chance to update the work.So is there a workaround for this problem..I am not quite yet familiar with the web technology..so i hope my question made sense.I did not want to waste another thread on this question since it is related to the question posted above only the context is web instead of client server.
January 06, 2003 - 7:39 am UTC
so, set the session timeout to something slightly more reasonable like 10minutes?
search this site for
optimistic pessimistic locking
for other techniques.
Apps does it do that automatically
Hrishy, January 07, 2003 - 2:24 am UTC
Hi Tom
I was talkin to a fellow DBA and he mentioned that our developers do not have to think about pessimistic locking and optimistic locking if we use an App server like Jrun,Tom Cat or Oracle9ias..is it true..coz..i am surprised if it is so..then how come in one of the earlier post you suggested to use the following package owa_opt_lock when it is already built into the app server..or am i missing something.
And also in the same post you mention..that optimistic locking in stateless environment is achived through date and sessionid.My doubt is why sessionid and not just date ?
well both these threads are great source of knowledge..i am also eagerly lookin forward to your book..I dont mind paying a few dollars for that book.and wish you include a topic like this.."web changes everything"..coz i find this select for update an interesting concept to look to in stateful and stateless environments"
January 07, 2003 - 6:32 am UTC
this is why DBA's are responsible for backing up databases, not developing applications against them.
app servers run code.
app servers run good, well designed code.
app servers run garbage code.
it is up to the developers to write good, well designed code.
there may be some facility builtin a TOOL they use to generate code that runs in these app servers -- like when you use forms, forms instantly applies pessimistic locking concurrency control automagicially. But, if you don't use forms -- just write code, you are on your own.
Tom Cat for example is a servlet engine. It runs java servlets. these servlets just contain java code you write. you can write code that destroys the integrity the database, you can write code that works good. It is up to you and the app server does nothing to make you go one way or the other.
as for date+sessionid, i believe we were talking about timing out the locks "held" by a given session after some reasonable time -- say 10 minutes. If we just used sessionid, we cannot time it out. If we just use date, we cannot hold it for a session. hence sessionid and date (but I'm guessing, lots of text on this here page)
why DBA's are responsible for backing up databases
hrishy, January 07, 2003 - 7:10 am UTC
Hi Tom
really DBA's are responsible for backing up databases..LOL..
hope Oracle University people are listening..so ocp would have only one exam to go..backup n recovery hehe..just kidding..
Tom as usual that was a cool response..from your side..thanks for the explanation..well you are right would love to bother you for a while on this thread..for now we are going ahead with your idea to the development team..
well by the way..Hrishy wunders even though he himself is a DBA..why he always has a browser open at asktom ;-D.
Does locking table in exclusive mode "prevent" row locking?
Michel SALAIS, October 14, 2003 - 6:55 pm UTC
Thanks a lot for your wonderful site
Here is my questions concerning locking mecanisme :
1)When a transaction locks a table in exclusive mode and then it tries to modify rows from that same table, is it necessary to acquire locks on these rows and what does Oracle?
2)Is there a mean to test this?
October 14, 2003 - 7:03 pm UTC
1) row locks are attributes of data -- we do not have a big list of row locks anywhere, unlike most every other database. So, as you visit the row, we leave our fingerprint behind. So yes, the rows are "locked" but there is no overhead - no more then anything else. it is just "natural" for us.
2) sure, you could dump blocks, but -- it really isn't worth your time. row locks are NOT an expensive item to be conserved in Oracle -- we are not db2 or sqlserver.
what humor, having great time with this thread, thanx
Kevin Meade, October 14, 2003 - 8:33 pm UTC
Hi Tom, me and Trevor are sitting here watching long jobs run slow and are rolling over reading this thread. Great job. Keep it coming.
Kevin and Trevor.
Locking a Record
Arvind, October 15, 2003 - 3:52 am UTC
It's Ok that Oracle allows to read the record by another user. But Can I check before a user selects the record for Rad or update whether that record is in use by another or not so that I can grant the rights to the user for the same. And at the same time I can display a message to the User that the Particular is record is being used by X user.
October 15, 2003 - 7:55 am UTC
you can use select for update NOWAIT to see if someone else has the record locked, yes.
Locking a Record
Arvind, October 15, 2003 - 3:54 am UTC
It's Ok that Oracle allows to read the record by another user. But Can I check before a user selects the record for Read or update whether that record is in use by another or not so that I can grant the rights to the user for the same. And at the same time I can display a message to the User that the Particular record is being used by X user.
Remove Session locks
Prince, October 15, 2003 - 4:49 am UTC
Hi Tom,
Very interesting to read your discussion on locking. I have used for update clause in my program during selection of a record (for update of <column name>).
Then i will update the table to increase the serial number by 1. After this i give a commit.
Now,in the same session, i need to add another entry, if i try to select the table again, it goes to <no_data_found> exception part.
I am able to do the same in a different session. Can i give nowait clause too like,
for update of <column nam> nowait;
Please bring your online version of book early, since it is very bulky to carry places?
Thanks
Prince
October 15, 2003 - 7:58 am UTC
You use for update NOWAIT in a select, but not in an update.
I don't understand the "no data found" comment -- one would need a full example to comment.
My new book is "less bulky". There will not be an online version of the book -- slight issue with "if I can download it, why pay for it".
Record Locking
Arvind, October 17, 2003 - 5:04 am UTC
With refence to the below question and your answer
Earlier Query
It's Ok that Oracle allows to read the record by another user. But Can I check before a user selects the record for Read or update whether that record is in use by another or not so that I can grant the rights to the user for the same. And at the same time I can display a message to the User that the Particular is record is being used by X user.
Today's Query
But I am selecting a record from LOV. My requirement is that as soon as I select a record from LOV the system should check that whether some other user is using that record or not. If using then system should propmt for the message that record is already in use.
Thanks
October 17, 2003 - 10:04 am UTC
select for update NOWAIT that row.
if that returns the row, you got it, it is yours.
if that returns an error, you don't got it, it is someone elses.
if you say "but I don't want to lock it, just see if it is", I will respond "that would be less then useful -- it would be misleading to say the least"
Locking Record
Arvind, October 21, 2003 - 12:22 am UTC
Thanks Tom. It has solved my Problem.
behaviour is different
A reader, December 23, 2003 - 11:38 am UTC
Hi Tom
The scenario which i m facing is very similar to the one mentioned in the above question.
We have a jobdetails tables (not for dbms_jobs) something like this
we have a job_details table with the following columns
job_id status
====== ======
1 started
2 started
3 not started
4 not started
5 not started
our business rule is not to have more than 2 jobs at the same time
-- session1
select job_id from job_details where status='started' for update;
if we have 2 started we dont do anything
where as if it is less than 2 we go ahead with the update & commit (i.e update status for the next job to 'started')
now suppose another session has started at teh same time as session1 . session1 has locked the data but session2 is blocked by session1.
now session1 updates & commits. (lock released)
immediately session2 lock gets released but it sees only the same data which was visible to session1
it cannot see the data updated & commited by session1
Since we have a limitation of no.of jobs to be started at the same time to only 2.
the above condition does not work as the data updated by session1 is not visible to session 2 even with a commit.
is it because the select is pre-ordained
How i can get around with this
Sorry for asking the question through the review but it was a bit too urgent
December 23, 2003 - 12:12 pm UTC
serialize at a higher level.
get lock
check records
decide to proceed, or not
eg:
select null from dual for update; -- serialize HERE
select * from t where status = 'started'; -- not HERE
you could use dbms_lock instead of dual if you like (better to use dbms_lock or your own private table instead of dual)
Optimistic lock better for high load
Alex V, December 23, 2003 - 12:35 pm UTC
For medium-high load applications
(let say 10-100 TPS against iinternet catalog)
optimistic lock is MUCH better than pessimistic.
According to all study, 50-85% transactions are
left unfinished in internet, so even 10 min timeout
gives tons of abandoned locks.
Moreover, if FOR UPDATE is used , all client must
use the same way, so go and check EVERY select around!
Better alternitives are:
--SERIALIZABLE isolation level (but more work for database)
--timestamp/sessionid/version (more work for application, triggers can help a lot here)
Complete integrity is inforced from within DB!
So main idea: validate transaction in the end,
not in the beginning.
(I am sure Tom has this in his book)
December 23, 2003 - 5:06 pm UTC
in a stateless, 3 tier web environment -- i totally agree.
in a heads down, data entry, client server environment -- i heartily disagree.
and it has nothing to do with TPS really. It has everything to do with the environment. (our highest load TPS's - the TPC-C's -- all pessimistic locking. it would be slower the other way around. it is driven by the duration of the transaction more so then the volume)
10g -- you get an SCN with each row :) no need for timestamp/etc in the near future.
Resource usage by locks
A reader, December 28, 2003 - 10:14 pm UTC
You said
--x--
1) row locks are attributes of data -- we do not have a big list of row locks anywhere, unlike most every other database. So, as you visit the row, we leave
our fingerprint behind. So yes, the rows are "locked" but there is no overhead - no more then anything else. it is just "natural" for us.
2) sure, you could dump blocks, but -- it really isn't worth your time. row locks are NOT an expensive item to be conserved in Oracle -- we are not db2 or sqlserver.
--x--
This reminded me of a marketing "claim" made by a SQL Server/MS person recently. He said, and I quote loosely.
<quote>
If you want to delete all rows from a million-row table and do 'delete from table;', Oracle will grab a million row-level locks, one for each row. SQL Server is "smart" and realizes that you are in fact touching the full table so it just places a exclusive lock on the whole table.
Assuming Oracle row-level locks use 1 byte per lock, thats a million bytes of memory/disk (some resource usage) that is just unnecessary.
</quote>
I couldnt refute it but it didnt quite seem right to me. Got me thinking...what, if any, resources does a row-level lock, or any lock for that matter use in Oracle? If the answer is NONE, how are they managed?
December 29, 2003 - 10:19 am UTC
well, the first rejoinder back to the MS guy should be "well, if I were going to delete ALL of the rows -- I would certainly use truncate. and if I were not going to delete all of the rows -- I would certainly NOT want you to lock the table, I'd want the other rows to be there for others to see. And -- don't you find it quite a pain that when you do the delete in MS sqlserver -- basically no one can query that table -- the system sort of grinds to a screeching halt...... Oh, and if I'm deleting 1,000,000 rows out of 2,000,000 -- since you guys keep locks in RAM, and there isn't just a table lock -- what then, aren't you using gobs of memory, real memory (expensive)?"
but anyway.
1,000,000 bytes of disk costs what? 2, maybe 3 cents? but are we being penny wise, pound foolish as the old saying goes? we are talking about space significantly smaller then that copy protection scheme called "the registry" (oh wait, thats a feature, not a copy protection scheme isn't it....)
And that aside:
</code>
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96524/c03block.htm#2595 <code>
shows the oracle block structure, the row directory (which is allocated regardless -- they'll have an equivalent thing) is what we use. it is there regardless.
we would be using the same amount of on disk storage with or without our locking scheme really. The benefits of our scheme include
o unlimited numbers of row locks
o ability to provide non-blocking reads - reads do not block writes, writes do not block reads
o pretty darn good scalability
Update
Joy, April 03, 2005 - 1:24 am UTC
Based on a certain condition, I do an update from oralce forms like the below..
begin
Update emp
set comm=100
where detpno=10
and sal>500
commit;
end;
1.What will happen if another user has one of the rows where deptno=10 and sal >500 locked for update. In that case how I capture the informantion about that one particular row, and also go ahead with the upate of the remaining rows which are not locked.
2.What are the quries I need to have with me, to trouble shoot a locking issue in general.
Thanks
Joy
April 03, 2005 - 9:24 am UTC
1) that would be "not smart". Give me the real world business case where "update all rows not currently locked" is a good idea?
If you have one, I can tell you how to do it in Oracle, but I want to understand the reasoning, the business case here. For I cannot anticipate any.
2) </code>
http://asktom.oracle.com/pls/ask/search?p_string=v%24lock+%22is+blocking%22 <code>
Updating all unlocked rows
Ajay, April 03, 2005 - 5:00 pm UTC
<quote>
1) that would be "not smart". Give me the real world business case where
"update all rows not currently locked" is a good idea?
If you have one, I can tell you how to do it in Oracle, but I want to understand
the reasoning, the business case here. For I cannot anticipate any.
</quote>
This could be useful whenever records have to be picked up in real time from a transaction table, processed and loaded somewhere else. In a scheduled ETL process, for example, you might want to process, and mark as processed, all the unlocked records, and leave the locked records for the next run of the ETL process (by which time the records will probably be unlocked).
April 03, 2005 - 5:24 pm UTC
but who would have the other records locked and why?
It seems uncoordinated, wouldn't the processing processes be responsible for marking a record as "processed", why move a record that hasn't been processed yet? I mean, how do you know the "unlocked" records are good to go? unless the processing process marks them as such?
Still confused.
If you need a queue, we have those, this still sounds "not right" to me.
Joy
Joy, April 03, 2005 - 10:59 pm UTC
Please answer the first of my first question?
----------
What will happen if another user has one of the rows where deptno=10 and sal
>500 locked for update
----------
Will the whole update fail?, or will the update of that one particular row fail?
April 04, 2005 - 6:50 am UTC
it'll wait for it.
so, neither. it'll block and wait.
lock
sam, September 07, 2006 - 9:36 pm UTC
Tom:
I have seen a PB or VB code where they have SQL statement that looks like this
update table set col=val where key=pk and col1=old_value and col2=old_value and col3=old_value;
It seems they are doing that for pessimistic locking
1. Is not this inefficient because you have to read all the records in table (full scan) before you find it and update it.
2. Can't you use the last modification date for record to make sure no one did updates to it. But still you will be doing a full scan here unless you build an index on PK+modification date/time.
3. Is your method above a substitute to these methods where you select for update and then do the update? Would not this hit the record using an index instead of full scan
September 08, 2006 - 4:20 pm UTC
they are doing that for OPTIMISTIC locking!!!
1) false, "where key = pk", the optimizer will do an index unique scan and find the row straight away.
2) you can, but only if it exists and only if all applications promise to maintain it
3) select for update is PESSIMISTIC locking and works in a "stateful environment" only.
lock
sam, September 09, 2006 - 10:02 am UTC
Tom:
I thought you said when you have a query
select * from table where col1=p and col2=e and col3=c
the optimizer will do a full scan unless you build an index on these 3 columns. Is there an exception if one of the columns is a PK that it would disregard all the other ones?
2. Is it true to say that optimistic locking results in lost updates while pessimisitc does not? Since on web apps you can only do optimistic then you will always have a potential for lost updates (if user A is updating a record then user B came in and did a quick update and committed) while user A is still working on it).
September 09, 2006 - 12:26 pm UTC
where did I say that???????
that is absolutely not true at all. Please provide a reference to where I've said that - I'll either retract it, or make it more clear what I was trying to say.
2) no, both optimistic and pessimistic locking are used TO PREVENT lost updates.
they are both techniques to prevent lost updates.
lock
sam, September 10, 2006 - 11:42 am UTC
Tom:
see nov 10, 2005 and nov 12, 2005 comments. I always thought that when you have "where clause" it will check if there is an index on all these columns you are earching for first and if not it will do a full scan. It makes sense does not it?
</code>
http://asktom.oracle.com/pls/ask/f?p=4950:61:10710416635894919399::::P61_ID:291916138754#51375369376183 <code>
2. What I meant by lost updates is not really "lost". What i meant to say
in pessimisitc you will not have a user click on "update" and then spent time doing changes and then hit save and then system tell him "ooops some one changed record you cant save this". This is what I meant. It will tell him right away that record is available for read only.
In optimistic locking this situation can happen where 10 people are updating same record and only the first one who hits save will have his changes saved. Am i correct.
September 10, 2006 - 12:15 pm UTC
I said "an index on all three columns, in the order I described would be useful" when answering:
here stock_number <> '99999' and
stock_type in ('P','B') and withdrawn is not null
however, that does NOT preclude an index on just stock_type from being used!!!! No where did I say "otherwise a full scan will definitely result" or even indicate that it would be the case.
2) that is the difference between pessimistic and optimistic.
Pessimistic concurrency control - you lock the record before making changes, you take the state of mind that you want to lock and hold the lock on a row before spending any time working on it.
optimistic concurrency control - you just read the row, work on it, and are optimistic that when you go back to update it - it'll not have been changed, you are willing to risk that it was and your efforts were wasted.
lock
sam, September 10, 2006 - 9:52 pm UTC
Tom:
Let us say you have ont table with 1 million records and one PK. Now are you saying if I did the update
update table T set col1=value where key=PK, col1=value1, col2=value2, col3=value3
the optimizer will not search through 1 million records to find a match for the where clause? There is no other way unless you have ONE index on all the 4 columns?
2. It sounds to me the optimistic locking can create a nightmare for an online system with thousands of users doing simultaneous updates. People will get frustrated of the optimsim. Pessimistic is the way to do it with a fixed amount of time to allow for update. correct?
thank you,
September 11, 2006 - 9:47 am UTC
you just said something that entirely conflicts with itself.
a) the optimizer will not search through 1 million records to find a match for the
where clause? that is correct, the optimizer can use the index on the primary key (there will be an index with KEY on the leading edge) to locate this record
b) There is no other way unless you have ONE index on all the 4
columns? that is false, you need not have one index on all 4 columns
2) in a stateless web environment, you don't really have too much of a choice. You use optimistic concurrency control because you cannot maintain locks from page to page to page.
If you have thousands of users doing simultaneous updates to the same row - you have a problem there don't you? If you used pessmisitic locking, you would have massive "enqueue waits" (serialization). You just describe an application that by definition would never have thousands of users because no one would use it.
lock
A reader, September 11, 2006 - 12:37 pm UTC
1. OK, I assume this is how it works. Correct!
If the PK is part of the "Where clause" then the optimizer will locate the record using the PK regardless of how many columns you have in the where clause.
If the PK IS NOT parts of the "where clause" then the optimizer will do a full table scan UNLESS there is an index on all of the columns in the WHERE clause.
2. In your book, you seem to favor "pessimistic locking" with oracle and client/server systems. Here you are sort of neutral.
How do you base your decision on whether to use pessimistic or optimistic. Is it
for client/server, always use pessimistic
for web, use optimstic (no other choice)
September 11, 2006 - 1:48 pm UTC
1) the optimizer looks at the entire predicate, all that needs to be done, it looks at all available access methods (indexes, scans, cluster access, whatever) and finds the best approach to accessing the data.
primary key has nothing to do with it.
the existence of an index on some of the column used in the predicate would.
2) if I was writing a client server system - i would prefer to use pessimistic locking because it is so much easier. however, when was the last time I wrote a client server application.....
many many many years ago.
Can i set a time limit to lock a colunm in table
Mohan, November 10, 2006 - 5:11 pm UTC
Hi Tom,
If im locking a colunm in a table, can i lock that only for some specific time, Ex:- 600 sec..after that that column should be automatically realsed(rollback/commit), so that other user can use that record to update.
Example:-
Select QTY from ITEMS FOR UPDATE (600secs)
so after 5 mins this record is free to use for others.
Since in my Java application im locking the record in DB throw web browser. And the record is locked , i will wait for the uesr to commit or rollback.
What if the user closes the Browser, or left it idle. The record is locked in the DB.
I need some time to be set, so that irrespective of browser input, the record should be realsed.
OR Should i have to handle this releasing part in JAVA( from server side)
Thanks
Mohan
November 10, 2006 - 6:58 pm UTC
there is no time based "release me"
you could use resource profiles to limit "idle time" so that anyone that was idle for 600 seconds would be killed (releasing any locks they might have)
You should seriously reconsider your transactional logic here - house of straw built on sand next to an active volcano in earthquake country describes your current state of affairs.
In a three tier environment, it is doubtful you really want to keep locks (sessions) across pages, sort of defeats one of the goals (reduced resources). If a single user monopolizes a connection for that long - what is the point.
Read only one unlocked row
Mette, February 15, 2007 - 9:35 am UTC
Hi Tom
We have a developer who is going to do update one row at a time from mulitple java threads. (pls. dont ask me about the business logic behind, because I dont know why).
They want to read just one unlocked row from the table at a time - there is no specific other select criteria, as just give me the next in line.
How can they accomplish this?
They have tried with select ... where rownum < 2 for update skip locked - but the rownum is evaualted "before" the "skip locked" - and I dont want them to use an undocumented feature either .... and neither would you by the look of this thread :-)
Can you give me a hint?
I'm on Oracle 9.2.0.6 for this problem.
regards
Mette
February 16, 2007 - 10:48 am UTC
man, oh, man, oh, man.
this sounds so bad.
sigh....
ok, I hate doing this.....
ops$tkyte%ORA9IR2> create table t ( x int );
Table created.
ops$tkyte%ORA9IR2> insert into t select rownum from all_users;
35 rows created.
ops$tkyte%ORA9IR2> commit;
Commit complete.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace function get_a_row return t%rowtype
2 as
3 resource_busy exception;
4 pragma exception_init( resource_busy, -54 );
5 l_rec t%rowtype;
6 begin
7 for x in ( select rowid rid from t )
8 loop
9 begin
10 select * into l_rec from t where rowid = x.rid for update nowait;
11 return l_rec;
12 exception
13 when resource_busy then null;
14 end;
15 end loop;
16 return null;
17 end;
18 /
Function created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> declare
2 l_rec t%rowtype;
3 begin
4 l_rec := get_a_row;
5 dbms_output.put_line( 'i got and locked ' || l_rec.x );
6 end;
7 /
i got and locked 1
PL/SQL procedure successfully completed.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> declare
2 pragma autonomous_transaction;
3 l_rec t%rowtype;
4 begin
5 l_rec := get_a_row;
6 dbms_output.put_line( 'i got and locked ' || l_rec.x );
7 commit;
8 end;
9 /
i got and locked 2
PL/SQL procedure successfully completed.
rows locked by a given transaction
Yoav, January 20, 2009 - 2:17 pm UTC
Hi Tom,
version 10204.
I would like to get a list of all rows locked by a given transaction . is it possible ?
Thanks
Yoav
January 20, 2009 - 3:37 pm UTC
No, it is not, we do not maintain an external list of locks. That would imply a fixed number of possible locks in the system and lots and lots of processing to manage them.
We store the locks on the data itself. You can see what objects they have rows locked in, but there is no list of "here are the 1,000,000 locks this transaction is holding" anywhere.
Read unlocked rows
goiyala3, November 09, 2009 - 3:54 am UTC
Hi Tom
I want to process unprocessed records based upon column status with 'OPEN'. Same program will be run in 10 threads.
How can i ensure that one thread should not read the same record which is already read by another?
Multiple Processes
goiyala3, November 12, 2009 - 6:03 am UTC
Thanks for quick reply Tom.
I have tried the following procedure to run in 4 parallel threads.
declare
sesid number:=0;
sescnt number:=0;
cursor c1(mod1 number) is select * from t1 where object_id in
(select object_id from
(select object_id,rownum rnm from t1 where status='OPEN')
where mod(rnm,5)=mod1 )
for update of status skip locked;
begin
dbms_application_info.SET_MODULE('processt1','action');
select distinct(sid) into sesid from v$mystat;
select rnm into sescnt from (select sid,rownum rnm from
v$session where module='processt1') where sid=sesid;
for i in c1(sescnt)
loop
update t1 set status=sesid where current of c1;
end loop;
commit;
dbms_output.put_line(sescnt);
end;
I know you won't agree with this approach. I just need your comment on this. AQ would suitable for this type of programming.
And if two threads starts on same time then version will clash right ?
November 15, 2009 - 1:51 pm UTC
... I know you won't agree with this approach. I just need your comment on this. ...
hah, that is just asking for trouble, isn't it?
In looking at your code - since rownum assignment is NOT DETERMINISTIC across queries (I might get the number 5 assigned to a row you get the number 6 assigned to and so on), running this four times with four different mod numbers could result in overlapping sets easily.
Or in your case, with the skipped locked - rows that never get selected.
Bobo, November 22, 2009 - 8:29 pm UTC
Hi tom;
Suppose two transactions wanted to insert, will the data block get locked by the first one until it finished its insert?
November 23, 2009 - 4:15 pm UTC
no, we do not lock blocks, we lock rows.
many thousands of people can concurrently insert into a table simultaneously
the time an insert might block another insert would be due to a primary/unique constraint.
If we have t( x int primary key )
and I insert into t values ( 1 );
and you try to do the same, you will block
until I commit or rollback - when your insert will either fail (duplicate value) or succeed( I rolled back)
Bobo, November 28, 2009 - 11:14 pm UTC
Thanks Tom;
By locking I mean latches, Is there any latch occur in this scenerio?
November 29, 2009 - 9:00 am UTC
in order to insert a row into a block, you have to get the block in "current mode".
At most one transaction has a block in current mode - and they hold it in current mode for an extremely short period of time.
If two transactions attempt to insert into the same block at precisely the same moment - one of them will wait on the other (buffer busy wait) to do it.
This is why you have things like multiple freelists in manual segment space management - so that multiple sessions can use different freelists to inserts at precisely the same millisecond - or ASSM (automatic segment space management) which spreads inserts out over many blocks to increase concurrency.
locking
A reader, November 29, 2009 - 1:00 pm UTC
Bobo
A reader, December 02, 2009 - 5:17 pm UTC
Thanks tom;
Is this scenerio(getting current version of the block) same in all transactions? (update,delete,insert)
December 04, 2009 - 1:11 pm UTC
if you want to modify data on a block, you have to do it on the current version of the block, yes.
we use a consistent read to find a row to update - and then we get that block in "current as of right now" mode to actually update it.
to insert a row on a block, you need the "current most up to date version" of that block
Changes in Locking Mechanism in Oracel 11g/12c
Abhijit Deb Roy, August 19, 2015 - 11:54 am UTC
Hi Tom,
Its been around 13 years now.
Just wanted to understand what is the difference in Locking Mechanism in 11gR2 and 12c databases?