关闭

浅说Mysql的行级锁,表级锁,页级锁

72人阅读 评论(0) 收藏 举报
分类:



MySQL行级锁:引擎 INNODB,理解为对单独的一行记录加锁

仅对指定的记录进行加锁,这样其它进程还是可以对同一个表中的其它记录进行操作。


MySQL表级锁:引擎 MyISAM , 理解为锁住整个表,可以同时读,写不行

直接锁定整张表,在你锁定期间,其它进程无法对该表进行写操作。如果你是写锁,则其它进程则读也不允许。


MySQL页级锁:引擎 BDB

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


InnoDB使用行锁定,BDB使用页锁定。对于这两种存储引擎,都可能存在死锁。

这是因为,在SQL语句处理期间,InnoDB自动获得行锁定和BDB获得页锁定,而不是在事务启动时获得。

行级锁定的优点:
当在许多线程中访问不同的行时只存在少量锁定冲突。

回滚时只有少量的更改。

可以长时间锁定单一的行。

行级锁定的缺点:
·         比页级或表级锁定占用更多的内存。
·         当在表的大部分中使用时,比页级或表级锁定速度慢,因为你必须获取更多的锁。
·         如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表,比其它锁定明显慢很多。
·         用高级别锁定,通过支持不同的类型锁定,你也可以很容易地调节应用程序,因为其锁成本小于行级锁定。

在以下情况下,表锁定优先于页级或行级锁定:
·         表的大部分语句用于读取。
·         对严格的关键字进行读取和更新,你可以更新或删除可以用单一的读取的关键字来提取的一行:
·                UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
·                DELETE FROM tbl_name WHERE unique_key_col=key_value;
·         SELECT 结合并行的INSERT语句,并且只有很少的UPDATE或DELETE语句。
·         在整个表上有许多扫描或GROUP BY操作,没有任何写操作。


三种锁的特性可大致归纳如下:
1) 表级:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
2) 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
3) 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

三种锁各有各的特点,若仅从锁的角度来说,表级锁更适合于以查询为主,

只有少量按索引条件更新数据的应用,如WEB应用;

行级锁更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。


InnoDB,是MySQL的数据库引擎之一,事务型数据库的首选引擎,支持ACID事务,支持行级锁定

InnoDB是为处理巨大数据量时的最大性能设计。InnoDB存储引擎完全与MySQL服务器整合,

InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。

InnoDB存储它的表&索引在一个表空间中,表空间可以包含数个文件(或原始磁盘分区)。

给 MySQL 提供了具有事务(transaction)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)、

多版本并发控制(multi-versioned concurrency control)的事务安全(transaction-safe (ACID compliant))型表。

InnoDB 提供了行级锁(locking on row level),提供与 Oracle 类似的不加锁读取(non-locking read in SELECTs)。

InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。


/* ==================== MySQL InnoDB 锁表与锁行 ======================== */

由于InnoDB预设是Row-Level Lock,所以只有「明确」的指定主键,MySQL才会执行Row lock (只锁住被选取的资料例) ,否则MySQL将会执行Table Lock (将整个资料表单给锁住)。

举个例子: 假设有个表单products ,里面有id跟name二个栏位,id是主键。

例1: (明确指定主键,并且有此笔资料,row lock)

复制代码代码如下:
SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

例2: (明确指定主键,若查无此笔资料,无lock)

复制代码代码如下:
SELECT * FROM products WHERE id='-1' FOR UPDATE;

例3: (无主键,table lock)

复制代码代码如下:
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

例4: (主键不明确,table lock)

复制代码代码如下:
SELECT * FROM products WHERE id<>'3' FOR UPDATE;

例5: (主键不明确,table lock)

复制代码代码如下:
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

在MySql 5.0中测试确实是这样的

另外:MyAsim 只支持表级锁,InnoDB支持行级锁
添加了(行级锁/表级锁)锁的数据不能被其它事务再锁定,也不被其它事务修改(修改、删除)
如果是表级锁时,不管是否查询到记录,都会锁定表





0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:3487次
    • 积分:209
    • 等级:
    • 排名:千里之外
    • 原创:17篇
    • 转载:11篇
    • 译文:0篇
    • 评论:1条
    文章存档