| Title: | Oracle Rdb - Still a strategic database for DEC on Alpha AXP! |
| Notice: | RDB_60 is archived, please use RDB_70 .. |
| Moderator: | NOVA::SMITHI SON |
| Created: | Fri Mar 18 1994 |
| Last Modified: | Fri May 30 1997 |
| Last Successful Update: | Fri Jun 06 1997 |
| Number of topics: | 5118 |
| Total number of notes: | 28246 |
Hi,
My customer found R/W transaction and R/O transaction makes deadlock
status. Please see followings and advise me the reason why.
Also, if you need more information, please inform me.
(1) S/W : Alpha/OpenVMS V6.1-1H2
Oracle Rdb V6.0A-ECO 3
(2) Process whose ID is 20200505:2 executes Read/Write Shared/Write
transaction and 20200561:2 executes READ/ONLY transaction to 2
shared tables.
$ rms/sh statistic rdb_jongmok03
--------------------------------------------------------------------------------
Node: HSA DEC Rdb V6.0-13 Performance Monitor 24-FEB-1997 20:35:53
Page: 3.00 Seconds Lock Deadlock History Elapsed: 01:20:19.20
Page: 1 of 9 RDB_RAMJM03C:[RAMJM03]RDB_JONGMOK03.RDB;1 Mode: Online
--------------------------------------------------------------------------------
Process.ID Occurred... Lock.deadlock.reason.................... #Deadlock
20200505:2 20:29:43.39 - waiting for page 2:68 (PW) <<< R/W, S/W 23
202004FE:2 0
20200504:2 0
2020055D:2 20:35:17.83 - waiting for page 2:80 (PW) 6
202004FF:2 0
20200502:2 0
20200561:2 20:28:51.37 - waiting for page 3:680 (PR) <<< R/O 20
20200501:2 0
20200500:4 0
20200513:2 20:34:57.28 - waiting for page 4:14 (PW) 4
20200526:2 20:10:00.42 - waiting for page 6:629 (PR) 1
20200529:2 0
2020057B:2 20:27:20.57 - waiting for page 4:16 (PR) 6
20200517:2 0
20200525:2 0
--------------------------------------------------------------------------------
$
(3) Transaction flow of each process about 2 shared tables.
READ WRITE JEOBSU_TB (count) READ ONLY
+-----------+ |=========| +-----------+
|Process 1 | 1. set transaction | table 1 | 1. set transaction |Process 2 |
|(pid | (Attach 1) |(number | (Attach 2) |(pid |
| 20200505)| 2. SELECT->TABLE 1 |of record| 2. SELECT->TABLE 1 | 20200561)|
+-----------+ 3. UPDATE->TABLE 1 | is one.)| +-----------+
Loop 1 - 6 (CNT = CNT + 1)|=========| Loop 1 - 4
JEOBSU_HOGA_TB
(detail of JEOBSU_TB)
4. INSERT->TABLE 2 |==========|
(IDX:CNT + 1) | table 2 | 3. SELECT->TABLE 2
5. UPDATE->TABLE 2 |(number | 4. COMMIT
(IDX:CNT + 1) |of record |
6. COMMIT |is equal |
|to 'COUNT'|
|of table 1|
|==========|
(Attach 1)
EXEC SQL USING CONTEXT :CONTEXT SET TRANSACTION
ON GD USING (READ WRITE ISOLATION LEVEL REPEATABLE READ RESERVING
GD.GITA_TB FOR PROTECTED WRITE,
GD.JUMUN_GEOBU_TB FOR SHARED WRITE)
END-EXEC.
(Attach 2)
EXEC SQL SET TRANSACTION
ON GD USING (READ ONLY RESERVING
GD.GONGTONG_TB FOR SHARED READ,
GD.JONG_MASTER_TB FOR SHARED READ,
GD.JANG_TB FOR SHARED READ) AND
ON JM USING (READ ONLY RESERVING
JM.JONGMOK_TB FOR SHARED READ,
JM.HOGA_GUBUN_TB FOR SHARED READ,
JM.JEOBSU_TB FOR SHARED READ,
JM.JEOBSU_HOGA_TB FOR SHARED READ)
END-EXEC.
Best Regards,
Jong-Ho Lee.
| T.R | Title | User | Personal Name | Date | Lines |
|---|---|---|---|---|---|
| 5069.1 | HOTRDB::PMEAD | Paul, [email protected], 719-577-8032 | Tue Feb 25 1997 09:14 | 3 | |
What exact resource does Rdb say the deadlock occurs on?
Deadlocks are often a normal part of database activity.
| |||||
| 5069.2 | How can I find exact resource ? | ORAREP::DEKVC::JONGHOLEE | Tue Feb 25 1997 19:52 | 23 | |
Hi,
Thanks for your help.
My customer say that between READ_ONLY and READ_WRITE transaction,
the DEADLOCK occured.
We cannot understand this situation.
Due to this deadlock, the processing is delayed more than
10 seconds(system parameter deadlock wait time is 10 seconds).
The application can't know the DEADLOCK from SQLCODE.
This DEADLOCK occurs internally as Manual(Guide to Database Performance
and tuning v6.0 3.8.1.5) described.
>>>What exact resource does Rdb say the deadlock occurs on?
We can't know the 'exact resource', because the DEADLOCK occurs internally.
Please let me know how can I find the 'Exact resource'.
>>>Deadlocks are often a normal part of database activity.
My customer want to know why this DEADLOCK(Between R/O and R/W) occurs.
Though, lock mechanism is complex, please inform me the reason.
Thanks.
Jong-ho Lee.
| |||||
| 5069.3 | not enough informationi | NOVA::BRYDEN | Tue Feb 25 1997 22:16 | 15 | |
It is difficult to pick what has been happening. The information
on that screen says that the R/W process has had 23 deadlocks
since attaching the most recent one is physical area 2 page 68.
The R/O process has had 20 deadlocks since it attached, the most
recent occurred while obtaining page 680 physical area 3. If you
dump those pages it might help explain what is going on. eg maybe
there has been an insert done which has caused a rebalance of the
index tree and the two processes are locking each other out.
Tell us what is on the pages in the display and also get the
customer to do an RMU/SHOW LOCKS/MODE=BLOCKING when you see these
messages, maybe it is some other process that is causing the
deadlock..
Dave
| |||||
| 5069.4 | hotrdb.us.oracle.com::PMEAD | Paul, [email protected], 719-577-8032 | Wed Feb 26 1997 09:19 | 17 | |
Please refer to the SQL documentation to find out how to report more
error information than just the SQLCODE.
If the customer doesn't like the long 10 second pause then they might
want to consider reducing DEADLOCKWAIT to a smaller number. Of course
that can mean that there might be a slight increase in system overhead,
but in general it is not noticeable.
There are many strategies that can be used to reduce the likelihood of
deadlocks. For example, if one particular table is prone to deadlocks,
then you might want to consider having the application use exclusive
access to that table. That, however, reduces concurrency to the table.
You might want to make sure that each transaction accesses tables using
the same order (that is, always update table A prior to table B).
There are other ways to prevent deadlocks too. You need to determine
what is causing your deadlocks so that you can understand why they are
occurring. Then you can work on strategies to prevent them.
| |||||
| 5069.5 | What is on those pages? | bouvs.us.oracle.com::OAKEY | I'll take Clueless for $500, Alex | Wed Feb 26 1997 12:34 | 26 |
>> <<< Note 5069.2 by ORAREP::DEKVC::JONGHOLEE >>> >> -< How can I find exact resource ? >- Jong-ho, >> My customer say that between READ_ONLY and READ_WRITE transaction, >> the DEADLOCK occured. I see nothing in .0 that indicates that the deadlocks were between the identified READ ONLY and READ WRITE transactions. I see evidence that one user was seeing many deadlocks on one specific page while a different user was seeing deadlocks on a different page. I also get the impression (although I'm not sure), that the only place you are seeing the deadlocks is in the deadlock history screen, is that correct? Rdb can encounter deadlocks on pages which will not be signalled back to the application program. When the deadlock is signalled, Rdb handles the condition internally. What is on the two pages that keep encountering deadlocks? You may not be able to avoid page deadlocks completely. If the 10 second wait is a problem, reduce DEADLOCK_WAIT as Paul suggested. However, be aware that changing DEADLOCK_WAIT can impact the entire system and not just the database in question. | |||||