- 浏览: 7845361 次
- 性别:
- 来自: 广州
文章分类
- 全部博客 (2425)
- 软件工程 (75)
- JAVA相关 (662)
- ajax/web相关 (351)
- 数据库相关/oracle (218)
- PHP (147)
- UNIX/LINUX/FREEBSD/solaris (118)
- 音乐探讨 (1)
- 闲话 (11)
- 网络安全等 (21)
- .NET (153)
- ROR和GOG (10)
- [网站分类]4.其他技术区 (181)
- 算法等 (7)
- [随笔分类]SOA (8)
- 收藏区 (71)
- 金融证券 (4)
- [网站分类]5.企业信息化 (3)
- c&c++学习 (1)
- 读书区 (11)
- 其它 (10)
- 收藏夹 (1)
- 设计模式 (1)
- FLEX (14)
- Android (98)
- 软件工程心理学系列 (4)
- HTML5 (6)
- C/C++ (0)
- 数据结构 (0)
- 书评 (3)
- python (17)
- NOSQL (10)
- MYSQL (85)
- java之各类测试 (18)
- nodejs (1)
- JAVA (1)
- neo4j (3)
- VUE (4)
- docker相关 (1)
最新评论
-
xiaobadi:
jacky~~~~~~~~~
推荐两个不错的mybatis GUI生成工具 -
masuweng:
(转)JAVA获得机器码的实现 -
albert0707:
有些扩展名为null
java 7中可以判断文件的contenttype了 -
albert0707:
非常感谢!!!!!!!!!
java 7中可以判断文件的contenttype了 -
zhangle:
https://zhuban.me竹板共享 - 高效便捷的文档 ...
一个不错的网络白板工具
http://www.searchtb.com/2010/09/mysql%e8%a1%8c%e9%94%81%e6%b7%b1%e5%85%a5%e7%a0%94%e7%a9%b6.html
做项目时由于业务逻辑的需要,必须对数据表的一行或多行加入行锁,举个最简单的例子,图书借阅系统。假设id=1的这本书库存为1,但是有2个人同时来借这本书,此处的逻辑为
Select restnum from book where id =1 ; –如果restnum大于0,执行update
Update book set restnum=restnum-1 where id=1;
问题就来了,当2个人同时来借的时候,有可能第一个人执行select语句的时候,第二个人插了进来,在第一个人没来得及更新book表的时候,第二个人查到数据了,其实是脏数据,因为第一个人会把restnum值减1,因此第二个人本来应该是查到id=1的书restnum为0了,因此不会执行update,而会告诉它id=1的书没有库存 了,可是数据库哪懂这些,数据库只负责执行一条条SQL语句,它才不管中间有没有其他sql语句插进来,它也不知道要把一个session的sql语句执行完再执行另一个session的。因此会导致并发的时候restnum最后的结果为-1,显然这是不合理的,所以,才出现锁的概念,Mysql使用innodb引擎可以通过索引对数据行加锁。以上借书的语句变为:
Begin;
Select restnum from book where id =1 for update ; — 给id=1的行加上排它锁且id有索引
Update book set restnum=restnum-1 where ;
Commit;
这样,第二个人执行到select语句的时候就会处于等待状态直到第一个人执行commit。从而保证了第二个人不会读到第一个人修改前的数据。 那这样是不是万无一失了呢,答案是否定的。看下面的例子。
跟我一步一步来,先建立表
CREATE TABLE `book` (
`id` int(11) NOT NULL auto_increment,
`num` int(11) default NULL,
`name` varchar(0) default NULL,
PRIMARY KEY (`id`),
KEY `asd` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
其中num字段加了索引
然后插入数据,运行,
insert into book(num) values(11),(11),(11),(11),(11);
insert into book(num) values(22),(22),(22),(22),(22);
然后打开2个mysql控制台窗口,其实就是建立2个session做并发操作
━━━━━━━━━━━━━━━━
在第一个session里运行:
begin;
select * from book where num=11 for update;
出现结果:
| id | num | name |
| 11 | 11 | NULL |
| 12 | 11 | NULL |
| 13 | 11 | NULL |
| 14 | 11 | NULL |
| 15 | 11 | NULL |
5 rows in set
然后在第二个session里运行:
begin;
select * from book where num=22 for update;
出现结果:
| id | num | name |
| 16 | 22 | NULL |
| 17 | 22 | NULL |
| 18 | 22 | NULL |
| 19 | 22 | NULL |
| 20 | 22 | NULL | 5 rows in set
好了,到这里什么问题都没有,是吧,可是接下来问题就来了,大家请看: 回到第一个session,运行:
update book set name=’abc’ where num=11;
━━━━━━━━━━━━━━━━
问题来了,session竟然处于等待状态,可是num=11的行不是被第一个session自己锁住的么,为什么不能更新呢?好了,打这里大家也许有自己的答案,先别急,再请看一下操作。
把2个session都关闭,然后运行:
delete from book where num=11 limit 3;
delete from book where num=22 limit 3;
其实就是把num=11和22的记录各删去3行, 然后重复“━━━━━━━━”之间的操作 竟然发现,运行update book set name=’abc’ where num=11;后,有结果出现了,说明没有被锁住, 这是为什么呢,难道2行数据和5行数据,对MySQL来说,会产生锁行和锁表两种情况吗。经过跟网友讨论和翻阅资料,仔细分析后发现: 在以上实验数据作为测试数据的情况下,由于num字段重复率太高,只有2个值,分别是11和12.而数据量相对于这两个值来说却是比较大的,是10条,5倍的关系。 那么mysql在解释sql的时候,会忽略索引,因为它的优化器发现:即使使用了索引,还是要做全表扫描,故而放弃了索引,也就没有使用行锁,却使用了表锁。简单的讲,就是MYSQL无视了你的索引,它觉得与其行锁,还不如直接表锁,毕竟它觉得表锁所花的代价比行锁来的小。以上问题即便你使用了force index强制索引,结果还是一样,永远都是表锁。 所以mysql 的行锁用起来并不是那么随心所欲的,必须要考虑索引。再看下面的例子。
select id from items where id in (select id from items where id <6) for update; –id字段加了索引
select id from items where id in (1,2,3,4,5) for update;
大部分会认为结果一样没什么区别,其实差别大了,区别就是第一条sql语句会产生表锁,而第二个sql语句是行锁,为什么呢?因为第一个sql语句用了子查询外围查询故而没使用索引,导致表锁。
好了,回到借书的例子,由于id是唯一的,所以没什么问题,但是如果有些表出现了索引有重复值,并且mysql会强制使用表锁的情况,那怎么办呢?一般来说只有重新设计表结构和用新的SQL语句实现业务逻辑,但是其实上面借书的例子还有一种办法。请看下面代码:
Set sql_mode=
‘STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
Begin;
Select restnum from book where id =1 ; –取消排它锁, 设置restnum为unsigned
Update book set restnum=restnum-1 where ;
If(update执行成功) commit;
Else rollback;
上面是个小技巧,通过把数据库模式临时设置为严格模式,当restnum被更新为-1的时候,由于restnum是unsigned类型的,因此update会执行失败,无论第二个session做了什么数据库操作,都会被回滚,从而确保了数据的正确性,这个目的只是为了防止并发的时候极小概率出现的2个session的sql语句嵌套执行导致数据脏读。当然最好的办法还是修改表结构和sql语句,让MYSQL通过索引来加行锁。 MySQL测试版本为5.0.75-log和5.1.36-community
做项目时由于业务逻辑的需要,必须对数据表的一行或多行加入行锁,举个最简单的例子,图书借阅系统。假设id=1的这本书库存为1,但是有2个人同时来借这本书,此处的逻辑为
Select restnum from book where id =1 ; –如果restnum大于0,执行update
Update book set restnum=restnum-1 where id=1;
问题就来了,当2个人同时来借的时候,有可能第一个人执行select语句的时候,第二个人插了进来,在第一个人没来得及更新book表的时候,第二个人查到数据了,其实是脏数据,因为第一个人会把restnum值减1,因此第二个人本来应该是查到id=1的书restnum为0了,因此不会执行update,而会告诉它id=1的书没有库存 了,可是数据库哪懂这些,数据库只负责执行一条条SQL语句,它才不管中间有没有其他sql语句插进来,它也不知道要把一个session的sql语句执行完再执行另一个session的。因此会导致并发的时候restnum最后的结果为-1,显然这是不合理的,所以,才出现锁的概念,Mysql使用innodb引擎可以通过索引对数据行加锁。以上借书的语句变为:
Begin;
Select restnum from book where id =1 for update ; — 给id=1的行加上排它锁且id有索引
Update book set restnum=restnum-1 where ;
Commit;
这样,第二个人执行到select语句的时候就会处于等待状态直到第一个人执行commit。从而保证了第二个人不会读到第一个人修改前的数据。 那这样是不是万无一失了呢,答案是否定的。看下面的例子。
跟我一步一步来,先建立表
CREATE TABLE `book` (
`id` int(11) NOT NULL auto_increment,
`num` int(11) default NULL,
`name` varchar(0) default NULL,
PRIMARY KEY (`id`),
KEY `asd` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
其中num字段加了索引
然后插入数据,运行,
insert into book(num) values(11),(11),(11),(11),(11);
insert into book(num) values(22),(22),(22),(22),(22);
然后打开2个mysql控制台窗口,其实就是建立2个session做并发操作
━━━━━━━━━━━━━━━━
在第一个session里运行:
begin;
select * from book where num=11 for update;
出现结果:
| id | num | name |
| 11 | 11 | NULL |
| 12 | 11 | NULL |
| 13 | 11 | NULL |
| 14 | 11 | NULL |
| 15 | 11 | NULL |
5 rows in set
然后在第二个session里运行:
begin;
select * from book where num=22 for update;
出现结果:
| id | num | name |
| 16 | 22 | NULL |
| 17 | 22 | NULL |
| 18 | 22 | NULL |
| 19 | 22 | NULL |
| 20 | 22 | NULL | 5 rows in set
好了,到这里什么问题都没有,是吧,可是接下来问题就来了,大家请看: 回到第一个session,运行:
update book set name=’abc’ where num=11;
━━━━━━━━━━━━━━━━
问题来了,session竟然处于等待状态,可是num=11的行不是被第一个session自己锁住的么,为什么不能更新呢?好了,打这里大家也许有自己的答案,先别急,再请看一下操作。
把2个session都关闭,然后运行:
delete from book where num=11 limit 3;
delete from book where num=22 limit 3;
其实就是把num=11和22的记录各删去3行, 然后重复“━━━━━━━━”之间的操作 竟然发现,运行update book set name=’abc’ where num=11;后,有结果出现了,说明没有被锁住, 这是为什么呢,难道2行数据和5行数据,对MySQL来说,会产生锁行和锁表两种情况吗。经过跟网友讨论和翻阅资料,仔细分析后发现: 在以上实验数据作为测试数据的情况下,由于num字段重复率太高,只有2个值,分别是11和12.而数据量相对于这两个值来说却是比较大的,是10条,5倍的关系。 那么mysql在解释sql的时候,会忽略索引,因为它的优化器发现:即使使用了索引,还是要做全表扫描,故而放弃了索引,也就没有使用行锁,却使用了表锁。简单的讲,就是MYSQL无视了你的索引,它觉得与其行锁,还不如直接表锁,毕竟它觉得表锁所花的代价比行锁来的小。以上问题即便你使用了force index强制索引,结果还是一样,永远都是表锁。 所以mysql 的行锁用起来并不是那么随心所欲的,必须要考虑索引。再看下面的例子。
select id from items where id in (select id from items where id <6) for update; –id字段加了索引
select id from items where id in (1,2,3,4,5) for update;
大部分会认为结果一样没什么区别,其实差别大了,区别就是第一条sql语句会产生表锁,而第二个sql语句是行锁,为什么呢?因为第一个sql语句用了子查询外围查询故而没使用索引,导致表锁。
好了,回到借书的例子,由于id是唯一的,所以没什么问题,但是如果有些表出现了索引有重复值,并且mysql会强制使用表锁的情况,那怎么办呢?一般来说只有重新设计表结构和用新的SQL语句实现业务逻辑,但是其实上面借书的例子还有一种办法。请看下面代码:
Set sql_mode=
‘STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
Begin;
Select restnum from book where id =1 ; –取消排它锁, 设置restnum为unsigned
Update book set restnum=restnum-1 where ;
If(update执行成功) commit;
Else rollback;
上面是个小技巧,通过把数据库模式临时设置为严格模式,当restnum被更新为-1的时候,由于restnum是unsigned类型的,因此update会执行失败,无论第二个session做了什么数据库操作,都会被回滚,从而确保了数据的正确性,这个目的只是为了防止并发的时候极小概率出现的2个session的sql语句嵌套执行导致数据脏读。当然最好的办法还是修改表结构和sql语句,让MYSQL通过索引来加行锁。 MySQL测试版本为5.0.75-log和5.1.36-community
发表评论
-
mybatis generator中的字段大小写生成问题
2017-10-22 19:35 11241mybatis generator插件中,如果 mysql数据 ... -
MySQL统计一个列中不同值的数量
2017-07-11 14:04 16227https://yiqiwuliao.com/post/mys ... -
mysql 1449 : The user specified as a definer ('root'@'%') does not exist 解决方法
2017-03-02 09:44 1724权限问题,授权 给 root 所有sql 权限 mysql ... -
几个不错的MYSQL 优化TIPS
2016-11-19 12:30 764图片来自http://imysql.com/的PDF分享 [ ... -
powerdesign 逆向ORACLE的坑
2016-09-27 17:08 522要注意的是,在WIN 64中,如果要用powerdesin 来 ... -
( 转)mysql中删除两条重复记录中的一条
2015-10-19 20:19 1293| id | createTime | labId | pub ... -
mysql中sql语句=,>的时候的索引设置
2015-05-02 12:27 1280在 https://www.percona.com/blog/ ... -
mysql中profile的使用
2015-04-30 11:11 2184mysql 的 sql 性能分析器主要用途是显示 sql 执行 ... -
mysql中的sql mode
2015-04-13 11:29 1109mysql sql mode小结 1 sql mode可以 ... -
PROCEDURE ANALYSE()为mysql提高性能提供建议
2015-04-02 16:37 1404procedure analyse();语法如下 select ... -
mongodb中意外退出的问题
2015-02-11 14:32 1309mongodb启动的时候,意外退出: Unclean shut ... -
mysql 5.5中保留字查询
2014-06-05 23:16 1451mysql 5.5中保留字查询 http://www.5is ... -
(转)oracle 临时表空间的增删改查
2014-03-18 12:44 1097oracle 临时表空间的增删改查 1、查看临时表空间 (d ... -
pl-sql developer安装
2014-02-07 09:16 1092一直都是机器本机上有oracle,所以装pl sql deve ... -
oracle中的nvl,nvl2等参数
2013-12-26 11:18 22401.nul函数将一个null值转换为一个实际的值。 数据类型可 ... -
Oracle中的ROWNUM rowid 以及MySQL中实现rownum功能类似的语句
2013-12-26 11:08 2320http://gong-10140.iteye.com/blo ... -
ORACLE XE版本的限制
2013-12-07 08:11 360310g中用户数据最大为4G, 11G中最大为11G,如果超出大 ... -
oracle中监控索引是否可用
2013-11-04 07:22 1034在oracle中,可以使用如下的方法监控索引是否可用: a ... -
mysql 5.6中的时间类型的新精度介绍
2013-10-28 09:33 8369留意到mysql 5.6中,可以使用select now(6) ... -
<<oracle索引技术》读书笔记1
2013-08-25 16:23 1288expert indexing in oracle datab ...
相关推荐
mysql索引和锁机制,简单介绍的ppt,很详细的内容
关于mysql中的innodb存储引擎的索引机制简介,详细介绍了锁机制
什么是索引,index 索引:是帮助高效获取数据的数据结构 索引也可能是一个文件 那么多存储结构 hash map 红黑树 二叉树等 为什么mysql底层要用B+ tree实现?
MySQL索引 聚集索引 如果你想了解MySQL索引查询优化,你首先应该对MySQL数据组织结构、B-Tree索引、聚集索引,次要索引有一定的了解,才能够更好地理解MySQL查询优化行为。这里主要探讨MySQL InnoDB的聚集索引。
mysql索引的设计和使用
介绍mysql不同的存储引擎 以及 索引技术在mysql中的应用
MySQl索引优化课件,详细得介绍了MySQl索引优化数据库
MySQL 索引最佳实践很不错,虽然是英文版
7月6日 MySQL索引篇7月6日 MySQL索引篇7月6日 MySQL索引篇7月6日 MySQL索引篇7月6日 MySQL索引篇7月6日 MySQL索引篇7月6日 MySQL索引篇7月6日 MySQL索引篇7月6日 MySQL索引篇7月6日 MySQL索引篇7月6日 MySQL索引篇7...
MySQL Innodb 索引原理详解
MySQL索引类型大汇总 mysql里需要用到了
MySQL索引分析和优化.pdf 记录自己在学习过程中看过的书欢迎下载
《MySQL索引原理及如何建立高效索引.pptx》主要讲述mysql数据库索引底层原理、作用、 索引使用、索引失效等核心技术点。非常实用!!!
由浅入深探究mysql索引结构原理、性能分析与优化
1. 索引与执行计划 1 1.1. 索引入门 1 1.1.1. 索引是什么 1 1.1.2. 索引得分类 3 1.1.3. 基础语法 3 1.2. 执行计划 3 1.2.1. 什么是执行计划 3 1.2.2. 执行计划的作用 3 1.2.3. 执行计划的语法 4 1.2.4. 执行计划...
讲座的一个ppt分享 1. MySQL的索引类型选择 2. MySQL数据结构分析 3. MySQL的引擎类型分析
05-VIP-Mysql索引优化实战二.pdf
04-VIP-Mysql索引优化实战一.pdf
mysql索引导出删除