小工具      在线工具  汉语词典  css  js  c++  java

innodb锁的几个实验(一)

mysql,mysql,锁 额外说明

收录于:40天前

排他锁和共享锁的相关实验。

需要用到的语句。


SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for picture
-- ----------------------------
DROP TABLE IF EXISTS `picture`;
CREATE TABLE `picture` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pic_name` varchar(255) DEFAULT '' COMMENT '图片名称', `synopsis` varchar(255) DEFAULT NULL COMMENT '简介', `path` varchar(255) DEFAULT NULL COMMENT '图片存储路径', `credate` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of picture
-- ----------------------------
INSERT INTO `picture` VALUES ('38', 'd16f1767f56e4519b3091510e6074d3a.jpeg', '2', '/images/d16f1767f56e4519b3091510e6074d3a.jpeg', '2018-05-14 22:03:00');
INSERT INTO `picture` VALUES ('42', '585727e8d80843fc8e5c66e2f8d34b93.jpeg', null, '/images/585727e8d80843fc8e5c66e2f8d34b93.jpeg', '2018-02-28 12:14:22');
INSERT INTO `picture` VALUES ('43', 'a387e46133e04f23b516be5dd2c54f2a.jpeg', null, '/images/a387e46133e04f23b516be5dd2c54f2a.jpeg', '2018-02-28 12:17:01');
INSERT INTO `picture` VALUES ('44', 'e019ceea061e403dbaec0a6605651384.jpeg', null, '/images/e019ceea061e403dbaec0a6605651384.jpeg', '2018-02-28 12:17:01');
INSERT INTO `picture` VALUES ('45', '8bc31a780fbe44d494b02fad3de66ead.jpeg', null, '/images/8bc31a780fbe44d494b02fad3de66ead.jpeg', '2018-02-28 12:17:01');
INSERT INTO `picture` VALUES ('46', '88fed805918f4183a7eb9d49ace38111.jpeg', null, '/images/88fed805918f4183a7eb9d49ace38111.jpeg', '2018-02-28 12:17:47');

如何获取排它锁和共享锁

专属锁:

SELECT * from picture for update;
update ….
delete ….
insert … (insert有点特殊,insert 是X 锁,但,如果事务二是 拿表的S锁的时候会阻塞(即update的where中不走索引),如果update走索引,则不阻塞.)

共享锁:
SELECT * from picture lock in share mode;

插入图片_tmp 从图片中选择*; – 图片桌S锁

注意:在共享模式下使用lock来添加共享锁。纯 select 不加锁,并且使用一致的非锁定读取来处理查询。

一些基础知识,

InnoDB 实现了以下两种类型的行锁。

共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。另外,为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁。
意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。
这里写图片描述

没有索引,使用表锁而不是行锁

InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!
查看其是否锁一张表的实验:

请注意,以下模拟了事务一和事务二之间的冲突。一般是执行事务一但未提交,然后执行事务二,看看事务二的关键SQL是否在等待。

业务一:

set autocommit = 0;
start TRANSACTION ;
-- synopsis 并没有 索引,是否会锁全表?
UPDATE picture set synopsis = '1' where synopsis = '2'; -- 执行完这一行后,去执行事务二

COMMIT;

业务二:

set autocommit = 0;
start TRANSACTION ;
UPDATE picture set synopsis = '42' where id = 42; -- id为42的数据并不是上面的数据

COMMIT;

这里写图片描述

这里写图片描述
这里写图片描述

结论: 上面事务一和事务二没有对同一条数据进行加锁,但事务二仍然在等待,说明innodb在未索引字段的情况下是索全表的.
用索引字段来处理,如果不针对同一条数据,则不会阻塞

2.默认,count有没有锁的?
采用上面的事务一和事务二,事务二改为select count(1) from picture , 没有锁.

3 关于表中X与S的冲突。

注意 select * from picture 无法获得 s 锁。只能在共享模式下加锁;

业务一:

set autocommit = 0;

start TRANSACTION ;

SELECT * from picture where id=38 for update; -- 锁住38行

COMMIT;

业务二:

set autocommit = 0;

start TRANSACTION ;

UPDATE picture set synopsis = '3' where id = 38;

COMMIT;

锁的情况如下:
这里写图片描述

关于插入锁

插入是一个

业务一:

set autocommit = 0;

start TRANSACTION ;

INSERT INTO `snmdb`.`picture` ( `pic_name`, `synopsis`, `path`, `credate`) VALUES ('88fed805918f4183a7eb9d49ace38111.jpeg', NULL, '/images/88fed805918f4183a7eb9d49ace38111.jpeg', '2018-02-28 12:17:47');


COMMIT;

业务二:

set autocommit = 0;

        start TRANSACTION ;

        UPDATE picture set synopsis = '31' where id = 38;
        -- UPDATE picture set synopsis = '31' where synopsis = '31'; 加表锁

COMMIT;

关于上面的insert ,当事务二锁整张表时(X或S),会阻塞,但一条数据的锁是不会的
又做了几个实验,感觉insert 在锁的查看中,发现是X 并且是锁了全部数据. 但我在事务二中用 where id =20 for update,又不会阻塞… 这个需要后续研究下.

一致的非锁定读

行多版本将的是innodb为每个行记录存储了多个版本, 在可重复读的事务隔离级别 的表现 为,在事务一对目标数据更新但未提交的情况下,事务二查询到的是事务二开始事务时的快照。
具体的可直接参考各大现成的博文。

附加到锁的几个查询

显示 ENGINE INNODB 状态; – 锁相关的信息可以在这里找到


=====================================
2018-03-23 16:42:47 0x7f082a3d0700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 52 seconds -----------------
BACKGROUND THREAD -----------------
srv_master_thread loops: 2113 srv_active, 0 srv_shutdown, 1988005 srv_idle srv_master_thread log flush and writes: 1990118 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 2925775490 --Thread 139673019549440 has waited at btr0sea.cc line 1204 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x326c518 created in file btr0sea.cc line 195 number of readers 0, waiters flag 0, lock_word: 20000000
Last time read locked in file btr0sea.ic line 128
Last time write locked in file /export/home/pb2/build/sb_0-24964902-1505322971.88/mysql-5.7.20/storage/innobase/include/btr0sea.ic line 90 OS WAIT ARRAY INFO: signal count 5002421504 RW-shared spins 0, rounds 4743473258, OS waits 1948118418 RW-excl spins 0, rounds 30204677083, OS waits 942368095 RW-sx spins 19718, rounds 418146, OS waits 5580 Spin rounds per wait: 4743473258.00 RW-shared, 30204677083.00 RW-excl, 21.21 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 3630767 Purge done for trx's n:o < 3630765 undo n:o < 0 state: running but idle History list length 39 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421148999013792, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421148999012880, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421148999003760, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421148999001936, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421148999014704, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421148999010144, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421148999007408, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421148999006496, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421148999005584, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421148999009232, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421148999011056, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 3630766, ACTIVE 12 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 983062, OS thread handle 139669376751360, query id 11126445 192.168.138.249 root Sending data select * from dsm_plat t where t.login_name = 'oZQ34t3k9VNrvKcV-Prf72t-L49w' for update ------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 510 page no 9 n bits 112 index id of table `boss_test`.`dsm_plat` trx id 3630766 lock_mode X waiting
Record lock, heap no 2 ------------------
---TRANSACTION 3630727, ACTIVE 892 sec
7 lock struct(s), heap size 1136, 192 row lock(s)
MySQL thread id 983233, OS thread handle 139673052829440, query id 11122672 192.168.138.249 root --------
FILE I/O --------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
4387880542 OS file reads, 156401 OS file writes, 54011 OS fsyncs
2997.06 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s -------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX -------------------------------------
Ibuf: size 1, free list len 3093, seg size 3095, 2419 merges
merged operations:
insert 37407, delete mark 41817, delete 30
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34673, node heap has 560 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
114843.18 hash searches/s, 75044.77 non-hash searches/s ---
LOG ---
Log sequence number 123124136289
Log flushed up to   123124136289
Pages flushed up to 123124136289
Last checkpoint at  123124136280
0 pending log flushes, 0 pending chkp writes
38029 log i/o's done, 0.00 log i/o's/second ----------------------
BUFFER POOL AND MEMORY ----------------------
Total large memory allocated 137428992
Dictionary memory allocated 1833884
Buffer pool size   8191
Free buffers       0
Database pages     7624
Old database pages 2825
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 23773193, not young 762364603040
0.04 youngs/s, 541681.99 non-youngs/s
Pages read 4387878703, created 74530, written 102620
2997.06 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 996 / 1000, young-making rate 0 / 1000 not 839 / 1000
Pages read ahead 2764.25/s, evicted without access 20.65/s, Random read ahead 0.00/s
LRU len: 7624, unzip_LRU len: 0 I/O sum[18850]:cur[1], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 2 read views open inside InnoDB Process ID=5545, Main thread ID=139673653462784, state: sleeping Number of rows inserted 2914934, updated 2107, deleted 71778, read 265487856749 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 190484.41 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 

– 执行的事务
SELECT * FROM information_schema.INNODB_TRX;

– .3 锁等待和持有锁的相互关系
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

– 事务的锁
SELECT * FROM information_schema.INNODB_LOCKS;

参考

. . .

相关推荐

额外说明

mysql 错误:无法选择数据库。

问题 PS E:\node-exclude\mysql5.7\mysql-5.7.42-winx64\bin> mysql_upgrade -u root -p --force Enter password: **** Error occurred: C

额外说明

第六篇 使用ORDER BY排序和展示数据

《SQL 从入门到精通》专栏目录 第 01 篇 和数据打交道的你,一定要学会 SQL 第 02 篇 在 SQL 的世界里一切都是关系 第 03 篇 使用 SELECT 语句初步探索数据库 第 04 篇 通过查询条件实现数据过滤 第 05 篇 如何使用 S

额外说明

SQL Server 中 REPLACE() 函数简介

SQL Server 中 replace() 函数简介 SQL Server REPLACE 函数语法: 要使用新字符串替换字符串所有出现的子字符串,请使用REPLACE()函数,如下所示: REPLACE(字段,‘wddw’,'wwww') 参数解析

额外说明

围棋基础知识巩固2

make和new的区别:都是用来初始化的 new多用来为基本数据类型初始化内存,返回的是指针 make用来初始化slince,map,channel,返回的是对应的类型 无缓冲通道和有缓冲通道区别:无缓冲通道传值得时候必须要有接收方来接收通道,否则报错;

额外说明

JavaScript —— JSchallenger Arrays 数组练习(测试一下你的 JS 数组基础)【专题二】

文章目录 二、JavaScript 数组练习 1. 返回数组 a 的第 n 个元素 2. 删除数组 a 的前三个元素,并返回结果 3. 提取数组 a 的后三个元素,并返回结果 4. 提取数组 a 的前三个元素,并返回结果 5. 以数组 a 和 数字 n

额外说明

PyTorch对WGAN(Wasserstein生成对抗网络)的实现

        生成对抗网络(GAN)的用途非常广泛,可以“无中生有”图片,人物动漫头像,去掉场景中的雨、黑白转彩色的图片与视频、视频预测、2D推导3D等等,对于Goodfellow的封神之作,大家有兴趣的可以阅读:Generative Adversar

额外说明

Java Web实训项目:西蒙购物网(下)

文章目录 四、实现步骤 (九)准备图片资源 (十)CSS样式文件 (十一)JavaScript脚本文件 (十二)添加JSTL的jar包 (十三)展现层页面(XXX.jsp) 1、登录页面login.jsp 2、注册页面register.jsp 3、显示商

额外说明

linux如何对外开放端口号,防火墙

当我们在 Linux 上运行服务时,例如网站、数据库等,我们通常需要将服务所监听的端口开放,以便外部网络可以通过该端口访问该服务。本篇博客将介绍如何在 Linux 上对外开放端口号。 什么是端口号 在计算机网络中,端口是一种虚拟的通信机制,用于区分不同的

额外说明

Java实现SM3加密和验证

在商用密码体系中,SM3主要用于数字签名及验证、消息认证码生成及验证、随机数生成等,其算法公开。据国家密码管理局表示,其安全性及效率与SHA-256相当。 需要引入如下坐标依赖: <dependency> <groupId>org.bouncyc

额外说明

Spring MVC 集成MongoDB的两种方式

根据我上一篇文章,首先设置并启动MongoDB服务器,然后按照以下步骤处理数据。 Mongo可以通过以下两种方式处理数据: 1.使用ArticleRepository进行数据的增删改查 查询数据 http://localhost:8080/springM

ads via 小工具