MYSQL加锁的测验

存储引擎支持的锁定级别

myisam 表级别

memory 表级别

inndb 行级别

bdb： 页级别

lock锁定类型

锁定方式目的

读锁自己与其他线程只能读取该表

写锁只有当前线程能够对表进行写入操作(其他线程也无法读这部分数据)

读锁的英文叫法是shared locks，shared是共享的意思，共享锁，就是所有用户都可以共享进行读(包括加锁的用户)，不能写。

写锁的英文叫法是Exclusive Locks，Exclusive是独有、排外的意思，只有自己(加锁的用户)才能进行些写入操作，其他用户只能读。有些书籍中有不同叫法，比如互斥锁、排他锁。

纠正一下：以前理解错误了。加了排他锁定，其他事务是无法读取数据的。网上的资料比较零散。

最近看了jim Gray那本《事务处理概念与技术》，里面提到排他锁时：保留对该节点写的权利，防止其他事务在该节点及其后代节点加{x,u,s,six,is,ix}锁。

提到的s就是共享锁了，既然防止其他事务加s锁，那么其他线程是无法读的(所有线程获取数据之前必须先申请锁才能操作)。文章下面加的试验也会证明了这点。

ps：看来总结也好，加深了理解，如果不总结，也不会发现细节理解偏差。

测试一：测验读锁

运行一个读锁：“lock table `cat` READ”

然后尝试使用update语句操作：

update `cat` set remark= ‘ceshi’

报错如下：

Table ‘a’ was locked with a READ lock and can’t be updated

现在解锁(针对当前线程锁定的所有表解锁)：”unlock tables”

然后再次运行update语句，报错消失。完成更新操作。说明已经解除读锁了(可以进行更新)

测验二：测验写锁

1、我在一个客户端SQLyog中运行一个写锁sql：LOCK TABLE cat WRITE;

2、另外开一个客户端(线程),尝试运行更新：

update `cat` set remark= ‘jgjgjg’

情况：因为使用了写锁，只有自己能够修改数据,其他线程无法执行update操作，此时服务器端的php程序一直等待数据库给予响应结果，数据被锁定了，根本没法执行sql语句，处于等待中，数据库并不会报错，因为这本来就是一个很正常的情况，需要等待释放锁才能执行update操作，其实大并发环境下就是这种情况(很多用户同时在执行sql操作，有的加了写锁定)，于是速度就慢下来了。

结果phpmyadmin一直处于等待中,等待服务器给予客户端(浏览器)数据，截图如下：

经验：使用phpmyadmin无法模拟出一个线程的情况。因为每次php运行完毕后，与数据库的连接就会自动断开(php脚本特性)。

而SQLyog这样的工具能够保持连接不断掉。所以,测验写锁的时候完全可以使用同一个工具测验出来。

增加测验：测验加写锁后，其他事务无法读数据的情况

同样也是在SQLyog这样的工具中运行：LOCK TABLE cat WRITE

因为加了x锁后，其他事务是无法获得s锁,所以根本无法读数据。phpmyadmin这边查询这个表，都是等待状态。

下面我在SQLyog释放掉写锁后，phpmyadmin这边就能读取数据了

InnoDB使用行锁定，BDB使用页锁定。对于这两种存储引擎，都可能存在死锁。这是因为，在SQL语句处理期间，InnoDB自动获得行锁定和BDB获得页锁定，而不是在事务启动时获得。

update语句默认比select语句优先权高。可以修改，在启动mysql的时候加上参数，用–low-priority-updates启动mysqld

在大流量、大数量的网站，往往瓶颈不在于具体的语言，其实.net、java、php等语言速度的差别是有，但是很小，忽略不计。往往瓶颈在于数据库。我是这样理解：

$conn->query(“update where ….”);

…php其他代码

//php需要等待数据库(比如mysql)给予返回结果，才能继续往下面执行代码，但里mysql被锁定了，一直没有执行完毕。所以卡死了,一直在等待中。这就是为什么语言不是瓶颈，瓶颈往往在数据库(数据量大的时候，数据库压力很大)，这就是同步执行，需要等到上面代码执行完毕才能继续执行，以前听过阿里巴巴的分享，使用一种异步执行方案。并行加载数据，那么代码的执行耗时取决于最耗时的操作(因为所有操作都是并行开始运行)

其他测验办法：还可以开多个mysql命令行界面来测验。每个界面就是一个session。

关于大表查询少用left join的原因

进行select查操作，加的是共享锁(对整个表还是对行，要看什么存储引擎)。共享锁，则其他线程只能读数据。那么insert，update操作就会阻塞，等待select操作完成后释放掉共享锁后，这些线程才能进行写数据操作。

如果使用left join进行关联查询，一张表的数据量大，就会导致copyingd table操作了，意思是要先复制一张临时表，在临时表上面进行计算操作。这个时间比较长，就会阻塞掉其他线程的写入操作，一直处在等待状态。需要进行写入的线程越多，那么越多的线程等待。

解决办法是：要迅速的查询，拆分成多步骤来操作，这样就算是加读锁的时候，也是针对当前操作的表。操作释放后，可以快速的释放掉锁。

我想到用现实生活中哲学来理解：一次干多件事情，当然是爽，但是非常揪心，搞的复杂，影响的面广，大面积的受到影响。如果拆分成小部分，一次干一小部分，那么受到影响的面积小。逐个逐个来分解任务。

在实际场景中，left join导致的阻塞其他线程的写操作的场景，刚好被我遇到了：

上面是show processlist命令查看出来的，这个命令可以查看mysql当前有哪些查询线程，会列出查询的语句出来。

上面情况显示，好几个insert操作被阻塞掉，一直在延迟，time项如果我没理解错的话，就是已经等待了这么多秒数了。

信息显示：waiting for table level lock。更新操作需要先获取锁(myisam存储引擎只支持表级别的锁,所以先获取表锁)。

问：到底是什么情况导致无法获取表锁？

肯定是其他线程在对别加了锁，一直没有执行完毕。从下面这张图能看出是什么原因：

显示的状态栏表示Coping to tmp table，正在将数据复制到临时表(tmp table)。左侧显示是查询语句，是left join。

貌似进入死锁状态了，a想要获取表的锁，才能继续操作。b线程也要获取，但是有个线程一直没有释放掉。

什么情况下会出现 copying to tmp table的操作呢？

有英文这样解释：

Copying to tmp table on disk The temporary result set was larger than tmp_table_size and the thread is now changing the in memory-based temporary table to a disk based one to save memory.

临时结果比tmp_table_size(这是一个内存缓存区)要大，存不下了，就会保持到磁盘去。

方案:

1、可以考虑把这个myisam存储引擎调整为innodb，innodb就支持行锁。

2、但是我觉得本质还是要减少left join查询，需要这样查询，拆分成多条sql，分步骤来执行会更好。

在互联网的应用结构中。联表查询，还是要慎重，因为数据库是共享资源，很多应用程序都要来访问。一旦一个线程锁定数据表了，就会造成其他线程操作数据被阻塞了。

所以思想是尽量快速完成查询。传统的数据库开发中使用复合查询，尽量一条sql可以完成很多事情的思想。在互联网是不要这样子做。

拆分成多条sql进行查询，应用程序编码还是增加了很多复杂度，本来一条sql丢入到query($sql)中就完成，现在要分成多个步骤。程序员会觉得麻烦。

不过，为了性能优化，是要这样子。数据库资源毕竟有限。有时候程序员调整一下思路，对于数据库压力的缓解是很大的。

个人收藏笔记记录

开通VIP