数据库表锁和行锁

最新推荐文章于 2024-02-22 10:26:06 发布

weixin_34059951

最新推荐文章于 2024-02-22 10:26:06 发布

阅读量139

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/3362233/blog/874271

版权

2019独角兽企业重金招聘Python工程师标准>>>

页级锁：引擎BDB。

表级锁：引擎MyISAM，锁住整个表，可以同时读，写不行。

行级锁：引擎InnoDB，单独的一行记录加锁。

表级,直接锁定整张表，在锁定期间，其他进程无法对该表进行写操作。如果你是写锁，则其他进程无法进行读操作。

行级，仅对指定的记录进行加锁，这样其他进程还是可以对同一张表中其他的记录进行操作。

页级，表锁速度快，但冲突多，行级锁冲突少，但速度慢。所以取了折中的页级，一次锁定相邻的一组记录。

mysql5.1支持对MyISAM和MEMORY进行表级锁定，对BDB进行页级锁定，对InnoDB进行行级锁定。

对于write，mysql使用的表锁定方法原理如下：

如果在表上没有锁，在它上面放一个写锁。否则，把锁定请求放在写锁定队列中。

对read,mysql使用的锁定方法原理如下：

如果在表上没有写锁定，把一个读锁定放到它上面。否则，把锁清秋放在读锁定队列中。

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

行锁定的优点：

1、当在许多线程中访问不同的行时只存在少量锁定冲突。

2、回滚时只有少量的更改。

3、可以长时间锁定单一的行

行级锁定的缺点：

1、比页级或表级锁定占用更多的内存。

2、当在表的大部分中使用时，比页级或表级锁定速度慢，因为必须获取更多的锁。

3、如果在大部分数据上经常进行GroupBY操作或者必须经常扫描整个表，比其他锁定明显慢很多。

4、用高级别锁定，通过支持不同的类型锁定，也可以很容易调节应用程序，因为其锁成本小于行级锁定。

在以下情况，表锁定优先页级或行级锁定：

1、表的大部分语句用于读取。

2、对严格的关键字进行读取和更新，可以用单一的读取关键词来提取一行。

3、select结合并行的insert语句，并且只有很少的update或delete语句。

4、在整个表上有许多扫描或groupby操作，没有任何写操作。

===============mysql锁表类型和解锁语句========================

如果想要在一个表上做大量的insert和select操作，但是并行的插入却不可能时，可以江榉路插入到临时表中，然后定期将临时表中的数据更新到实际的表里。命令：

mysql>LOCK TABLE real_table WRITE,insert_table WRITE;

mysql>INSERT INTO real_table select * from insert_table;

mysql>TRUNCATE TABLE insert_table;

mysql>UNLOCK TABLES;

行级锁的优点：

1、在很多线程请求不同记录时减少冲突锁。

2、事务回滚时减少改变数据。

3、使长时间对单独的一行记录加锁成为可能。

行级锁的缺点：比页级锁和表级锁小号更多的内存。

锁是计算机协调多个进程或线程并发访问某一资源的机制，不同的数据库的锁机制大同小异。由于数据库资源是一种供很多用户共享的资源，所以如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的重要因素。了解锁机制不仅可以使我们更有效的开发利用数据库资源，也使我们能够更好地维护数据库，从而提高数据库的性能。

mysql的锁机制比较简单，其显著的特点是不同的存储引擎支持不同的锁机制。

例如：MyISAM和MEMORY存储引擎采用的是表级锁；BDB存储引擎采用的是页面锁，同时也支持表级锁；InnoDB存储引擎即支持行级锁，也支持表级锁，默认是采用行级锁。

以上三种锁的特性可大致归纳如下：

1)表级锁：开销小，加锁快；不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。

2)行级锁：开销大，加锁慢；会出现死锁，锁定粒度小，发生锁冲突的概率最低，并发度也最高。

3)页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

三种锁各有特点，若仅从锁的角度来说，表级锁更适合以查询为主，只有少量按索引条件更新数据的应用，如web应用；行级锁更适合于大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理系统。

mysql表级锁有两种模式：表共享读锁和表独占写锁。就是说对MyISAM表进行读操作时，他不会阻塞其他用户对同一张表的读请求，但会阻塞对同一表的写操作；而对MyISAM表的写操作，则会阻塞其他用户对同一张表的读和写操作。

MyISAM表的读和写是串行的，即在进行读操作时不能进行写操作，反之也是一样。但在一定条件下MyISAM表也支持查询和插入的操作的并发进行，其机制是通过一个系统变量来进行，当其值设置为0时，不允许并发插入；其值为1时，如果MyISAM表中没有空洞（即表中没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录；当其值为2时，无论MyISAM表中有木有空洞，都允许在表尾并发插入记录。

MyISAM锁调度实现：当一个线程请求对MyISAM表中的读锁，同时另一个线程也请求同一张表的写锁，此时写进程优先获得锁。但是大量的写操作会造成查询操作很难获得读锁，从而造程永久阻塞。可以通过指定参数low-priority-updates，使MyISAM默认引擎给与读请求优先的权利，设置为1（set low-priority-updates=1），使优先级降低。