innodb锁的几个实验(一)

最新推荐文章于 2024-07-29 10:47:39 发布

名明鸣冥

最新推荐文章于 2024-07-29 10:47:39 发布

阅读量973

点赞数

分类专栏： mysql 文章标签： mysql 锁

本文链接：https://blog.csdn.net/mingover/article/details/80318221

版权

mysql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

排它锁，共享锁的相关实验.

需要用到的语句.


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;

INSERT into picture_tmp SELECT * from picture; – picture 表 S锁

说明加共享锁是用 lock in share mode ,纯粹的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 锁的,只有加上 lock in share mode; 才行

事务一:

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;

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

关于 insert 的锁

insert 是X 锁，但，如果事务二是拿表的S锁的时候会阻塞(即update的where中不走索引)，如果update走索引，则不阻塞.

事务一:

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

附锁的几个查询

show ENGINE INNODB status ; – 这个里面能查到锁相关的信息


=====================================
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
============================