Takes place

When the boss helped review the code, he saw the SQL I had to write, and gave me a correction.

$coupon = EnterpriseCoupon::whereId($params['coupon_id'])
    ->where('scope_id'.$params['scope_id'])
    ->lockForUpdate()
    ->first();
Copy the code

If an exception occurs after mysql begin, throw to exit. Rollback is not necessary, because mysql links are short links, so it will proactively release the lock thread.

The data table

CREATE TABLE `kbb_common`.`enterprise_coupon` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'on the id',
  `creator_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'creator admin_users.id',
  `name` varchar(50) NOT NULL DEFAULT ' ' COMMENT 'Coupon name',
  `introduce` varchar(255) DEFAULT ' ' COMMENT 'Coupon Description',
  `introduce_info` varchar(255) DEFAULT ' ' COMMENT 'Coupon Description Details',
  `type` int(2) unsigned NOT NULL DEFAULT '1' COMMENT 'Coupon Type 1: Full Discount Coupon',
  `over_amount` decimal(10.2) unsigned NOT NULL DEFAULT '0.00' COMMENT 'How much is available (in yuan)',
  `discount_fee` decimal(10.2) unsigned NOT NULL DEFAULT '0.00' COMMENT 'Discount amount of Coupon (YUAN)',
  `effective_date` date DEFAULT NULL COMMENT 'Fixed Effective Date',
  `expired_date` date DEFAULT NULL COMMENT 'Fixed expiration date',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Status 0 pending review 1 Normal 2 Removed',
  `remarks` varchar(255) DEFAULT ' ' COMMENT 'Remarks (background only)',
  `created_at` timestamp NULL DEFAULT NULL COMMENT 'Creation time',
  `updated_at` timestamp NULL DEFAULT NULL COMMENT 'Update Time',
  `total_num` int(10) unsigned NOT NULL DEFAULT '1' COMMENT 'Number of Coupons issued',
  `use_channel` int(10) unsigned DEFAULT '0' COMMENT 'Use Scenario 1101: Hotel; 1102: delivery; 1103: take a taxi; 1104: a ticket; 1105: come on; 1106: train ticket; ',
  `superposition` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT 'Do I stack 0? Do I stack 1?',
  `used_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Number of Coupons used',
  `corp_name` varchar(255) DEFAULT ' ' COMMENT 'Business Name',
  `scope` int(10) unsigned NOT NULL DEFAULT '1' COMMENT 'Scope 1 specifies enterprise, scope 2 specifies locale',
  `scope_id` int(10) DEFAULT NULL COMMENT 'Scope ID is the enterprise ID of the enterprise server if the enterprise is specified'.PRIMARY KEY (`id`),
  KEY `idx-name` (`name`),
  KEY `idx-corp_id` (`scope_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COMMENT='Business Coupon Form';
Copy the code

The insert statement

INSERT INTO `kbb_common`.`enterprise_coupon`(`id`, `creator_id`, `name`, `introduce`, `introduce_info`, `type`, `over_amount`, `discount_fee`, `effective_date`, `expired_date`, `status`, `remarks`, `created_at`, `updated_at`, `total_num`, `use_channel`, `superposition`, `used_num`, `corp_name`, `scope`, `scope_id`) VALUES (1.15.'$100 Coupon'.'$100 Coupon'.' '.1.2000.00.100.00.'2021-09-03'.'2021-09-15'.1.'$100 Coupon'.'the 2021-09-03 15:32:14'.'the 2021-09-14 11:03:14'.100.1101.0.8.' '.1.10);
INSERT INTO `kbb_common`.`enterprise_coupon`(`id`, `creator_id`, `name`, `introduce`, `introduce_info`, `type`, `over_amount`, `discount_fee`, `effective_date`, `expired_date`, `status`, `remarks`, `created_at`, `updated_at`, `total_num`, `use_channel`, `superposition`, `used_num`, `corp_name`, `scope`, `scope_id`) VALUES (2.15.'$200 Coupon'.'$200 Coupon'.' '.1.2000.00.5000.00.'2021-09-03'.'2021-09-16'.1.'$100 Coupon'.'the 2021-09-03 15:35:47'.'the 2021-09-03 15:35:47'.200.1102.0.2.' '.1.1);
INSERT INTO `kbb_common`.`enterprise_coupon`(`id`, `creator_id`, `name`, `introduce`, `introduce_info`, `type`, `over_amount`, `discount_fee`, `effective_date`, `expired_date`, `status`, `remarks`, `created_at`, `updated_at`, `total_num`, `use_channel`, `superposition`, `used_num`, `corp_name`, `scope`, `scope_id`) VALUES (3.15.'$300 Coupon'.'$300 Coupon'.'$300 Coupon'.1.2000.00.200.00.'2021-09-03'.'2021-09-08'.1.'$100 Coupon'.'the 2021-09-03 15:48:53'.'the 2021-09-08 16:06:43'.200.1101.0.200.' '.1.1);
INSERT INTO `kbb_common`.`enterprise_coupon`(`id`, `creator_id`, `name`, `introduce`, `introduce_info`, `type`, `over_amount`, `discount_fee`, `effective_date`, `expired_date`, `status`, `remarks`, `created_at`, `updated_at`, `total_num`, `use_channel`, `superposition`, `used_num`, `corp_name`, `scope`, `scope_id`) VALUES (4.15.'$500 Coupon'.'$500 Coupon'.'$500 Coupon'.1.2000.00.500.00.'2021-09-03'.'2021-09-08'.1.'$100 Coupon'.'the 2021-09-06 11:19:35'.'the 2021-09-08 16:24:16'.500.1101.0.0.' '.1.1);
INSERT INTO `kbb_common`.`enterprise_coupon`(`id`, `creator_id`, `name`, `introduce`, `introduce_info`, `type`, `over_amount`, `discount_fee`, `effective_date`, `expired_date`, `status`, `remarks`, `created_at`, `updated_at`, `total_num`, `use_channel`, `superposition`, `used_num`, `corp_name`, `scope`, `scope_id`) VALUES (5.15.'$500 Coupon'.'$500 Coupon'.'$500 Coupon'.1.2000.00.500.00.'2021-09-03'.'2021-09-08'.1.'$100 Coupon'.'the 2021-09-06 17:58:39'.'the 2021-09-08 16:09:45'.2500.1101.0.1.Highlight Technology.1.1);
INSERT INTO `kbb_common`.`enterprise_coupon`(`id`, `creator_id`, `name`, `introduce`, `introduce_info`, `type`, `over_amount`, `discount_fee`, `effective_date`, `expired_date`, `status`, `remarks`, `created_at`, `updated_at`, `total_num`, `use_channel`, `superposition`, `used_num`, `corp_name`, `scope`, `scope_id`) VALUES (6.15.'$500 Coupon'.'$500 Coupon'.'$500 Coupon'.1.2000.00.500.00.'2021-09-03'.'2021-09-08'.2.'$100 Coupon'.'the 2021-09-06 18:01:04'.'the 2021-09-06 18:01:04'.2500.1101.0.0.Highlight Technology.1.1);

Copy the code

A lock waiting scenario occurs. Procedure

-- session 1
begin;
select * from enterprise_coupon where status = 1

- wait
Copy the code
-- session 2
update enterprise_coupon set remakrs = 1 where status =2; This one right heresqlIt waits, does not execute, and finally times out.Copy the code

Cause of occurrence

Innodb rows are locked by locking index entries on indexes. When we set status = 1,status we don’t have an index, so we do a full table scan. So the whole table data traversal out, are added to the lock. In this way, if you modify human data, it will block. Wait for the SQL commit.

Matters needing attention

Therefore, when using a lock, we should pay attention to whether there is an index on the lock, no index so very dangerous.

MySQL > insert row lock on index

See the lock

Show processList query all database statuses select * from information_schema.innodb_trx query transaction processes select * from Information_schema innodb_locks check the lock

SQL statements commonly used to query ongoing transactions and wait locks