php - INNODB Lock 有什么问题？

Question

我在处理大量更新、插入和删除请求的系统上工作。这就是为什么我选择 INNODB 作为我的存储引擎的原因是行锁。我们每 10 分钟更新 60.000 条记录。我们正在使用 Gearman 并行化我们在不同服务器上的工作。代码使用 PHP，我们使用 Zend Framework。

那么让我们从问题的描述开始。我们正在记录错误，几乎每 5 到 20 分钟就会发生一个错误。

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

简单地扩展“锁定等待超时”对我们没有帮助，因为我们试图尽可能快地更新。

为了获得更多信息，我完成了 SHOW INNODB STATUS\G。一件事让我问 - 怎么回事？

---TRANSACTION 0 18126657, ACTIVE 54 sec, process no 16154, 
OS thread id 47956454176512 fetching rows, thread declared inside InnoDB 68 mysql tables in use 1, 
locked 1 1204 lock struct(s), heap size 112624, 65338 row lock(s), undo log entries 44 
MySQL thread id 3331522, query id 103362521 [ServerIP] [USER] Updating 
UPDATE userproducts SET lowest_price=16.96, last_lowest_price_update='2012-09-09 20:07:30' WHERE product_id LIKE 'XXX'

65388 行被锁定，因为这个简单的更新？或者我只是 missanderstand 什么？遵循完成此更新的 PHP 代码。

$this->db->beginTransaction();

    try {
        for($i = 0; $i < count($products); $i++)
        {
            if(isset($products[$i]['price_total_end']))
            {
                if(count($products[$i]['price_total_end']) > 1)
                {
                    $this->db->query("UPDATE userproducts SET lowest_price=".$products[$i]['price_total_end'][1].", last_lowest_price_update='".date("Y-m-d H:i:s", time())."' WHERE product_id LIKE '".$products[$i]['p_id']."'");
                } elseif(count($asin[$i]['price_total_end']) == 1) {
                    $this->db->query("UPDATE userproducts SET lowest_price=-1, last_lowest_price_update='".date("Y-m-d H:i:s", time())."' WHERE product_id LIKE '".$products[$i]['p_id']."'");
                }
            }
        }
        $this->db->commit();
    } catch (Exception $e) {
        $this->db->rollBack();
        echo $e->getMessage();
    }

数组 $products 在他的第一个 50 级条目中。所以计数直到 50 不应该那么多，或者这绝对是无效的？接下来不要忘记的是，我们有 20 个 Worker 可以同时执行此查询。

我认为最后一个重要信息是 mysql_report。

MySQL 5.1.63-0+squeeze1  uptime 13 9:11:39      Sun Sep  9 20:44:50 2012

__ Key ___
Buffer used    55.00k of  16.00M  %Used:   0.34
  Current       2.92M            %Usage:  18.28
Write hit      99.95%
Read hit      100.00%


__ Questions ___
Total         103.61M    89.6/s
  DMS          90.73M    78.5/s  %Total:  87.57
  Com_          8.57M     7.4/s            8.27
  COM_QUIT      3.34M     2.9/s            3.22
  QC Hits     922.81k     0.8/s            0.89
  +Unknown     46.70k     0.0/s            0.05
Slow 2 s        9.33M     8.1/s            9.00  %DMS:  10.28  Log:  ON
DMS            90.73M    78.5/s           87.57
  INSERT       60.63M    52.4/s           58.51         66.82  
  UPDATE       17.80M    15.4/s           17.17         19.61
  DELETE        9.55M     8.3/s            9.22         10.53
  SELECT        2.76M     2.4/s            2.66          3.04
  REPLACE           0       0/s            0.00          0.00
Com_            8.57M     7.4/s            8.27
  set_option    3.40M     2.9/s            3.28
  show_fields   2.88M     2.5/s            2.78
  begin         1.13M     1.0/s            1.09


__ SELECT and Sort ____
Scan            3.60M     3.1/s %SELECT: 130.54
Range              47     0.0/s            0.00
Full join          24     0.0/s            0.00
Range check         0       0/s            0.00
Full rng join       1     0.0/s            0.00
Sort scan      24.32k     0.0/s
Sort range      1.69M     1.5/s
Sort mrg pass       0       0/s


__ Query Cache ___

Memory usage    1.05M of  16.00M  %Used:   6.57   
Block Fragmnt   4.55%   
Hits          922.81k     0.8/s   
Inserts         2.11M     1.8/s   
Insrt:Prune   2.36k:1     1.8/s   
Hit:Insert     0.44:1


__ Table Locks ____  
Waited            658     0.0/s  %Total:   0.00   
Immediate      90.73M    78.5/s


__ Tables ___   
Open              128 of  128    %Cache: 100.00  
Opened          3.95k     0.0/s


__ Connections __   
Max used          301 of  300      %Max: 100.33  
Total           3.34M     2.9/s


