Hi Riyaj
Thanx for the reply.
As it's a web system we'll probably be selecting 20 records at a time (ie.
the user sees 20 products of which he can book more than one at a time). So
I'd probably not try to select and test each individual record, purely for
performance considerations.
select * from t1 skip locked
----------------------------
I need to unfortunately know which records have been locked - > I need to
display them as well to the user, because he may want to wait to book them,
if the other user doesn't buy them ... this happens frequently.
Also, "skip locked" only works when you want to select something for update,
ie. select * from t1 FOR UPDATE skip locked. When the user is only browsing,
I don't want to lock anything.
Thanx for the responses.
Cheers
JL
-----Original Message-----
Sent: Tuesday, February 13, 2001 5:41 PM
To: Multiple recipients of list ORACLE-L
Hi Johan
Will this work ?
Person A books the product he wants with 'select * from t1 for
update'. At this point, he has lock on the row.
Person B comes in, looks at the product and does a 'select * from t1
for update nowait'
If the row is locked for update then person B will get ORA-0054. If
Person B gets an ORA-0054 then, you know that the person A has booked the
product but not bought yet.
The locks hold by the person A will be cleaned by the pmon if the process
dies away.
But the above will only work if each of the web user gets its own database
connections. I don't know about your environment, but in most environment,
the connection to the database is shared among the web sessions. If that is
the case, then you may have to use the flags to track the state changes.
If you want to show only rows that are locked then you could use this
undocumented feature:
'select * from t1 skip locked'. This will skip all the rows that have
been locked.
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
"These are my opinions and does not bind my employer. Use at your risk"
"Johan
Locke@i-Comme To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
rce Services" cc:
<Johan.Locke Subject: RE: Off Topic: Row
Locking - Row Id
Sent by:
root@fatcity.
com
02/13/01
12:50 AM
Please
respond to
ORACLE-L
Thanks Riyaj
Unfortunately it doesn't solve my problem. It only helps if the transaction
is BLOCKING another transaction.
This is my requirement, maybe somebody has a good solution.
A product selection engine. There are a limited number of products, each
unique.
Person A comes in over the web (this important). Looks at the products and
"books" the product he wants. At this stage I just want to issue a "SELECT
FOR UPDATE" - without commiting. Person A goes through the payment
selection, and if succesfull, the product is marked as "bought" and the
transaction commited.
If during the process of payment authorisation for Person A, person B looks
through the products, Person B must see the product person A is buying as
"Booked - not yet bought".
Why don't I just set a flag in the row, commit it, do the payment and
commit
that?
----------------------------------------------------------------------------
------
If for some reason person A's web session terminates voluntarily or
involuntarily, I'd have to run a process in the background which cleans up
the flags. As this is a very processing intensive table, it slows down the
processing tremendously. Conversly, if I could use the user's web session
termination, which will terminate the database session, to make oracle
release the lock on that row it makes my life a lot easier.
About 90% of the people will access the site within a period of about an
1-2
hours (within which you're aiming to sell 54000 of the 60000 products)
Why did I want the rowid's?
---------------------------
I wanted to run a query something like:
SELECT PRODUCT,
decode(oracle_internal_lock,yes,'Booked','Available')
FROM PRODUCTS
where status != 'Bought'
To get an output like:
PRODUCT AVAILABLE
------- ---------
PROD A Booked
PROD B Available
PROD C Available
A background process killing flags that have timed out is not a viable
solution.
Additional Info:
----------------
OPS 8.1.6 on a Sun Cluster
Dynamo Appserver (4.5.0), JDK 1.2.1
Netscape Web Server
Ideas???
Regards
JL
-----Original Message-----
Sent: Monday, February 12, 2001 5:21 PM
To: Multiple recipients of list ORACLE-L
Hi
You could get the following columns from the v$session table and then
use dbms_rowid.rowid_create to construct the rowid:
ROW_WAIT_OBJ# NUMBER
ROW_WAIT_FILE# NUMBER
ROW_WAIT_BLOCK# NUMBER
ROW_WAIT_ROW# NUMBER
Session that is waiting will have this information in its v$session view
and the session holding will have -1 in the row_wait_obj#.
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- ---------------
-------------
92632 5 13
1
-1 0 0
0
Hope this helps!!
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
"This is my opinion and does not bind my employer. Use at your own risk"
"Johan
Locke@i-Comme To: Multiple recipients of
list
ORACLE-L <[EMAIL PROTECTED]>
rce Services" cc:
<Johan.Locke Subject: Off Topic: Row
Locking
- Row Id
Sent by:
root@fatcity.
com
02/11/01
11:05 PM
Please
respond to
ORACLE-L
Hi
Anybody have any idea where I can find the rowid of a row that is being
locked within a table?
Kind Regards
JL
-----Original Message-----
Sent: Monday, February 12, 2001 5:40 AM
To: Multiple recipients of list ORACLE-L
Hi
In my opinion, this is an ITL issue. When a process need an ITL and
have to wait for it , then it pseudo randomly selects a locked row (from
that block) and enqueues itself in to the waiters queue. But the
row_waited information in v$session will be null. In rare cases, it is
possible for the deadlock to occur if the ITL waiter holds the row that is
needed by the other process.
I would ask, what is the frequency of this deadlock ? Is this the first
occurrence ? If it is the first occurrence, then I would wait for the next
occurrence and then spend time and resource.
Hope this helps!!
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
"These are my opinions and does not bind my employer. Use at your risk"
elkinsl@flash
.net To: Multiple recipients of
list
ORACLE-L <[EMAIL PROTECTED]>
Sent by: cc:
root@fatcity. Subject: Deadlock
Interpretation Assistance Requested
com
02/10/01
07:00 PM
Please
respond to
ORACLE-L
Listers,
HP-UX 11.0, V7.3.4.3. Deadlock trace file snippet:
SELECT * FROM UNIT_STATISTICS WHERE UNIT_ID = :b1 AND MONTH = :b2 AND
YEAR = :b3 AND RANK_CODE = :b4 FOR UPDATE OF QUANTITY
<snip>
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)
------
---
Resource Name process session holds waits process session holds
waits
TX-00180008-000042d6 837 635 X 784 481
S
TX-00160010-00004412 784 481 X 837 635
X
Rows waited on:
Session 481: no row
Session 635: obj - rowid = 00000722 - 00000289.0033.0102
I've never really encountered all that many deadlocks before. The ones I
*have* seen in the past were the "classic" TX locks where user A has a row
locked that user B needs and vice versa and the mode requested was X. On
Friday, the DBA's sent me a trace file from a deadlock (with the info above
from that trace file) and asked me to investigate. The deadlocks they had
seen in the past were due to application coding issues, hence their tossing
this to the development side of the house.
After a lot of research on Metalink, the Steve Adams site
(http://www.ixora.com.au), and Usenet archive searches (www.deja.com), the
S
mode wait for session 481 (and no row) makes me think this isn't the
typical
application induced deadlock due to the way and order in which locks are
acquired.
There are 3 foreign keys on the table, and, each of them are indexed. There
is no bitmap index. PCT_FREE is 10 and PCT_USED is 40. I don't really know
all that much about how heavily DML is issued against the table. But, after
reading material on when the wait is in S mode, I wonder if this might be
an
ITL issue. From what I've read the past 2 days, there could be other
reasons
for the S mode wait, but, waits for Unique/PK enforcement, insufficient ITL
slots, and bitmap index were the most common reasons mentioned. Because the
statement reported was a SELECT FOR UPDATE, I've eliminated (correctly?)
the
check for uniqueness wait during inserts, and, with no bitmap index on the
table, that leaves the ITL slots as the main candidate.
What I need to do is determine if this is indeed an application coding
issue, or, if I need to kick this back to the DBA's and let them research
it. And I don't mean that in a finger pointing way. The DBA's and
developers
there work well together. From what I've read and learned so far, this
deadlock doesn't seem to be an application coding issue. I am thinking
about
saying that and asking them (if they haven't already) to open a TAR and
provide the trace file to Oracle Support.
If anyone has any comments or suggestions, I would appreciate hearing them
(because if this could still be due to an application coding issue, more
research needs to be done on the development and/or my side of the house).
Regards,
Larry G. Elkins
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johan Locke@i-Commerce Services
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johan Locke@i-Commerce Services
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johan Locke@i-Commerce Services
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Off Topic: Row Locking - Row Id
Johan [EMAIL PROTECTED] Services Wed, 14 Feb 2001 00:06:36 -0800
- Off Topic: Row Locking - Row Id Johan [EMAIL PROTECTED] Services
- Re: Off Topic: Row Locking - Row Id Saurabh Sharma
- Re: Off Topic: Row Locking - Row Id Riyaj_Shamsudeen
- RE: Off Topic: Row Locking - Row Id Johan [EMAIL PROTECTED] Services
- RE: Off Topic: Row Locking - Row Id Johan [EMAIL PROTECTED] Services
- RE: Off Topic: Row Locking - Row Id Riyaj_Shamsudeen
- RE: Off Topic: Row Locking - Row Id jkstill
- RE: Off Topic: Row Locking - Row Id Johan [EMAIL PROTECTED] Services
- RE: Off Topic: Row Locking - Row Id Johan [EMAIL PROTECTED] Services
- RE: Off Topic: Row Locking - Row Id jkstill
- Re: Off Topic: Row Locking - Row Id Thater, William
- Re: Off Topic: Row Locking - Row Id jkstill
- RE: Off Topic: Row Locking - Row Id Paul Baumgartel
- RE: Off Topic: Row Locking - Row Id jkstill
- RE: Off Topic: Row Locking - Row Id Rachel Carmichael
- RE: Off Topic: Row Locking - Row Id Rachel Carmichael
- RE: Off Topic: Row Locking - Row Id Mark Leith
- RE: Off Topic: Row Locking - Row Id jkstill
- RE: Off Topic: Row Locking - Row Id jkstill
