mondbs_ora_32203.trc
|
Duplicate |
Team A
[ZBX-17080] Oracle Lock table maintenances in share mode Created: 2019 Dec 13 Updated: 2024 Apr 10 Resolved: 2020 Jan 14 |
|
| Status: | Closed |
| Project: | ZABBIX BUGS AND ISSUES |
| Component/s: | Server (S) |
| Affects Version/s: | 4.4.3 |
| Fix Version/s: | 4.0.17rc1, 4.4.5rc1, 5.0.0alpha1, 5.0 (plan) |
| Type: | Problem report | Priority: | Major |
| Reporter: | Dmitrijs Lamberts | Assignee: | Vladislavs Sokurenko |
| Resolution: | Fixed | Votes: | 1 |
| Labels: | dbsyncer, deadlock, oracle | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Attachments: |
mondbs_ora_32203.trc
|
||||
| Issue Links: |
|
||||
| Team: | Team A |
||||
| Sprint: | Sprint 60 (Jan 2020) | ||||
| Story Points: | 0.5 | ||||
| Description |
1665:20191210:172019.299 slow query: 290.653173 sec, "lock table maintenances in share mode" 1664:20191210:172019.299 slow query: 287.644316 sec, "lock table maintenances in share mode" 1662:20191210:172019.299 slow query: 293.398638 sec, "lock table maintenances in share mode" 1663:20191210:172019.299 slow query: 291.391157 sec, "lock table maintenances in share mode" 1664:20191210:172022.893 [Z3007] query failed: [-1] ORA-00060: deadlock detected while waiting for resource 1663:20191210:172023.019 [Z3007] query failed: [-1] ORA-00060: deadlock detected while waiting for resource 1665:20191210:172023.181 [Z3007] query failed: [-1] ORA-00060: deadlock detected while waiting for resource |
| Comments |
| Comment by Vladislavs Sokurenko [ 2019 Dec 18 ] |
|
Please provide Oracle log of deadlock, it should contain 2 queries that deadlocked. |
| Comment by Mikhail Grigorev [ 2019 Dec 20 ] |
|
------------Blocker(s)----------- ------------Waiter(s)------------ Resource Name process session holds waits serial process session holds waits serial TM-00013C65-00000000-00000000-00000000 90 116 S SSX 28191 98 119 S SSX 56184 TM-00013C65-00000000-00000000-00000000 98 119 S SSX 56184 90 116 S SSX 28191 ----- Information for waiting sessions ----- Session 116: sid: 116 ser: 28191 audsid: 24524027 user: 110/ZABBIX flags: (0x41) USR/- flags2: (0x40009) -/-/INC flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/- pid: 90 O/S info: user: oracle, term: UNKNOWN, ospid: 31496 image: [email protected] client details: O/S info: user: zabbix, term: pts/0, ospid: 31450 machine: dsat.db.infra.cloud.loc program: [email protected] (TNS V1-V3 application name: [email protected] (TNS V1-V3, hash value=3637101948 current SQL: insert into event_suppress (event_suppressid,eventid,maintenanceid,suppress_until) values (:1,:2,:3,:4)Session 119: sid: 119 ser: 56184 audsid: 24524042 user: 110/ZABBIX flags: (0x41) USR/- flags2: (0x40009) -/-/INC flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/- pid: 98 O/S info: user: oracle, term: UNKNOWN, ospid: 31534 image: [email protected] client details: O/S info: user: zabbix, term: pts/0, ospid: 31449 machine: dsat.db.infra.cloud.loc program: [email protected] (TNS V1-V3 application name: [email protected] (TNS V1-V3, hash value=3637101948 current SQL: insert into event_suppress (event_suppressid,eventid,maintenanceid,suppress_until) values (:1,:2,:3,:4) ----- End of information for waiting sessions ----- |
| Comment by Vladislavs Sokurenko [ 2019 Dec 20 ] |
|
Thank you, maybe there is additional information of "insert into event_suppress" during the deadlock ? For example what it tried to insert |
| Comment by Mikhail Grigorev [ 2019 Dec 23 ] |
|
I will try to enable tracing in Oracle, as well as trace log in zabbix-server, it will take me 1-2 days. Thank's. |
| Comment by Mikhail Grigorev [ 2019 Dec 24 ] |
|
Oracle trace file in attachment |
| Comment by Vladislavs Sokurenko [ 2020 Jan 06 ] |
|
Thank you, issue is reproduced! |
| Comment by Vladislavs Sokurenko [ 2020 Jan 07 ] |
|
Fixed in pull request feature/ZBX-17080-4.0 |
| Comment by Vladislavs Sokurenko [ 2020 Jan 09 ] |
|
Fixed in:
|
