Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-19318

Insert trends queries are not sorted, which can cause deadlocks

    XMLWordPrintable

Details

    • Team A
    • Sprint 76 (May 2021), Sprint 77 (Jun 2021)
    • 0.25

    Description

      Insert queries into trends table can sometimes fail with a deadlock:

      25043:20210428:015918.488 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  deadlock detected
      DETAIL:  Process 64086 waits for ShareLock on transaction 1912518228; blocked by process 64102.
      Process 64102 waits for ShareLock on transaction 1912495679; blocked by process 64086.
      HINT:  See server log for query details.
      CONTEXT:  while inserting index tuple (36946999,101) in relation "i1_trends_p2021_04"
       [insert into trends (itemid,clock,num,value_min,value_avg,value_max) values (25300881,1619582400,4,0.000000,0.000000,0.000000),(2664661,1619582400,5,0.000000,0.000000,0.000000),(31835071,1619582400,5,0.000000,0.000000,0.000000),(20766597,1619582400,4,0.000000,0.000000,0.000000),(20587203,1619582400,4,98.820694,98.820694,98.820694),(17528023,1619582400,4,0.000000,0.000000,0.000000),(20992374,1619582400,4,0.000000,0.000000,0.000000),(29180996,1619582400,4,0.000000,0.000000,0.000000),(21111788,1619582400,5,0.000000,0.000000,0.000000),(18183382,1619582400,4,0.000000,0.000000,0.000000),(23656545,1619582400,4,0.000000,0.000000,0.000000),(25000032,1619582400,5,0.000000,0.000000,0.000000),(26163401,1619582400,5,0.000000,0.000000,0.000000),(21594859,1619582400,5,4.621179,15.677315,24.924233),(19541546,1619582400,4,0.000000,0.453634,1.814535),(19751795,1619582400,5,0.000000,0.000000,0.000000),(16223181,1619582400,4,8.858829,8.863622,8.868184),(19475097,1619582400,4,0.000000,0.000000,0.000000),(19607067,1619582400,4,0.000000,0.000000,0.000000),(24146297,1619582400,4,3.655221,3.655308,3.655419),(16990830,1619582400,4,1695.992569,73434.150134,161794.398808),(19506243,1619582400,5,0.000000,0.000000,0.000000),(26163023,1619582400,5,0.033376,0.063944,0.083187),(18184272,1619582400,5,7.579884,8.683098,9.863351),(15232896,1619582400,4,0.000000,0.000000,0.000000),(25626422,1619582400,4,0.000000,0.008311,0.016628),(26306562,1619582400,5,158.856097,1747.038851,4315.396875),(20698230,1619582400,5,0.000000,0.000000,0.000000),(25794277,1619582400,5,0.000000,0.000000,0.000000),(23805503,1619582400,4,1.308577,1.396119,1.480964),(9493816,1619582400,5,39.564041,68.671044,126.365698),(30390798,1619582400,5,64.067229,64.067229,64.067229),(16862162,1619582400,4,0.147771,0.207477,0.314627),(20313151,1619582400,4,0.000000,0.000000,0.000000),(8052702,1619582400,4,92.053000,92.180770,92.263275),(19741978,1619582400,5,0.000000,0.000667,0.003335),(2907871,1619582400,5,48.051442,56.710195,62.516723),(19724236,1619582400,5,0.000000,1.373813,6.869063),(26251519,1619582400,4,0.000000,0.004133,0.016532),(24565321,1619582400,5,0.000000,0.000000,0.000000),(22794847,1619582400,4,3.626826,3.799740,4.264009),(15625697,1619582400,5,0.000000,0.000000,0.000000),(17283223,1619582400,4,2475.513265,3278.957892,4368.490126),(27770095,1619582400,4,0.000000,0.000000,0.000000),(23780786,1619582400,4,99.998627,99.998627,99.998627),(27193202,1619582400,5,0.000000,0.000000,0.000000),(23628305,1619582400,5,0.000000,0.000000,0.000000),(21166674,1619582400,4,0.000000,0.000000,0.000000),(18162474,1619582400,5,20.049346,21.769445,23.430802),(2365825,1619582400,4,166.796972,169.692245,170.979806),(18214000,1619582400,5,10.483366,10.483366,10.483366),(28567792,1619582400,4,348.823222,3236.853440,9403.366163),(29624173,1619582400,4,0.001109,0.001109,0.001109),(24898495,1619582400,4,0.000000,0.024730,0.065822),(6616742,1619582400,4,176353.187305,178007.582667,180199.785655),(29605644,1619582400,4,11.583007,11.693370,11.871544),(31834698,1619582400,4,0.000000,0.000000,0.000000),(25604896,1619582400,4,5.874587,8.274090,11.415827),(20049506,1619582400,5,0.000000,0.000000,0.000000),(21249583,1619582400,4,0.000000,0.000000,0.000000),(23041419,1619582400,5,0.000000,0.000000,0.000000),(32004377,1619582400,4,0.000000,0.000000,0.000000),(19312148,1619582400,5,2.690791,2.911833,3.127306),(26124601,1619582400,5,0.000000,0.000000,0.000000),(23137059,1619582400,4,87.347837,395.862038,772.429313),(2281906,1619582400,4,0.000000,0.000000,0.000000),(18103207,1619582400,4,57.570800,69.451902,85.502311),(23141849,1619582400,4,0.026779,0.027762,0.030713),(26357521,1619582400,5,2.676801,3.744030,4.886101),(3530533,1619582400,5,0.000000,0.000000,0.000000),(19500135,1619582400,5,14.198850,55.516848,99.967138),(6646819,1619582400,4,0.000000,0.000000,0.000000),(21811955,1619582400,5,1.135442,1.901808,2.930164),(23480188,1619582400,5,5.729264,70.099464,114.135500),(20559656,1619582400,5,0.064659,0.064659,0.064659),(27251530,1619582400,5,0.033014,0.161824,0.379663),(25062793,1619582400,4,0.000000,0.240003,0.640339),(10099485,1619582400,5,100.000000,100.000000,100.000000),(24333602,1619582400,5,99.110612,99.110612,99.110612),(12755968,1619582400,4,0.000000,0.000000,0.000000),(22988273,1619582400,4,0.000000,0.000000,0.000000),(19340319,1619582400,4,0.000000,0.004204,0.016818),(11758207,1619582400,5,0.265874,0.338563,0.412011),(22559026,1619582400,4,6.743493,6.743493,6.743493),(17998658,1619582400,5,97.813745,97.813745,97.813745),(15200063,1619582400,5,0.000000,0.096007,0.416662),(16051097,1619582400,4,93.282893,93.846385,94.448369),(22846270,1619582400,4,0.000000,0.000000,0.000000),(20027167,1619582400,4,0.000000,0.000000,0.000000),(29534777,1619582400,4,0.429227,0.448706,0.471998),(23988727,1619582400,5,0.000000,0.000000,0.000000),(20483145,1619582400,4,99.673126,99.673126,99.673126),(21802135,1619582400,5,0.000000,0.000000,0.000000),(12483136,1619582400,5,0.025926,0.073704,0.132963),(10065113,1619582400,4,98.948705,98.986518,99.000752),(14302596,1619582400,4,15112.069383,15237.723621,15435.984329),(26910251,1619582400,4,0.000000,0.000000,0.000000),(19351876,1619582400,5,69.819447,92.868972,99.469848),(16129458,1619582400,5,3.010413,4.277775,5.250532),(19471021,1619582400,4,0.000000,0.000000,0.000000),(16379446,1619582400,5,0.000000,0.000000,0.000000),(22364938,1619582400,4,0.000000,0.000000,0.000000),(12541857,1619582400,4,0.000000,0.000000,0.000000),(23480225,1619582400,5,0.000000,2.304985,10.217729),(22346218,1619582400,4,0.000000,0.000000,0.000000),(11073517,1619582400,4,9.419819,11.397984,13.938019),
      

      Possibly because of gap locks.

      Attachments

        Activity

          People

            dgoloscapov Dmitrijs Goloscapovs
            zux Edgars Melveris
            Votes:
            1 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: