首页 > 代码库 > [Oracle] enq: TX - row lock contention 优化案例

[Oracle] enq: TX - row lock contention 优化案例

根据开发反馈,最近每天早上7:30应用会报警,应用的日志显示数据库连接池满了,新的连接被拒绝。

首先,我做了ASH报告(报告区间:7:25 ~ 7:35),从ASH的等待事件发现enq: TX - row lock contention居然高达76.54%,如下所示:

Top User Events

    EventEvent Class% EventAvg Active Sessions
    enq: TX - row lock contentionApplication76.540.81
    CPU + Wait for CPUCPU12.760.14
    db file sequential readUser I/O7.400.08
    enq: TX - row lock contention等待事件是一种行的等待事件,也就是说同一时刻有多个session请求修改同一行。

    下一步就是找这个等待事件主要由哪些SQL引起的:

    Top SQL with Top Events

      SQL IDPlanhashSampled # of Executions% ActivityEvent% EventTop Row Source% RwSrcSQL Text
      4rm17788qwxuy12726618535469.45enq: TX - row lock contention69.45UPDATE69.45update shift_case set expertId...
      1cqbcdr0ufyk61272661853105.20enq: TX - row lock contention5.20UPDATE5.20update shift_case set daySecti...
      1anu5c146v8d7127266185341.89enq: TX - row lock contention1.89UPDATE1.89update shift_case set daySecti...
      gbw4zk8jv0n0u2588599834101.57CPU + Wait for CPU0.79TABLE ACCESS - BY GLOBAL INDEX ROWID0.47select sc.scId, sc.estId, ct.c...
      dvmk92c1umc9790531702191.42CPU + Wait for CPU1.42CONNECT BY - NO FILTERING WITH START-WITH0.63select h.hospitaluuid id, h.pl...
      从上表可以得出,SQL_ID=4rm17788qwxuy的SQL语句是罪魁祸首,改SQL语句如下:

      4rm17788qwxuyupdate shift_case set expertId = :1 , shiftDate = :2 , daySection = :3 , rcLimit = :4 , orderingCount = :5 , shareRccount = :6 , clinicTypeUuid = :7 , fee = :8 , isTimeDivision = :9 , state = :10 , isopen=:11 , stateTime = :12 , updateTime = sysdate where scId =:13
      scid是shift_case的主键,也就是说同一时刻有非常多的session在请求更新同一行。

      好了,既然已经定位到问题就好办了,马上把应用开发人员找来一问,真相大白:原来该应用需要从外部系统获取数据,为了让内部的数据库和外部的尽量保持一致,每次查询外部系统时,会在数据库里执行update语句。

      解决办法也简单:由于每次的Update都会把前一次的update覆盖(等于前面的update做的都是无用功),所以根本没必要每次查询都update,只要最后一次查询做update就可以了。