Home » RDBMS Server » Performance Tuning » DeadLock in the database (Oracle 9i on Solaris 9)
| DeadLock in the database [message #313777] |
Mon, 14 April 2008 09:00  |
|
|
Hi,
We have around 8 OCI sessions in our applications to write to the oracle database.
We are pumping data at a rate of 200CPs after 10hrs we find a deadlock in the oracle database.
Need some suggestions like how can we avoid such dealocks
Please find the udump traces below:
/export/home/oracle/software/app/oracle/product/9.2.0/admin/cwbes/udump/cwbes_ora_27289.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /export/home/oracle/software/app/oracle/product/9.2.0
System name: SunOS
Node name: veritas7
Release: 5.10
Version: Generic_118833-36
Machine: sun4v
Instance name: cwbes
Redo thread mounted by this instance: 1
Oracle process number: 32
Unix process pid: 27289, image: oracle@veritas7 (TNS V1-V3)
*** 2008-04-12 00:39:45.572
*** SESSION ID:(30.2) 2008-04-12 00:39:45.570
DEADLOCK DETECTED ( ORA-00060 )
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00050002-0001aded 32 30 X 38 36 S
TX-00030002-00022808 38 36 X 32 30 S
session 30: DID 0001-0020-00000002 session 36: DID 0001-0026-00000002
session 36: DID 0001-0026-00000002 session 30: DID 0001-0020-00000002
Rows waited on:
Session 36: obj - rowid = 000084D8 - AAAJBUAAOAABWCpAAA
(dictionary objn - 34008, file - 14, block - 352425, slot - 0)
Session 30: obj - rowid = 000084D8 - AAAJBUAAOAABWCpAAA
(dictionary objn - 34008, file - 14, block - 352425, slot - 0)
Information on the OTHER waiting sessions:
Session 36:
pid=38 serial=2 audsid=15519 user: 78/UTSACCT
O/S info: user: cworks, term: , ospid: 27249, machine: veritas7
program: uts_as@veritas7 (TNS V1-V3)
application name: uts_as@veritas7 (TNS V1-V3), hash value=0
Current SQL Statement:
INSERT INTO RT_CDR1 VALUES(:1,:32774,:32798,:33024,:33025,:33026,:33027,:33028,:33029,:33030,:33031,:33032,:33033,:33034,:33035,:33036,:33037,:33038,:33
039,:33040,:33041,:33042,:33043,:33044,:33045,:33046,:33047,:33048,:33049,:33050,:33051,:33052,:33053,:33054,:33055,:33056,:33057,:33 058,:33059,:33060,:3
3061,:33062,:33063,:33064,:33065,:33066,:33067,:33554)
End of information on OTHER waiting sessions.
Current SQL statement for this session:
INSERT INTO RT_CDR1 VALUES(:1,:32774,:32798,:33024,:33025,:33026,:33027,:33028,:33029,:33030,:33031,:33032,:33033,:33034,:33035,:33036,:33037,:33038,:33
039,:33040,:33041,:33042,:33043,:33044,:33045,:33046,:33047,:33048,:33049,:33050,:33051,:33052,:33053,:33054,:33055,:33056,:33057,:33 058,:33059,:33060,:3
3061,:33062,:33063,:33064,:33065,:33066,:33067,:33554)
===================================================
PROCESS STATE
-------------
Process global information:
process: d71c69a8, call: d72163c4, xact: d7cae3ec, curses: d81dca2c, usrses: d81dca2c
----------------------------------------
SO: d71c69a8, type: 2, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=32, calls cur/top: d72163c4/d72163c4, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 50
last post received-location: kcbzww
last process to post me: d71c8028 1 0
last post sent: 0 0 50
last post sent-location: kcbzww
last process posted by me: d71c8028 1 0
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: d81d03a4
O/S info: user: oracle, term: UNKNOWN, ospid: 27289
OSD pid info: Unix process pid: 27289, image: oracle@veritas7 (TNS V1-V3)
----------------------------------------
SO: d7a8083c, type: 8, owner: d71c69a8, flag: INIT/-/-/0x00
(FOB) flags=2 fib ptr=d7b265ec incno=1 pending i/o cnt=0
----------------------------------------
SO: d7a7f0e8, type: 8, owner: d71c69a8, flag: INIT/-/-/0x00
(FOB) flags=2 fib ptr=d7b29298 incno=0 pending i/o cnt=0
----------------------------------------
SO: d7a7ea84, type: 8, owner: d71c69a8, flag: INIT/-/-/0x00
(FOB) flags=2 fib ptr=d7b26898 incno=0 pending i/o cnt=0
----------------------------------------
SO: d7a7d7e4, type: 8, owner: d71c69a8, flag: INIT/-/-/0x00
(FOB) flags=2 fib ptr=d7b28818 incno=0 pending i/o cnt=0
----------------------------------------
SO: d81dca2c, type: 4, owner: d71c69a8, flag: INIT/-/-/0x00
(session) trans: d7cae3ec, creator: d71c69a8, flag: (100041) USR/- BSY/-/-/-/-/-
DID: 0001-0020-00000002, short-term DID: 0000-0000-00000000
txn branch: 0
oct: 0, prv: 0, sql: dbb9ccfc, psql: 0, user: 78/UTSACCT
O/S info: user: cworks, term: , ospid: 27249, machine: veritas7
program: uts_as@veritas7 (TNS V1-V3)
application name: uts_as@veritas7 (TNS V1-V3), hash value=0
last wait for 'latch free' blocking sess=0xd81de67c seq=50283 wait_time=48213
address=d8273f08, number=11, tries=0
temporary object counter: 0
----------------------------------------
SO: d915339c, type: 52, owner: d81dca2c, flag: INIT/-/-/0x00
LIBRARY OBJECT PIN: pin=d915339c handle=dbb9baa4 mode=S lock=d915234c
user=d81dca2c session=d81dca2c count=1 mask=0041 savepoint=29189542 flags=[00]
----------------------------------------
SO: d7cae3ec, type: 38, owner: d81dca2c, flag: INIT/-/-/0x00
(trans) bsn = 29181235, flg = 0x1e03, flg2 = 0x00, prx = 0x0, ros = 2147483647, spn = 29189554
efd = 5
parent xid: 0x0000.000.00000000
env: (scn: 0x0000.1259e999 xid: 0x0005.002.0001aded uba: 0x00814d1e.1880.50 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0000.1
259ec84 0sch: scn: 0x0000.00000000)
cev: (spc = 478 arsp = d7cde184 ubk tsn: 1 rdba: 0x00814d1e useg tsn: 1 rdba: 0x00800049
hwm uba: 0x00814d1e.1880.50 col uba: 0x00000000.0000.00
num bl: 18 bk list: 0xd7c9fbd0)
(enqueue) TX-00050002-0001ADED DID: 0001-0020-00000002
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
res: d7231a88, mode: X, prv: d7231a90, sess: d81dca2c, proc: d71c69a8
xga: 0xdceab4a0, heap: UGA
----------------------------------------
SO: d7c9fbd0, type: 37, owner: d7cae3ec, flag: -/-/-/0x00
(List of Blocks) next index = 17
index savepoint itli buffer hint rdba
--------------------------------------------------
0 29189232 7 0x7bfe44dc 0x48104f6
1 29189255 7 0xa2fbd90c 0x4803ba1
2 29189275 8 0x99fe02c4 0x38560a7
3 29189278 6 0x92ff9768 0x4808032
4 29189301 9 0x88ff3d14 0x48019ab
5 29189324 5 0x5cfbb978 0x480bcb5
6 29189344 3 0x57ff9a58 0x38560a8
7 29189347 9 0x2efcac8c 0x48022eb
8 29189370 4 0x43feb538 0x4809498
9 29189393 6 0x70fc546c 0x4808b7a
10 29189416 6 0x52fc5bc4 0x480fdf2
11 29189439 2 0xaeff3098 0x480783b
12 29189459 5 0x6cfc79e0 0x38560a9
13 29189462 4 0x43fbc0d0 0x480947b
14 29189485 8 0x89fe76cc 0x4806856
15 29189508 5 0x68fff4ac 0x480a3ed
16 29189531 9 0x56fd5920 0x4807cc8
----------------------------------------
SO: da2dd3e4, type: 37, owner: d7cae3ec, flag: -/-/-/0x00
(List of Blocks) next index = 20
index savepoint itli buffer hint rdba
--------------------------------------------------
0 29188887 9 0x4cfb348c 0x48017bb
1 29188907 7 0x37fca244 0x38560a2
2 29188910 8 0xb5fb0938 0x48111bd
3 29188933 3 0x8ffbe410 0x4811781
4 29188956 8 0x9afa900c 0x4801f10
5 29188976 5 0x53ffd808 0x38560a3
6 29188979 5 0xa5ffc724 0x480ea10
7 29189002 4 0x56fb47a4 0x4803bdb
8 29189025 7 0xb6fabf0c 0x48053d2
9 29189045 4 0x91fa6c10 0x38560a4
10 29189048 8 0x3dff2fdc 0x4805b80
11 29189071 3 0x6dfe18cc 0x48025e4
12 29189094 4 0xaffb5e68 0x48028ee
13 29189114 2 0x70fe4a00 0x38560a5
14 29189117 4 0xb2fea800 0x480a581
15 29189140 6 0x9dfd1880 0x480ac89
16 29189163 8 0x2ffd454c 0x480d597
17 29189186 2 0xa7fb38f4 0x48070c5
18 29189206 5 0x89fc4500 0x38560a6
19 29189209 9 0xa7ffbcdc 0x480051b
----------------------------------------
SO: d7ca3d04, type: 37, owner: d7cae3ec, flag: -/-/-/0x00
(List of Blocks) next index = 20
index savepoint itli buffer hint rdba
--------------------------------------------------
0 29188519 2 0x68fcddc0 0x480ce6a
1 29188542 6 0x36fff3f0 0x4811762
2 29188565 8 0x6efd6c38 0x4809049
3 29188585 6 0x66fea2dc 0x385609e
4 29188588 4 0x33fae770 0x480b6b7
5 29188611 6 0x6cff794c 0x48036ee
6 29188634 8 0x6efc4d14 0x480418e
7 29188654 2 0x43fe6180 0x385609f
8 29188657 6 0x5cfba1f8 0x48012c0
9 29188680 2 0x93fdba10 0x4804ae3
10 29188703 4 0x4afe57f4 0x480f9da
11 29188726 7 0x8cfe2838 0x480c9bc
12 29188746 5 0x66fb6a28 0x38560a0
13 29188749 8 0x64fdf068 0x4811030
14 29188772 8 0x87fcb618 0x480c4b7
15 29188795 9 0xb3fc3070 0x480a2ae
16 29188815 3 0x86fedd9c 0x38560a1
17 29188818 9 0x54faa9c0 0x480460d
18 29188841 5 0x8dfbbaf0 0x480adae
19 29188864 4 0x53feba5c 0x480b6af
----------------------------------------
SO: d7c9cd00, type: 37, owner: d7cae3ec, flag: -/-/-/0x00
(List of Blocks) next index = 20
index savepoint itli buffer hint rdba
--------------------------------------------------
0 29188104 2 0x9efd659c 0x480318d
1 29188124 2 0x6dfbef14 0x3856099
2 29188127 6 0x93fb0068 0x480cb14
3 29188150 4 0x34fbca5c 0x4805e3e
4 29188173 6 0x31fe1520 0x48073d7
5 29188196 9 0x6dfa7078 0x4805f2e
6 29188216 6 0x82ffcb8c 0x385609a
7 29188254 8 0x5ffd8530 0x481146f
8 29188277 7 0x4ffa788c 0x480a26e
9 29188300 7 0x9efce2e4 0x480d1fa
10 29188320 5 0xacfed410 0x385609b
11 29188323 9 0x9eff53d8 0x4808b3e
12 29188346 9 0xb0fc4e8c 0x4806afe
13 29188404 4 0xb0ff2594 0x4805c48
14 29188424 2 0x8ffb2988 0x385609c
15 29188427 6 0x70faffac 0x48010d0
16 29188450 3 0x3cfb9928 0x48024f7
17 29188473 5 0x5bfede58 0x480c94e
18 29188493 2 0xabfb4d84 0x385609d
19 29188496 9 0x64fdfe5c 0x4805447
----------------------------------------
SO: d7ca70d8, type: 37, owner: d7cae3ec, flag: -/-/-/0x00
(List of Blocks) next index = 20
index savepoint itli buffer hint rdba
--------------------------------------------------
0 29187721 1 0x52fb1ffc 0x3856094
5 29187793 3 0x93fff624 0x48034a8
6 29187816 2 0xa0fc4dd0 0x4803dbc
7 29187839 2 0x47faea60 0x480b5ed
8 29187862 9 0x94fea744 0x4804dd7
9 29187882 1 0x97fd1b70 0x3856096
10 29187885 8 0x93ff72b0 0x480409e
11 29187908 4 0x67fd5d88 0x4811c14
12 29187931 4 0x68fe7554 0x480c68f
13 29187951 2 0x6ffb80ec 0x3856097
14 29187954 2 0x46ff6e48 0x4801049
15 29187977 8 0x4afadbb0 0x480c353
16 29188000 9 0x66feb9a0 0x48050a5
17 29188020 2 0x5cfa554c 0x3856098
18 29188023 3 0x41ffbf10 0x480fc49
19 29188046 10 0x73faf0fc 0x480de87
----------------------------------------
SO: d91deaec, type: 37, owner: d7cae3ec, flag: -/-/-/0x00
(List of Blocks) next index = 20
index savepoint itli buffer hint rdba
--------------------------------------------------
0 29185700 9 0x50fb3604 0x480da6c
1 29185723 2 0x5ffd6078 0x480411e
2 29185743 1 0x8bfeb828 0x3856090
3 29185746 3 0x6cffd8c4 0x480da53
4 29185804 8 0xb0fabe50 0x48033c6
5 29185827 9 0x85ff24d8 0x480a94a
6 29185847 2 0x55fbc8e4 0x3856091
7 29185850 3 0xb3fdacd8 0x480311c
8 29185873 4 0xbafd24fc 0x4809218
9 29185896 6 0x98fda6f8 0x4803868
10 29185919 7 0x37fbd61c 0x480505c
11 29185939 8 0x50fe1174 0x3856092
12 29185942 9 0xb8fd83b8 0x4806dc6
13 29186000 7 0x84fc4c58 0x48109c2
14 29186020 1 0x3ffd9034 0x3856093
15 29186023 5 0xadfb172c 0x4801544
16 29186187 5 0x86fe1a44 0x480a488
17 29186257 5 0xaafab1d4 0x4805753
18 29187678 7 0x52fd193c 0x480fc7b
19 29187701 8 0xaefc2ef8 0x4811988
----------------------------------------
SO: d91e0400, type: 37, owner: d7cae3ec, flag: -/-/-/0x00
(List of Blocks) next index = 20
index savepoint itli buffer hint rdba
--------------------------------------------------
0 29185355 9 0xa0fc8ac4 0x48090b5
1 29185375 4 0x54fc63d8 0x385608b
2 29185378 4 0x7effc89c 0x480336f
3 29185401 2 0x6aff09ac 0x480b436
4 29185424 9 0x9dfd6a04 0x480371e
5 29185444 4 0x56fcb6d4 0x385608c
6 29185447 8 0xadfbdb40 0x48106d9
7 29185470 7 0x40fd8820 0x4800d0d
8 29185493 6 0x4bfd7c60 0x480689b
9 29185513 3 0x73fcbe2c 0x385608d
10 29185516 6 0x40fb6f4c 0x480377b
11 29185539 8 0x87ff3500 0x480c8e1
12 29185562 4 0x97ff7db4 0x48045c5
13 29185585 2 0x47fb2464 0x480d4da
14 29185605 8 0xb9fd1708 0x385608e
15 29185608 7 0x45fde4a8 0x4803230
16 29185631 2 0x43fc5238 0x480c13d
17 29185654 9 0x9efb6a28 0x4806094
18 29185674 4 0xa8fa4a48 0x385608f
19 29185677 9 0x52ff9010 0x4800590
----------------------------------------
SO: da2dbc7c, type: 37, owner: d7cae3ec, flag: -/-/-/0x00
(List of Blocks) next index = 20
index savepoint itli buffer hint rdba
--------------------------------------------------
0 29184972 1 0x71fb0b6c 0x3856086
1 29184975 4 0x63fe7e24 0x480bb0d
2 29184998 2 0x9cfccf10 0x480d27c
3 29185021 6 0x2efd7f50 0x4801896
4 29185044 3 0x85fca478 0x48090ea
5 29185064 8 0x78fe1bbc 0x3856087
6 29185067 7 0xa7fd28a8 0x480d9c4
7 29185090 4 0x3dfd6ac0 0x480f421
8 29185113 14 0x73fde1b8 0x480fd93
9 29185133 6 0x7cfb3258 0x3856088
10 29185136 4 0x58fc82b0 0x480c5ad
11 29185159 6 0x5afc23f4 0x480bbc3
12 29185182 4 0x32fed120 0x480ae07
13 29185202 2 0x51feb828 0x3856089
14 29185205 7 0x58fc92d8 0x4800eb0
15 29185228 9 0x7efd2dcc 0x480205d
16 29185251 5 0x9cffe9a8 0x48057be
17 29185274 5 0x63fa8f50 0x480cc4f
18 29185294 6 0x80fcd958 0x385608a
19 29185332 5 0x5ffcc40c 0x480eea4
----------------------------------------
SO: da2dd8e8, type: 37, owner: d7cae3ec, flag: -/-/-/0x00
(List of Blocks) next index = 20
index savepoint itli buffer hint rdba
--------------------------------------------------
0 29184537 9 0x9ffa8c60 0x480679a
1 29184560 3 0xabff9e04 0x4801dba
2 29184583 7 0x84ff37f0 0x4803f26
3 29184603 3 0x98fb23a8 0x3856082
4 29184606 8 0x5afd9d6c 0x4811a1c
5 29184664 8 0x70fbd794 0x4809e10
6 29184687 8 0x9efe0208 0x480cbc3
7 29184707 8 0xa6fcac8c 0x3856083
8 29184710 9 0x94fa7714 0x480c1bf
9 29184733 6 0x73feee80 0x480a5ff
10 29184756 5 0x3ffa8f50 0x480f1c9
11 29184776 3 0x32fba7d8 0x3856084
12 29184814 2 0x3cfd25b8 0x480a08d
13 29184837 2 0x3efc4e8c 0x480d7e8
14 29184860 6 0x95fd659c 0x480ca2b
15 29184880 1 0x5afee66c 0x3856085
16 29184883 4 0x2bfc9ddc 0x48123f6
17 29184906 3 0xb3ff48d4 0x4804c6b
18 29184929 2 0x87fdf068 0x4812242
19 29184952 4 0x42fca300 0x480ae70
----------------------------------------
SO: d7ca2244, type: 37, owner: d7cae3ec, flag: -/-/-/0x00
(List of Blocks) next index = 20
index savepoint itli buffer hint rdba
--------------------------------------------------
0 29184157 3 0x58fe538c 0x48098f9
1 29184180 3 0x74faafa0 0x480adb3
2 29184203 2 0x54fc1254 0x4811dec
3 29184223 7 0x69fed298 0x385607d
4 29184226 6 0x8fff0834 0x4803fdf
5 29184249 6 0x8cfc4dd0 0x4805394
6 29184307 11 0x89fb1960 0x480aa37
7 29184327 7 0x55fe9894 0x385607e
8 29184330 3 0x96fdfda0 0x480e818
9 29184353 7 0x96fbdeec 0x480afaa
10 29184376 7 0x30fb97b0 0x480f24b
11 29184396 8 0x7bfb0938 0x385607f
12 29184399 5 0x3effce7c 0x48102a4
13 29184422 9 0x72fd6714 0x480efdc
14 29184445 3 0x90ffaa80 0x4800f95
15 29184465 8 0x2cfeb6b0 0x3856080
16 29184468 9 0x8dfeceec 0x48077ba
17 29184491 9 0x55feba5c 0x480d19e
18 29184514 2 0x99fb7470 0x4801edd
19 29184534 8 0xb6fedd9c 0x3856081
----------------------------------------
SO: d7ca9dfc, type: 37, owner: d7cae3ec, flag: -/-/-/0x00
(List of Blocks) next index = 20
index savepoint itli buffer hint rdba
--------------------------------------------------
0 29183774 1 0xb8fe15dc 0x3856078
1 29183777 4 0x30fb9f08 0x48074e1
2 29183800 2 0x44ff3444 0x480b761
3 29183858 2 0xa1fb3e18 0x4800bb3
4 29183881 7 0xb4febf80 0x4811c86
5 29183901 6 0x99fc81f4 0x3856079
6 29183904 8 0x4cffd518 0x480240b
7 29183927 5 0x38fec560 0x480c3b4
8 29183950 9 0x69fd49b4 0x48027e1
9 29183970 5 0x72fdf9f4 0x385607a
10 29183973 6 0xa4fd78b4 0x4804d07
11 29183996 5 0x41fccd98 0x48021cd
12 29184019 6 0x52fd1fd8 0x480a695
13 29184039 4 0x40fe94e8 0x385607b
14 29184042 13 0x39fbb688 0x480159a
15 29184065 4 0x2cfc312c 0x480c7af
16 29184088 5 0x88fd53fc 0x48024af
17 29184111 8 0x40fa5d60 0x480a313
18 29184131 3 0x89fd0c04 0x385607c
19 29184134 2 0xb1fbe700 0x4808182
----------------------------------------
SO: d7cab3b8, type: 37, owner: d7cae3ec, flag: -/-/-/0x00
(List of Blocks) next index = 20
index savepoint itli buffer hint rdba
0 29183409 5 0x3ffd17c4 0x480323d
1 29183432 4 0x58fe33f8 0x480a69f
2 29183455 5 0x50fb087c 0x480d862
3 29183475 6 0x33fbe410 0x3856074
4 29183478 7 0xa7ff736c 0x480dd52
5 29183501 8 0x69fc4a24 0x480b671
6 29183524 2 0x75fb957c 0x480ba6c
7 29183544 4 0x7bfd2964 0x3856075
8 29183547 6 0x63fb4a94 0x4801472
9 29183570 2 0x3bfa5f94 0x480e967
10 29183593 3 0xb8fec560 0x4806685
11 29183613 2 0x91faca10 0x3856076
12 29183616 6 0x9bfa9f78 0x4808a97
13 29183639 3 0x30facff0 0x48045d1
14 29183662 3 0x4dfdc5d0 0x480227a
15 29183685 8 0xa6fd70a0 0x480b2e2
16 29183705 5 0x38fa6340 0x3856077
17 29183708 8 0x5cffa908 0x4810fc1
18 29183731 5 0x77fc5f70 0x4802678
19 29183754 3 0xa8fab6f8 0x480ac69
----------------------------------------
SO: d7cada2c, type: 37, owner: d7cae3ec, flag: -/-/-/0x00
(List of Blocks) next index = 20
index savepoint itli buffer hint rdba
--------------------------------------------------
0 29183064 4 0xbafeead4 0x480b664
1 29183087 5 0x73fe2b28 0x4806f0c
2 29183107 5 0x43fa4178 0x385606f
3 29183110 8 0x37fe13a8 0x4802ba3
4 29183133 2 0x36fa7714 0x480c64f
5 29183156 5 0xb1ff2884 0x480f5d8
6 29183176 5 0xb2ffc4f0 0x3856070
7 29183179 9 0x64ff98e0 0x4806606
8 29183202 7 0x47faacb0 0x481001a
9 29183225 8 0x77fd1418 0x4810b6d
10 29183245 2 0x7efa66ec 0x3856071
11 29183248 7 0xa4fda7b4 0x48114e1
12 29183271 4 0xb2fb8d68 0x480c51a
13 29183294 3 0x92fc1254 0x4809c53
14 29183314 1 0x36fb2d34 0x3856072
15 29183317 2 0x88fec794 0x4803886
16 29183340 8 0xb4fac7dc 0x4803ad9
17 29183363 8 0xb7fb0124 0x4807ac2
18 29183386 3 0x5afd27ec 0x48048b9
19 29183406 6 0x93fdf87c 0x3856073
----------------------------------------
SO: d91e2a74, type: 37, owner: d7cae3ec, flag: -/-/-/0x00
(List of Blocks) next index = 20
index savepoint itli buffer hint rdba
--------------------------------------------------
0 29182716 1 0x82fb9114 0x385606a
1 29182719 2 0xb3fe538c 0x481143f
2 29182742 5 0x8bfedce0 0x480df50
3 29182765 8 0xa4fd3234 0x48086d6
4 29182788 7 0x78fab9e8 0x4811d17
5 29182808 4 0x90fd80c8 0x385606b
6 29182811 5 0x44ffbcdc 0x480fe6e
7 29182834 3 0x61fb62d0 0x4810cde
8 29182857 6 0x77ffb294 0x4807512
9 29182877 1 0x84fbf37c 0x385606c
10 29182880 3 0xa3fb4860 0x480b2b0
11 29182903 5 0x35fb781c 0x4811d6d
12 29182926 4 0xa3fa9530 0x480c0d6
13 29182949 3 0x36fc6edc 0x48117ac
14 29182969 8 0x87fc8a08 0x385606d
15 29182972 8 0x41fe6180 0x480886c
16 29182995 7 0x6dfe0a1c 0x48082f2
17 29183018 7 0x49ff9ec0 0x48032b7
18 29183038 6 0x56fdd5f8 0x385606e
19 29183041 2 0x7bfba4e8 0x48066db
----------------------------------------
SO: da2dd08c, type: 37, owner: d7cae3ec, flag: -/-/-/0x00
(List of Blocks) next index = 20
index savepoint itli buffer hint rdba
--------------------------------------------------
0 29182351 7 0x53fef460 0x48066c5
1 29182374 9 0x42fd135c 0x480fc65
2 29182394 1 0x9efdd480 0x3856066
3 29182397 2 0xadfc7e48 0x48015a7
4 29182420 6 0x58fdc68c 0x4802f3f
5 29182443 8 0x3bfd6c38 0x480615c
6 29182466 6 0x35fe2024 0x4802bab
7 29182486 6 0x83fbdcb8 0x3856067
8 29182489 7 0x4dfdcd28 0x48114a9
9 29182512 8 0xa7fa6574 0x480e87a
10 29182535 7 0x6dff8160 0x480cbb6
11 29181845 4 0xb3fd78b4 0x48099b7
12 29181868 2 0x5dfd3d38 0x480a0e1
13 29181888 5 0x41fdd308 0x385605f
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: DeadLock in the database [message #313877 is a reply to message #313807] |
Tue, 15 April 2008 00:08  |
|
|
Hi Michel,
You have mentioned that we can avoid deadlock by partition of the concurrent jobs by all the columns that are in all keys.
Wanted to know in details like how can we achieve the partition.
|
|
|
|
| Re: DeadLock in the database [message #313881 is a reply to message #313877] |
Tue, 15 April 2008 00:25 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
It is not a physical partition, it is a logical one.
Assume you have keys on 2 columns, cola and colb, that take values from 1 to 100 and 4 concurrent jobs, then each one will only hanlde (cola,colb) values in range:
1/ ([1,500],[1,500])
2/ ([1,500],[501,1000])
3/ ([501,1000],[1,500])
4/ ([501,1000],[501,1000])
Regards
Michel
|
|
|
|
| Re: DeadLock in the database [message #313954 is a reply to message #313809] |
Tue, 15 April 2008 06:08 |
|
|
Hi,
We have around 8 OCI sessions and if we do the logical partition as u mentioned that may affect our pereformance as our goal is to reach 500 CPS.
And u mentioned "when Oracle detects a deadlock, it terminates 1 on the processes involved so the other process can proceed" Can i confirm this if the oracle automatically breaks the deadlock?
|
|
|
|
| Re: DeadLock in the database [message #313958 is a reply to message #313881] |
Tue, 15 April 2008 06:11 |
|
|
Hi,
I have got some information as
ALTER TABLE table NOLOGGING;
ALTER TABLE table CACHE PARALLEL 15;
ALTER TABLE table DISABLE ROW MOVEMENT;
If we can use the above commands on the table we insert to prevent the deadlock. Can this help us?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: DeadLock in the database [message #314591 is a reply to message #314401] |
Thu, 17 April 2008 04:10 |
|
|
Hi,
In my application the OCIStmtExecute gets locked when a statement is execute.Please find the stack dump below.
current thread: t@4
[1] __lwp_park(0x0, 0x0, 0x0, 0x0, 0x1, 0x0), at 0xff365994
[2] mutex_lock_queue(0xff378b44, 0x0, 0x709660, 0xff378000, 0x0, 0x0), at 0xff36166c
[3] slow_lock(0x709660, 0xfe1f0600, 0x70a1b8, 0x1, 0xfc7d3afc, 0x4), at 0xff36206c
[4] kpuexec(0x64f554, 0x8ff600, 0x70a1b8, 0x1, 0x0, 0x0), at 0xfc9c0704
[5] OCIStmtExecute(0x64f554, 0x8ff600, 0x70a1b8, 0x1, 0x0, 0x0), at 0xfc96f434
Wanted to know what is the reason for lock and how can we avoid this.
|
|
|
|
| Re: DeadLock in the database [message #314631 is a reply to message #314591] |
Thu, 17 April 2008 05:51 |
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
|
Member |
|
|
deadlock detected while waiting for resource
When two sessions update/delete the same row, this condition arises. This is known as a deadlock.
To resolve this, rollback or commit from one session for the other session to continue work.
[Updated on: Thu, 17 April 2008 05:52] Report message to a moderator |
|
|
|
|
|
| Re: DeadLock in the database [message #314658 is a reply to message #314631] |
Thu, 17 April 2008 07:27 |
|
|
Hi,
When I collected the stack traces for the OCIStmtExecute I dinot receive any udump traces mentioning as deadlock. I don't think its a deadlock.
Can there be any situtation that the OCIStmtExecute() gets locked and is not returned.How can we resolve such situation.
|
|
|
|
|
|
| Re: DeadLock in the database [message #314699 is a reply to message #314631] |
Thu, 17 April 2008 09:20 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
| Quote: | deadlock detected while waiting for resource
When two sessions update/delete the same row, this condition arises. This is known as a deadlock.
|
No no no no no no no!
That is not a deadlock, and Oracle will not intervene in that case.
What you have described is simply resource contention, where two users both want to update the same row. The second user to update the row will have his session hang, waiting to aquire a lock on the row, until the first user has committed or rolled back his work.
A deadlock is more complex.
A deadlock is what would occur if you follow this sequence of steps:
1) user A locks row 1 in table X
2) user B locks row 33 in table X
3) user A tries to lock row 33 in table X - his session is now hanging, waiting for the lock
4) user B tries to lock row 1 in table X - his session is now hanging, waiting for the lock.
Neither user can do anything, and so both sessions will wait forever.
Or rather, one of the users will get his session killed and his changes rolled back inside 3 seconds by Oracle, in order to break the deadlock.
|
|
|
|
| Re: DeadLock in the database [message #316777 is a reply to message #314699] |
Mon, 28 April 2008 05:20 |
|
|
Hi All,
When we execute the insert statemnet using the OCIStmtExecute our application thread gets stopped at _libc_read and hence the entire application is hanged.
Can any one help for why the thread gets stopped at that point and how can we recover from the same.
Please find the dbx traces below when the application is hanged.
Reading -
Reading ld.so.1
Reading libsocket.so.1
Reading libnsl.so.1
Reading libthread.so.1
Reading libpthread.so.1
Reading libintl.so.1
Reading libgen.so.1
Reading librt.so.1
Reading libdl.so.1
Reading libresolv.so.2
Reading libosstoed.so
Reading libccxerces-c1_5_1d.so
Reading libACEd.so
Reading libbesmgd.so
Reading libdbintfd.so
Reading libcommonlogd.so
Reading libclientrequestintfd.so
Reading libinmemorydbd.so
Reading libarchon-c1_0.so
Reading libcagent1_0.so
Reading libclntsh.so.9.0
Reading libwtc9.so
Reading libsched.so.1
Reading libaio.so.1
Reading libkstat.so.1
Reading libm.so.1
Reading libCrun.so.1
Reading libw.so.1
Reading libc.so.1
Reading libmp.so.2
Reading libmd5.so.1
Reading libc_psr.so.1
Reading nss_files.so.1
Reading nss_nis.so.1
detected a multithreaded program
Attached to process 1921 with 45 LWPs
t@1 (l@1) stopped in _poll at 0xfdb9dfdc
0xfdb9dfdc: _poll+0x0004: ta 0x8
Current function is ACE_Reactor::run_event_loop
38 return r->run_reactor_event_loop (ACE_Reactor::check_reconfiguration);
(/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx)
(/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx) thread
current thread ($thread) is t@1
(/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx) threads
> t@1 a l@1 ?() running in _poll()
t@2 a l@2 ?() sleep on 0x4553b0 in __lwp_park()
t@3 a l@3 ?() sleep on 0x4574e8 in __lwp_park()
t@4 a l@4 ?() sleep on 0x700d68 in __lwp_park()
t@5 a l@5 ?() running in _so_accept()
t@6 a l@6 create_threaded_task() sleep on 0x49b1a0 in __lwp_park()
t@7 a l@7 create_threaded_task() sleep on 0x49b1a0 in __lwp_park()
t@8 a l@8 create_threaded_task() running in _so_accept()
t@9 a l@9 create_threaded_task() sleep on 0xfe334fa0 in __lwp_park()
t@10 a l@10 create_threaded_task() sleep on 0xfe334fa0 in __lwp_park()
t@11 a l@11 create_threaded_task() sleep on 0xfe335f88 in __lwp_park()
t@12 a l@12 create_threaded_task() running in _libc_nanosleep()
t@13 a l@13 ?() sleep on 0x4633a0 in __lwp_park()
t@14 a l@14 ?() running in _libc_read()
t@15 a l@15 ?() running in _libc_read()
t@16 a l@16 ?() running in _libc_read()
t@17 a l@17 ?() running in _libc_read()
t@18 a l@18 ?() sleep on 0x457af8 in __lwp_park()
t@19 a l@19 ?() running in _libc_read()
t@20 a l@20 ?() sleep on 0x457af8 in __lwp_park()
t@21 a l@21 ?() running in _libc_read()
t@22 a l@22 ?() running in _libc_read()
t@23 a l@23 ?() sleep on 0x457af8 in __lwp_park()
t@24 a l@24 ?() sleep on 0x457af8 in __lwp_park()
t@25 a l@25 ?() sleep on 0x457af8 in __lwp_park()
t@26 a l@26 ?() sleep on 0x457af8 in __lwp_park()
t@27 a l@27 ?() running in _libc_read()
t@28 a l@28 ?() running in _libc_read()
t@29 a l@29 ?() running in _poll()
t@30 a l@30 ?() running in _poll()
t@31 a l@31 ?() running in _poll()
t@32 a l@32 ?() running in _poll()
t@33 a l@33 ?() running in _poll()
t@34 a l@34 ?() running in _poll()
t@35 a l@35 ?() running in _poll()
t@36 a l@36 ?() running in _poll()
t@37 a l@37 ?() running in _poll()
t@38 a l@38 ?() running in _poll()
t@39 a l@39 ?() running in _poll()
t@40 a l@40 ?() running in _poll()
t@41 a l@41 ?() running in _poll()
t@42 a l@42 ?() running in _poll()
t@43 a l@43 ?() running in _poll()
t@44 a l@44 ?() running in _libc_nanosleep()
t@45 a l@45 create_threaded_task() sleep on 0x4698d0 in __lwp_park()
(/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx)
(/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx) thread t@4
Current function is COCIStatement::Execute
1048 status = OCIStmtExecute(m_Session.get_svc(), m_pstmth, m_Session.get_error(), (ub4) m_Rows, (ub4) 0,
t@4 (l@4) stopped in __lwp_park at 0xff365994
0xff365994: __lwp_park+0x0010: ta 0x8
(/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx) sync -info 0x700d68
0x00700d68 (0x700d68): thread mutex(locked)
Lock owned by t@28
Threads blocked by this lock are:
> t@4 (0xfe1f0600) a l@4 ?() sleep on 0x700d68 in __lwp_park()
(/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx) thread t@28
Current function is COCIStatement::Execute
1048 status = OCIStmtExecute(m_Session.get_svc(), m_pstmth, m_Session.get_error(), (ub4) m_Rows, (ub4) 0,
t@28 (l@28) stopped in _libc_read at 0xfdb9f24c
0xfdb9f24c: _libc_read+0x0008: ta 0x8
(/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx) where -l
current thread: t@28
[1] libc.so.1:_libc_read(0x23, 0x7bf6f6, 0x810, 0x0, 0x0, 0x0), at 0xfdb9f24c
[2] libthread.so.1:_ti_read(0x23, 0x7bf6f6, 0x810, 0x0, 0x0, 0x0), at 0xff35d7b4
[3] libclntsh.so.9.0:snttread(0x23, 0x7bf6f6, 0x810, 0x0, 0x0, 0x0), at 0xfccc8ae8
[4] libclntsh.so.9.0:nttrd(0x7b63d8, 0x7bf6f6, 0x7b685c, 0x0, 0x0, 0x0), at 0xfccc5a34
[5] libclntsh.so.9.0:nsprecv(0x0, 0xfd238d68, 0x7a8508, 0x0, 0x84d, 0x0), at 0xfcb4f07c
[6] libclntsh.so.9.0:nsrdr(0x0, 0x1, 0xfd238d68, 0x7b5f30, 0x1, 0x20), at 0xfcb5387c
[7] libclntsh.so.9.0:nsdo(0x7a8508, 0xfafe0b27, 0x4b96b8, 0x0, 0xfafe0b27, 0x0), at 0xfcb33dfc
[8] libclntsh.so.9.0:nioqrc(0x4b96b8, 0x0, 0x0, 0xc27, 0x1, 0x726000), at 0xfcb6bed0
[9] libclntsh.so.9.0:ttcdrv(0x728ae8, 0x728ae8, 0x0, 0x0, 0x0, 0x0), at 0xfcd06d74
[10] libclntsh.so.9.0:nioqwa(0x726b98, 0x0, 0xfcd0640c, 0x728704, 0x726b1c, 0x0), at 0xfcb75468
[11] libclntsh.so.9.0:upirtrc(0x7a7fd0, 0x0, 0x728638, 0x0, 0x0, 0x729b44), at 0xfc966f80
[12] libclntsh.so.9.0:kpurcsc(0x722874, 0x5e, 0x728638, 0x728704, 0x729b44, 0xfcd0962c), at 0xfca0f5d8
[13] libclntsh.so.9.0:kpuexecv8(0x72a344, 0x0, 0x728638, 0x0, 0x0, 0xfafe685a), at 0xfc9bf4f4
[14] libclntsh.so.9.0:kpuexec(0x722874, 0x725fb4, 0x726530, 0x1, 0x0, 0x0), at 0xfc9c18bc
[15] libclntsh.so.9.0:OCIStmtExecute(0x722874, 0x725fb4, 0x726530, 0x1, 0x0, 0x0), at 0xfc96f434
=>[16] libdbintfd.so:COCIStatement::Execute(this = 0x853258), line 1048 in "COCIStatement.cpp"
[17] libdbintfd.so:AcctRDMS_DB::DoWriteCDRDB(this = 0x69b2d0, pReq = 0x928cc0, pResp = 0x92b758), line 1007 in "AcctRDMS_DB.cpp"
[18] libdbintfd.so:AcctRDMS_DB::commonDBRequest(this = 0x69b2d0, pDbReq = 0x928cc0, pDbResp = 0x92b758), line 87 in "AcctRDMS_DB.cpp"
[19] libdbintfd.so:CommonDBClientAPI::dbRequest(this = 0x457ac0, dbHandle = 7U, pDbReq = 0x928cc0, pDbResp = 0x92b758), line 297 in "CommonDBClientAPI.cpp"
[20] -:AcctClientRequest::handleAcctWriteCDR(this = 0x457358, pReq = 0x928cc0, handle = 7U), line 1014 in "AcctClientRequest.cpp"
[21] -:AcctClientRequest::svc(this = 0x457358), line 1667 in "AcctClientRequest.cpp"
[22] libACEd.so:ACE_Task_Base::svc_run(0x457358, 0x1, 0x0, 0x0, 0x1c, 0x457358), at 0xfec91070
[23] libACEd.so:ACE_Thread_Adapter::invoke_i(0x609658, 0x4553e8, 0x609658, 0xfec90fc0, 0x457358, 0x0), at 0xfebb0fb4
[24] libACEd.so:ACE_Thread_Adapter::invoke(0x609658, 0xfe1f3634, 0x4553e8, 0x609658, 0x0, 0x4d3720), at 0xfebb0ea4
[25] libACEd.so:ace_thread_adapter(0x609658, 0x0, 0x0, 0x0, 0x0, 0x609658), at 0xfeb42d5c
(/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx)
|
|
|
|
| Re: DeadLock in the database [message #316794 is a reply to message #313787] |
Mon, 28 April 2008 06:37 |
|
|
Hi Michel,
You had mentioned that insertion in the order of the primary key can slove deadlock issue.
Please provide me more details how can we achieve this.
should we handle the insertion in a logical way in the code or how can we achieve insertion in the order of the primary key.
|
|
|
|
| Re: DeadLock in the database [message #316804 is a reply to message #316794] |
Mon, 28 April 2008 07:08 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you look at the example I posted1) user A locks row 1 in table X
2) user B locks row 33 in table Z
3) user A tries to lock row 33 in table Z - his session is now hanging, waiting for the lock gained by user B in step 2
4) user B tries to lock row 1 in table X - his session is now hanging, waiting for the lock gained by user A in step 1.
You'll see what Michel is saying.
The problem only occurs because the users are trying to get locks on the rows in a different order. If user B tried to get a lock on Table X first, his session would simply wait for user A to finish with the row, and would then run through smoothly.
|
|
|
|
| Re: DeadLock in the database [message #316805 is a reply to message #316804] |
Mon, 28 April 2008 07:12 |
|
|
Hi,
In our case we have around 15 sessions writing into the same table.
And when we receive any duplicate packets our sessions get hanged.
please refer the dbx traces in the previous mail.
|
|
|
|
| Re: DeadLock in the database [message #316807 is a reply to message #316805] |
Mon, 28 April 2008 07:18 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Sorry, I don't trawl through dump files for free.
I don't understand what you mean by 'And when we receive any duplicate packets our sessions get hanged.'
What packets? Tcp/Ip packets? UPS Packets? Crisps?
|
|
|
|
| Re: DeadLock in the database [message #316809 is a reply to message #316807] |
Mon, 28 April 2008 07:23 |
|
|
Hi,
We insert some data in the database. We commit the data only after some time say 4 seconds. suppose in that within 4 seconds if we try to insert the same data which is already inserted in the table oracle throws the duplicate pkt error and many a times oci session gets hanged.
|
|
|
|
|
|
| Re: DeadLock in the database [message #316819 is a reply to message #316814] |
Mon, 28 April 2008 08:05 |
|
|
Hi,
We are handling the deadlock in our code and even the database reoccurs but even if there is no deadlock we find the application gets hanged.
Please find the dbx traces below.
Our OCI session gets stopped at _lib_read when we try to insert using the OCIStmtExecute.
You can see the threads 14,15,16,17 28 get stopped at _libc_read
Reading -
Reading ld.so.1
Reading libsocket.so.1
Reading libnsl.so.1
Reading libthread.so.1
Reading libpthread.so.1
Reading libintl.so.1
Reading libgen.so.1
Reading librt.so.1
Reading libdl.so.1
Reading libresolv.so.2
Reading libosstoed.so
Reading libccxerces-c1_5_1d.so
Reading libACEd.so
Reading libbesmgd.so
Reading libdbintfd.so
Reading libcommonlogd.so
Reading libclientrequestintfd.so
Reading libinmemorydbd.so
Reading libarchon-c1_0.so
Reading libcagent1_0.so
Reading libclntsh.so.9.0
Reading libwtc9.so
Reading libsched.so.1
Reading libaio.so.1
Reading libkstat.so.1
Reading libm.so.1
Reading libCrun.so.1
Reading libw.so.1
Reading libc.so.1
Reading libmp.so.2
Reading libmd5.so.1
Reading libc_psr.so.1
Reading nss_files.so.1
Reading nss_nis.so.1
detected a multithreaded program
Attached to process 1921 with 45 LWPs
t@1 (l@1) stopped in _poll at 0xfdb9dfdc
0xfdb9dfdc: _poll+0x0004: ta 0x8
Current function is ACE_Reactor::run_event_loop
38 return r->run_reactor_event_loop (ACE_Reactor::check_reconfiguration);
(/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx)
(/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx) thread
current thread ($thread) is t@1
(/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx) threads
> t@1 a l@1 ?() running in _poll()
t@2 a l@2 ?() sleep on 0x4553b0 in __lwp_park()
t@3 a l@3 ?() sleep on 0x4574e8 in __lwp_park()
t@4 a l@4 ?() sleep on 0x700d68 in __lwp_park()
t@5 a l@5 ?() running in _so_accept()
t@6 a l@6 create_threaded_task() sleep on 0x49b1a0 in __lwp_park()
t@7 a l@7 create_threaded_task() sleep on 0x49b1a0 in __lwp_park()
t@8 a l@8 create_threaded_task() running in _so_accept()
t@9 a l@9 create_threaded_task() sleep on 0xfe334fa0 in __lwp_park()
t@10 a l@10 create_threaded_task() sleep on 0xfe334fa0 in __lwp_park()
t@11 a l@11 create_threaded_task() sleep on 0xfe335f88 in __lwp_park()
t@12 a l@12 create_threaded_task() running in _libc_nanosleep()
t@13 a l@13 ?() sleep on 0x4633a0 in __lwp_park()
t@14 a l@14 ?() running in _libc_read()
t@15 a l@15 ?() running in _libc_read()
t@16 a l@16 ?() running in _libc_read()
t@17 a l@17 ?() running in _libc_read()
t@18 a l@18 ?() sleep on 0x457af8 in __lwp_park()
t@19 a l@19 ?() running in _libc_read()
t@20 a l@20 ?() sleep on 0x457af8 in __lwp_park()
t@21 a l@21 ?() running in _libc_read()
t@22 a l@22 ?() running in _libc_read()
t@23 a l@23 ?() sleep on 0x457af8 in __lwp_park()
t@24 a l@24 ?() sleep on 0x457af8 in __lwp_park()
t@25 a l@25 ?() sleep on 0x457af8 in __lwp_park()
t@26 a l@26 ?() sleep on 0x457af8 in __lwp_park()
t@27 a l@27 ?() running in _libc_read()
t@28 a l@28 ?() running in _libc_read()
t@29 a l@29 ?() running in _poll()
t@30 a l@30 ?() running in _poll()
t@31 a l@31 ?() running in _poll()
t@32 a l@32 ?() running in _poll()
t@33 a l@33 ?() running in _poll()
t@34 a l@34 ?() running in _poll()
t@35 a l@35 ?() running in _poll()
t@36 a l@36 ?() running in _poll()
t@37 a l@37 ?() running in _poll()
t@38 a l@38 ?() running in _poll()
t@39 a l@39 ?() running in _poll()
t@40 a l@40 ?() running in _poll()
t@41 a l@41 ?() running in _poll()
t@42 a l@42 ?() running in _poll()
t@43 a l@43 ?() running in _poll()
t@44 a l@44 ?() running in _libc_nanosleep()
t@45 a l@45 create_threaded_task() sleep on 0x4698d0 in __lwp_park()
(/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx)
(/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx) thread t@4
Current function is COCIStatement::Execute
1048 status = OCIStmtExecute(m_Session.get_svc(), m_pstmth, m_Session.get_error(), (ub4) m_Rows, (ub4) 0,
t@4 (l@4) stopped in __lwp_park at 0xff365994
0xff365994: __lwp_park+0x0010: ta 0x8
(/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx) sync -info 0x700d68
0x00700d68 (0x700d68): thread mutex(locked)
Lock owned by t@28
Threads blocked by this lock are:
> t@4 (0xfe1f0600) a l@4 ?() sleep on 0x700d68 in __lwp_park()
(/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx) thread t@28
Current function is COCIStatement::Execute
1048 status = OCIStmtExecute(m_Session.get_svc(), m_pstmth, m_Session.get_error(), (ub4) m_Rows, (ub4) 0,
t@28 (l@28) stopped in _libc_read at 0xfdb9f24c
0xfdb9f24c: _libc_read+0x0008: ta 0x8
(/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx) where -l
current thread: t@28
[1] libc.so.1:_libc_read(0x23, 0x7bf6f6, 0x810, 0x0, 0x0, 0x0), at 0xfdb9f24c
[2] libthread.so.1:_ti_read(0x23, 0x7bf6f6, 0x810, 0x0, 0x0, 0x0), at 0xff35d7b4
[3] libclntsh.so.9.0:snttread(0x23, 0x7bf6f6, 0x810, 0x0, 0x0, 0x0), at 0xfccc8ae8
[4] libclntsh.so.9.0:nttrd(0x7b63d8, 0x7bf6f6, 0x7b685c, 0x0, 0x0, 0x0), at 0xfccc5a34
[5] libclntsh.so.9.0:nsprecv(0x0, 0xfd238d68, 0x7a8508, 0x0, 0x84d, 0x0), at 0xfcb4f07c
[6] libclntsh.so.9.0:nsrdr(0x0, 0x1, 0xfd238d68, 0x7b5f30, 0x1, 0x20), at 0xfcb5387c
[7] libclntsh.so.9.0:nsdo(0x7a8508, 0xfafe0b27, 0x4b96b8, 0x0, 0xfafe0b27, 0x0), at 0xfcb33dfc
[8] libclntsh.so.9.0:nioqrc(0x4b96b8, 0x0, 0x0, 0xc27, 0x1, 0x726000), at 0xfcb6bed0
[9] libclntsh.so.9.0:ttcdrv(0x728ae8, 0x728ae8, 0x0, 0x0, 0x0, 0x0), at 0xfcd06d74
[10] libclntsh.so.9.0:nioqwa(0x726b98, 0x0, 0xfcd0640c, 0x728704, 0x726b1c, 0x0), at 0xfcb75468
[11] libclntsh.so.9.0:upirtrc(0x7a7fd0, 0x0, 0x728638, 0x0, 0x0, 0x729b44), at 0xfc966f80
[12] libclntsh.so.9.0:kpurcsc(0x722874, 0x5e, 0x728638, 0x728704, 0x729b44, 0xfcd0962c), at 0xfca0f5d8
[13] libclntsh.so.9.0:kpuexecv8(0x72a344, 0x0, 0x728638, 0x0, 0x0, 0xfafe685a), at 0xfc9bf4f4
[14] libclntsh.so.9.0:kpuexec(0x722874, 0x725fb4, 0x726530, 0x1, 0x0, 0x0), at 0xfc9c18bc
[15] libclntsh.so.9.0:OCIStmtExecute(0x722874, 0x725fb4, 0x726530, 0x1, 0x0, 0x0), at 0xfc96f434
=>[16] libdbintfd.so:COCIStatement::Execute(this = 0x853258), line 1048 in "COCIStatement.cpp"
[17] libdbintfd.so:AcctRDMS_DB::DoWriteCDRDB(this = 0x69b2d0, pReq = 0x928cc0, pResp = 0x92b758), line 1007 in "AcctRDMS_DB.cpp"
[18] libdbintfd.so:AcctRDMS_DB::commonDBRequest(this = 0x69b2d0, pDbReq = 0x928cc0, pDbResp = 0x92b758), line 87 in "AcctRDMS_DB.cpp"
[19] libdbintfd.so:CommonDBClientAPI::dbRequest(this = 0x457ac0, dbHandle = 7U, pDbReq = 0x928cc0, pDbResp = 0x92b758), line 297 in "CommonDBClientAPI.cpp"
[20] -:AcctClientRequest::handleAcctWriteCDR(this = 0x457358, pReq = 0x928cc0, handle = 7U), line 1014 in "AcctClientRequest.cpp"
[21] -:AcctClientRequest::svc(this = 0x457358), line 1667 in "AcctClientRequest.cpp"
[22] libACEd.so:ACE_Task_Base::svc_run(0x457358, 0x1, 0x0, 0x0, 0x1c, 0x457358), at 0xfec91070
[23] libACEd.so:ACE_Thread_Adapter::invoke_i(0x609658, 0x4553e8, 0x609658, 0xfec90fc0, 0x457358, 0x0), at 0xfebb0fb4
[24] libACEd.so:ACE_Thread_Adapter::invoke(0x609658, 0xfe1f3634, 0x4553e8, 0x609658, 0x0, 0x4d3720), at 0xfebb0ea4
[25] libACEd.so:ace_thread_adapter(0x609658, 0x0, 0x0, 0x0, 0x0, 0x609658), at 0xfeb42d5c
(/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx)
|
|
|
|
| Re: DeadLock in the database [message #316835 is a reply to message #316819] |
Mon, 28 April 2008 09:04 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
So this isn't a deadlock issue after all, if the problem occurrs even where there aren't deadlocks.
I read that trace file differently to you - I see 14,15,16,17 running, lots of other threads sleeping - presumably waiting for a lock of some sort, and t@4 being blocked by t@28.
Is this a continually running process, or some sort of batched import.
If it's the latter, you could try removing the constraints from the table (which will speed things up) and add a de-dup stage to remove the duplicate rows.
|
|
|
|
| Re: DeadLock in the database [message #316837 is a reply to message #313777] |
Mon, 28 April 2008 09:08 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
/
If/when rows are returned, they identify the conflicted sessions
|
|
|
|
| Re: DeadLock in the database [message #316840 is a reply to message #316837] |
Mon, 28 April 2008 09:14 |
|
|
Hi,
I got the below output with above command:
SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2, lmode, request, type FROM V$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request
2 /
SESS ID1 ID2
------------------------------------------------ ---------- ----------
LMODE REQUEST TY
---------- ---------- --
Holder: 22 327713 111263
6 0 TX
Waiter: 27 327713 111263
0 4 TX
Waiter: 28 327713 111263
0 4 TX
SESS ID1 ID2
------------------------------------------------ ---------- ----------
LMODE REQUEST TY
---------- ---------- --
Waiter: 33 327713 111263
0 4 TX
Holder: 25 524333 108499
6 0 TX
Waiter: 22 524333 108499
0 4 TX
SESS ID1 ID2
------------------------------------------------ ---------- ----------
LMODE REQUEST TY
---------- ---------- --
Waiter: 32 524333 108499
0 4 TX
Waiter: 26 524333 108499
0 4 TX
Holder: 32 1048584 9211
6 0 TX
SESS ID1 ID2
------------------------------------------------ ---------- ----------
LMODE REQUEST TY
---------- ---------- --
Waiter: 24 1048584 9211
0 4 TX
Waiter: 34 1048584 9211
0 4 TX
Waiter: 37 1048584 9211
0 4 TX
12 rows selected.
SQL>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 01:22:39 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
