Percona MySQL table locking

Question

PHP

Good afternoon

I installed Percona MySQL server 5.5 + HandlerSocket. Decided to play. Here is the test script:

Insert with PDO code

<?php

function out($line = '')
{
  echo PHP_EOL.$line.PHP_EOL;
}

function sep()
{
  out();
  out('------------------------------');
  out();
}

/**
 * @param $pdo PDO
 * @param $config array
 */
function run($pdo, $config)
{
  $sql = "TRUNCATE products";

  $stmt = $pdo->prepare($sql);
  $stmt->execute();

  $numberOfInsertOps = $config['insert_number'];

  $startInsertTime = microtime(true);

  $sql = "INSERT INTO products (name, price) VALUES (:name, :price)";
  $stmt = $pdo->prepare($sql);

  for ($i = 0; $i < $numberOfInsertOps; $i++)
  {
    $name = md5(rand(0, 1000));
    $price = rand(1, 1000);

    $stmt->bindParam(':name', $name);
    $stmt->bindParam(':price', $price);

    try
    {
      $result = $stmt->execute();

      if (!$result)
      {
        out('shit happened');
      }

      out($pdo->lastInsertId());
    }
    catch (Exception $e)
    {
      sep($e->getMessage());
    }
  }

  out('Insert in one query time: ' . ( microtime(true) - $startInsertTime) );
}


$pdo = new PDO("mysql:host=$hostname;dbname=$db_name", $username, $password);

run($pdo, array(
  'insert_number' => 100
));

According to the idea, all 100 records should be inserted. On another machine, everything works correctly, but there is a regular MySQL.
And the following happens to me:

- The number of added storks always lies in the range of 40-70, and each time the script is executed, their number changes.
- If you check $pdo->errorInfo()- it returns the code 0000 - it knows that everything is fine, just the number of affected rows is 0.
- And after the first not inserted row - all the following ones are not added either.
That is, as a result of the script execution, I see something like:

1
2
3
…
60
61
62
shit
62
shit
62
shit
62
shit

Looks like blockers.

If the table engine is on MyISAM, then there are no problems.

If you add such a hack (from the 49th line) - the problem is solved (But it cannot be called a solution).

Here is the structure of the table:

CREATE TABLE IF NOT EXISTS `products` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `price` int(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And one more thing:

if you insert records into the table using the HandlerSocket:

Insert with HandlerSocket code

  $hs = new \HSPHP\WriteSocket();
  $hs->connect();


  $id = $hs->getIndexId('test','products','','name,price');

  $startHandlerSocketInsertTime = microtime(true);
  for ($i = 0; $i < $numberOfInsertOps; $i++)
  {
    $name = 'handler-'.md5(rand(0, 1000));
    $price = rand(0, 1000);

    $hs->insert($id, array($name, $price));
  }

  out('Insert HandlerSocket time: ' . ( microtime(true) - $startHandlerSocketInsertTime) );

The number of rows inserted into the database also depends on the number of loop iterations.

if 10 loops - I have 100 rows in DB table
if 50 loops - 50 rows
if 100 loops - 100 rows
if 500 loops - 500 rows
if 1000 loops - ~1100 rows and this number will change every time the script is run.

What could be the problem? How to find and fix it?

Thank you in advance.

update.
Found out that the error is this:

Array (
[0] => 00000
[1] =>
[2] =>
)

An error occuredSQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query

Answer 1

looks like deadlock.
Try doing
CREATE TABLE innodb_monitor (i INT) ENGINE=INNODB;
Plus, you need to check the settings innodb_thread_concurrency and innodb_buffer_pool_instances

Answer 2

Reinstalled php and everything was fine. (more precisely, updated from 5.3 to 5.5)