__ Created Temp ___    
Disk table      2.88M     2.5/s   
Table           2.89M     2.5/s    Size:  32.0M   
File                5     0.0/s


__ Threads ___    
Running            21 of  168   
Cached              6 of    8      %Hit:  97.09  
Created        97.29k     0.1/s    
Slow                0       0/s


__ Aborted ___    
Clients         2.45k     0.0/s 
Connects        2.75k     0.0/s


__ Bytes ___    
Sent           11.04G    9.5k/s  
Received       23.13G   20.0k/s   

__ InnoDB Buffer Pool _____    
Usage          82.86M of   1.46G  %Used:   5.52    
Read hit      100.00%    
Pages    
  Free         90.70k            %Total:  94.48    
  Data          4.92k                      5.12 %Drty:   4.07    
  Misc            386                      0.40  
  Latched           0                      0.00    
Reads         610.88G  528.3k/s 
  From file       816     0.0/s            0.00   
  Ahead Rnd         3     0.0/s    
  Ahead Sql        27     0.0/s    
Writes        614.37M   531.3/s    
Flushes        10.07M     8.7/s   
Wait Free           0       0/s


__ InnoDB Lock ___
Waits          837060     0.7/s    
Current            19
Time acquiring   
  Total     157140176 ms
  Average       18772 ms  
  Max           59096 ms


__ InnoDB Data, Pages, Rows __    
Data 
  Reads           919     0.0/s 
  Writes       11.96M    10.3/s  
  fsync         6.26M     5.4/s    
  Pending
    Reads           0
    Writes          0
    fsync           0

Pages   
  Created      39.41k     0.0/s    
  Read          2.64k     0.0/s   
  Written      10.07M     8.7/s    

Rows
  Deleted      40.82M    35.3/s
  Inserted     42.90M    37.1/s
  Read        540.45G  467.4k/s    
  Updated      47.48M    41.1/s

所以回到最重要的问题。为什么我们总是得到 1205 锁定等待超时错误？

更新:我现在尝试了其他几件事。首先，我将 INSERT ... ON DUPLICATE KEY UPDATE 更改为 UPDATE，如果没有任何效果，程序应该执行插入。因为以下两个原因。1. 99.9% 的操作是更新。2. 我想 - 不清楚那个伪造的 - 也许 INSERT 发生表锁定。

但是什么都没有改变。之后我用唯一键尝试了tipp。什么都没有改变。

然后我想到，MySQL 中有许多“休眠”进程。我认为这是由运行时间非常长的 Gearman 脚本引起的？所以我更改了更新的代码，就像我之前发布的那样。

$this->db->beginTransaction();

        try {
            for($i = 0; $i < count($asin); $i++)
            {
                if(isset($asin[$i]['price_total_end']))
                {
                    if(count($asin[$i]['price_total_end']) > 1)
                    {
                        // Es konnten Konkurrenten ausgemacht werden
                        if( $asin[$i]['price_total_end'][0] > 0 )
                        {
                            // Prüfe ob der eigene Preis der günstigste ist
                            if( $asin[$i]['merchant_end'][0] == $merchantId )
                            {
                                // Ja der eigene Preis ist der günstigste, deshalb soll der nächste Preis der lowest sein.
                                $this->db->query("UPDATE userproducts SET lowest_price=".$asin[$i]['price_total_end'][1].", last_lowest_price_update='".date("Y-m-d H:i:s", time())."' WHERE product_id LIKE '".$asin[$i]['asin']."'");
                            } else {
                                $this->db->query("UPDATE userproducts SET lowest_price=".$asin[$i]['price_total_end'][0].", last_lowest_price_update='".date("Y-m-d H:i:s", time())."' WHERE product_id LIKE '".$asin[$i]['asin']."'");
                            }
                        }
                    } elseif(count($asin[$i]['price_total_end']) == 1) {
                        if( $asin[$i]['price_total_end'][0] >= 0 )
                        {
                            $this->db->query("UPDATE userproducts SET lowest_price=-1, last_lowest_price_update='".date("Y-m-d H:i:s", time())."' WHERE product_id LIKE '".$asin[$i]['asin']."'");
                        }
                    }
                }
            }
            $this->db->commit();
        } catch (Exception $e) {
            $this->db->rollBack();
            echo $e->getMessage();
        }
        **$this->db->closeConnection();**

但是什么都没有改变。连接数仍为 301，线程数为 32。我的代码有问题吗？

我真的无法面对这个问题。

我们非常欢迎您的想法和建议。谢谢大家。

Answer 1

首先尝试使用以下方法获取所有产品的主键:

SELECT primaryKey WHERE product_id LIKE '".$products[$i]['p_id']."'"

然后遍历结果并使用 WHERE 部分中的 primaryKey 更新它们。

对所有 $products 重复该操作。并在所有迭代后的最后提交。

我的直觉/早期经验告诉你应该能够通过这种方式避免死锁，但遗憾的是我无法解释原因。