mysql delete 会锁表吗_mysql 锁及核心问题

【问题背景】

协议绑卡需要插入绑卡表（表A）和签约表（表B）

后台系统需要删除测试数据

【问题发现】

线上异常日志不定期出现Deadlock found when trying to get lock

【问题分析】

InnoDB的行锁是通过给索引项加锁实现的。这就意味着只有通过索引条件检索数据时，InnoDB才使用行锁，否则使用表锁。

-----------------------------------------

mysql 的行锁是通过索引实现的。

在mysql中，如果查询条件带有主键，会锁行数据，如果没有，会锁表。

当where条件中的字段没有加索引时，会锁住整张表。

在有索引的情况下，更新不同的行，innodb默认的行锁是不会阻塞的。

where 后面的索引失效时，行锁变表锁，其他事物操作会有阻塞

-----------------------------------------

delete from bankcard_base where user_id= and product_id=

delete from bankcard_agreement where user_id= and product_id=

insert into bankcard_base values();

insert into bankcard_agreement values();

CREATE TABLE `bankcard_user` (

`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',

bigint(20) NOT NULL COMMENT '用户id(对应用户中心actid)',

bigint(20) NOT NULL COMMENT '业务编号',

bigint(20) NOT NULL COMMENT '支付产品id',

bigint(20) NOT NULL COMMENT '银行卡id',

`bind_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '绑卡类型: 0.快捷绑卡、1.协议绑卡、2非自营协议绑卡',

`is_default` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否为默认卡：0：否，1：是',

`status` tinyint(4) DEFAULT '0' COMMENT '状态: 0:正常，1:冻结',

`product_type` tinyint(4) NOT NULL DEFAULT '-1' COMMENT '-1 未知，其它参照渠道枚举',

PRIMARY KEY (`id`),

UNIQUE KEY `uniq_user_channel_card` (`user_id`,`biz_id`,`product_id`,`bank_card_id`) USING BTREE,

KEY `idx_user_card_id` (`bank_card_id`)

) ENGINE=InnoDB AUTO_INCREMENT=4819 DEFAULT CHARSET=utf8 COMMENT='银行卡用户信息表';

CREATE TABLE `bankcard_agreement` (

`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',

bigint(20) NOT NULL COMMENT '平台用户id',

bigint(20) NOT NULL COMMENT '支付产品id',

bigint(20) NOT NULL COMMENT '银行卡id',

varchar(32) DEFAULT NULL COMMENT '支付签约协议号',

`status` tinyint(4) DEFAULT '0' COMMENT '状态: 0:正常，1:冻结',

PRIMARY KEY (`id`),

UNIQUE KEY `uniq_user_channel_card` (`user_id`,`product_id`,`bank_card_id`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=3257 DEFAULT CHARSET=utf8 COMMENT='银行卡协议表';

select @@version

set autocommit=0;

MySQL锁分为行锁和表锁

表锁锁定整个表，锁定粒度大，并发低，不会出现死锁哦。

行锁，开销大，会出现死锁；

读锁/写锁

读锁的特点：读共享、写阻塞

myisam 在执行查询语句前，自动给表加读锁，执行增删改操作前，自动加写锁，myisam的表级锁有两种模式

表共享读锁

表独占写锁

结论：

1、对myisam表的读操作（加读锁），不会阻塞其他进程对同一类的读请求，但会阻塞对同一表的写请求，只有当读锁释放后，才会执行其他进程的写操作

lock table mylock read;

1、事物1 读mylock 可以， 事物2 读mylock 可以

2、事物1 update/insert/delete mylock 报错， 事物2 update/insert/delete mylock 阻塞

3、事物1 读其他未加锁的表 报错，事物2可以读或者更新其他未锁定的表

2、对myisam表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。

写锁

lock table mylock write

1、事物1 查询 mylock 可以，事物2查看 mylock阻塞，需要等待锁被释放

2、事物1 更新/插入 mylock可以执行

3、 事物1 查询其他未加锁的表报错

-------------------------行锁-----------------

总结：多个事务操作同一行数据时，后来的事务处于阻塞等待状态。这样可以避免了脏读等数据一致性的问题。后来的事务可以操作其他行数据，解决了表锁高并发性能低的问题。

加锁的方式：自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁；当然我们也可以显示的加锁：

共享锁：select * from tableName where ... + lock in share more

排他锁：select * from tableName where ... + for update

show profiles for

如何分析行锁定：show status like 'innodb_row_lock%';

比较重要的是：innodb_row_lock_time_avg 平均等待时长

innodb_row_lock_waits等待总次数

innodb_row_lock_time等待总时长

如何锁定一行 select * from t for update;

行锁的建议：

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁

合理设计索引，尽量缩小索引的范围

尽可能较少检索条件，避免间隙锁

尽量控制事务大小，减少锁定资源量和时间长度

尽可能低级别事务隔离

varchar不加单引号会引发行锁。

--------------------核心问题汇总------------------

1、提测后编译不通过：提测需要走icode变更，编译打包通过后才可提测

2、提测后发现无权限：提测的icode分支 需要加上相关的测试人员

3、提测后冒烟多次不通过：冒烟必须走提测后的分支冒烟，必须自上而下全流程走，自测联调不叫冒烟

4、提测邮件缺东少西：该有的：提测分支，api版本，scf.xml信息，相关的sql，相关的配置开关，相关的文档链接

5、提测前的配置相关应该确定，例如产品配置，必须跟上线保持一致，不能自定义测试

6、接口文档内容错误：接口名错误，未提供tcp全路径，接口参数错误，不能直接copy原来的

7、接口改动不与业务方沟通，私自修改删除：合同查询接口删除字段，测试已经提醒过不能随意删除要跟业务沟通

8、提测前没有详细的设计文档，不足以写用例，会议讲实现方案不是实际开发人员讲的，组长讲的与实际不符 有的点讲不清楚

9、一个bug反复多次修改不好，这种应该自测一下：合同查询的一个金额提了11次

10、用例评审测试提出的风险点问题，应该重视以及反馈：还款后查询工行入账是否实时已经在讲方案设计和用例评审的时候都说过，没有反馈，这个问题导致后续变更实现方案

11、用例评审确认的点发现与实际不符，用例评审需要认真

12、测试是需要了解代码实现的细节不是黑盒，开发人员应该全力配合

13、提测前应该梳理出每个服务的改动点，以便一起确定测试范围

14、提测前应该合并之前分支，以便测试进行codediff，尽量避免测完了合并再回归

15、开发代码codereview 应该至少测试中前期进行，不能已经后期了或测完了因为cr去改动代码

16、提交代码后未打包编译，时常出现打包编译失败情况

17、代码优化，bug修复未从最外层自测，出现影响其他服务层的问题