Question 248511 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Procedure Efficiency and Safety for Bulk Insertion and Updating the Same Record in a Cluster

I have a table for managing items in a warehouse with the following schema:

CREATE TABLE `item` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `barcode` varchar(45) DEFAULT NULL,
  `sku` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `barcode_UNIQUE` (`barcode`)
) ENGINE=InnoDB;

Each item in this table is associated with an SKU (Stock Keeping Unit) and has a unique ID that serves as its primary key. Additionally, each item is required to possess a unique 13-digit barcode, which is generated by prefixing the ID with zeros to ensure it consists of exactly 13 digits.

We often need to insert a significant number of records for each item. To facilitate this, we’ve implemented a procedure that accepts both the SKU and the quantity of records to be inserted. Within this procedure, we perform individual record insertion, followed by updating each record’s barcode with the corresponding added ID. This process is repeated (in single transaction) based on the specified number of records.

Here’s the procedure:

DELIMITER $$
CREATE PROCEDURE `bulk_insert_into_item`(IN inSku varchar(100), IN inSize INT)
BEGIN
  START TRANSACTION;
    SET @counter = 0;
    REPEAT
        INSERT INTO item(sku) VALUES (inSku);
        SET @recordId =  LAST_INSERT_ID();
        UPDATE item SET barcode =lpad(@recordId, 13, 0)  WHERE id = @recordId;
        SET @counter = @counter + 1;
    UNTIL @counter >= inSize END REPEAT;
    COMMIT;
END$$
DELIMITER ;

I have concerns about the efficiency of this procedure, particularly in two key aspects:

  1. How well does the procedure perform when adding a large number of
    items?
  2. Will the table reading be locked at the time of inserting?
  3. Our database is clustered, is the procedure safe for
    concurrent insertion of multiple items?

Any insights or suggestions on improving the efficiency and safety of this procedure would be greatly appreciated.

Get suggestions to improve the efficiency and safety of this procedure

Tags:

2

Answers


  1. 0

    Test this:

    CREATE PROCEDURE bulk_insert_into_item (IN inSku varchar(100), IN inSize INT)
BEGIN
  INSERT INTO item (sku)
  WITH RECURSIVE cte AS (
    SELECT 1 id UNION ALL SELECT id + 1 FROM cte WHERE id < inSize
    )
  SELECT inSku FROM cte;
  UPDATE item SET barcode = LPAD(id, 13, '0') WHERE id >= LAST_INSERT_ID() AND sku = inSku;
END

    or

    CREATE PROCEDURE bulk_insert_into_item (IN inSku varchar(100), IN inSize INT)
BEGIN
  INSERT INTO item (sku)
  WITH RECURSIVE cte AS (
    SELECT 1 id UNION ALL SELECT id + 1 FROM cte WHERE id < inSize
    )
  SELECT inSku FROM cte;
  UPDATE item SET barcode = LPAD(id, 13, '0') WHERE barcode IS NULL;
END

    fiddle

    Login or Signup to reply.
  2. 0

    If you are only occasionally encountering a deadlock, catch it and rerun the procedure. It should work on the second try. You should add this code even if some process theoretically eliminates the deadlock.

    If you are often deadlocking, then speeding up the procedure will reduce the frequency of deadlocks. A REPEAT (or other loop) is terribly slow, especially with 2 SQLs inside it. I don’t know whether Akina’s ‘recursive CTE’ will be faster, but it has the potential — 1 big SELECT + 1 INSERT + 1 UPDATE. 3 big-ish SQLs will probably be a lot faster than the current 200.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search