精华内容
下载资源
问答
  • 这里的锁就是独占锁,也就是X锁。那么如果有查询这一行数据时,是要加锁吗?不是的,mysql为了提高性能,读的时候使用mvcc机制,通过ReadView,查询undo log版本链,获取到自己能查询到的版本...

    当有多个事务同时更新一条sql时,mysql是如何处理的呢?很显然,使用加锁的方式,一个事务获得了锁,进行操作,其他事务排队一个个等着,等当前这个事务执行完释放锁,其他事务获取锁取到锁的进行操作。这里的锁就是独占锁,也就是X锁。

    那么如果有查询这一行数据时,是要加锁吗?不是的,mysql为了提高性能,读的时候使用mvcc机制,通过ReadView,查询undo log版本链,获取到自己能查询到的版本数据。读与更新同时进行,不互斥,性能就提高了。

    共享锁又是什么呢?mysql里叫共享锁为S锁,通 过手动执行select * from table lock in share mode来加共享锁。加了共享锁,还能加独占锁吗?不能的,共享锁和独占锁是互斥的。不过共享锁和共享锁不互斥,也就是加了一个共享锁,还可以再加另一个共享锁。

    独占锁

    共享锁

    独占锁

    互斥

    互斥

    共享锁

    互斥

    不互斥

    但平时我们是很少去手动加共享锁的,一般更新时加独占锁就够了。 另外执行select * from table for update也会加独占锁。

    原文:https://www.cnblogs.com/ITyannic/p/12907721.html

    展开全文
  • Key(键值) – 上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。 CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR...

    91fdf8f64a71bdf60c6bb319dc0a7354.png

    分区后 会产生多个 数据存储文件MYD,MYI ,把内容读取分散到多个文件上,这样减少并发读取,文件锁的概率,提高IO

    === 水平分区的几种模式:===

    1. Range(范围) – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区,80年代(1980's)的数据,90年代(1990's)的数据以及任何在2000年(包括2000年)后的数据。

    CREATE TABLE users (

    uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(30) NOT NULL DEFAULT '',

    email VARCHAR(30) NOT NULL DEFAULT ''

    )

    PARTITION BY RANGE (uid) (

    PARTITION p0 VALUES LESS THAN (3000000)

    DATA DIRECTORY = '/data0/data'

    INDEX DIRECTORY = '/data1/idx',

    PARTITION p1 VALUES LESS THAN (6000000)

    DATA DIRECTORY = '/data2/data'

    INDEX DIRECTORY = '/data3/idx',

    PARTITION p2 VALUES LESS THAN (9000000)

    DATA DIRECTORY = '/data4/data'

    INDEX DIRECTORY = '/data5/idx',

    PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/data6/data'

    INDEX DIRECTORY = '/data7/idx'

    );

    在这里,将用户表分成4个分区,以每300万条记录为界限,每个分区都有自己独立的数据、索引文件的存放目录,与此同时,这些目录所在的物理磁盘分区可能也都是完全独立的,可以提高磁盘IO吞吐量。

    2.Hash(哈希) – 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区,。例如DBA可以建立一个对表主键进行分区的表。

    CREATE TABLE users (

    uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(30) NOT NULL DEFAULT '',

    email VARCHAR(30) NOT NULL DEFAULT ''

    )

    PARTITION BY HASH (uid) PARTITIONS 4 (

    PARTITION p0

    DATA DIRECTORY = '/data0/data'

    INDEX DIRECTORY = '/data1/idx',

    PARTITION p1

    DATA DIRECTORY = '/data2/data'

    INDEX DIRECTORY = '/data3/idx',

    PARTITION p2

    DATA DIRECTORY = '/data4/data'

    INDEX DIRECTORY = '/data5/idx',

    PARTITION p3

    DATA DIRECTORY = '/data6/data'

    INDEX DIRECTORY = '/data7/idx'

    );

    分成4个区,数据文件和索引文件单独存放。

    3. Key(键值) – 上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。

    CREATE TABLE users (

    uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(30) NOT NULL DEFAULT '',

    email VARCHAR(30) NOT NULL DEFAULT ''

    )

    PARTITION BY KEY (uid) PARTITIONS 4 (

    PARTITION p0

    DATA DIRECTORY = '/data0/data'

    INDEX DIRECTORY = '/data1/idx',

    PARTITION p1

    DATA DIRECTORY = '/data2/data'

    INDEX DIRECTORY = '/data3/idx',

    PARTITION p2

    DATA DIRECTORY = '/data4/data'

    INDEX DIRECTORY = '/data5/idx',

    PARTITION p3

    DATA DIRECTORY = '/data6/data'

    INDEX DIRECTORY = '/data7/idx'

    )

    分成4个区,数据文件和索引文件单独存放。

    4.List(预定义列表) – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如:DBA建立了一个横跨三个分区的表,分别根据2004年2005年和2006年值所对应的数据。

    CREATE TABLE category (

    cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(30) NOT NULL DEFAULT ''

    )

    PARTITION BY LIST (cid) (

    PARTITION p0 VALUES IN (0,4,8,12)

    DATA DIRECTORY = '/data0/data'

    INDEX DIRECTORY = '/data1/idx',

    PARTITION p1 VALUES IN (1,5,9,13)

    DATA DIRECTORY = '/data2/data'

    INDEX DIRECTORY = '/data3/idx',

    PARTITION p2 VALUES IN (2,6,10,14)

    DATA DIRECTORY = '/data4/data'

    INDEX DIRECTORY = '/data5/idx',

    PARTITION p3 VALUES IN (3,7,11,15)

    DATA DIRECTORY = '/data6/data'

    INDEX DIRECTORY = '/data7/idx'

    );

    分成4个区,数据文件和索引文件单独存放。

    5. Composite(复合模式) - 很神秘吧,哈哈,其实是以上模式的组合使用而已,就不解释了。举例:在初始化已经进行了Range范围分区的表上,我们可以对其中一个分区再进行hash哈希分区。

    = 垂直分区(按列分)=

    举个简单例子:一个包含了大text和BLOB列的表,这些text和BLOB列又不经常被访问,这时候就要把这些不经常使用的text和BLOB了划分到另一个分区,在保证它们数据相关性的同时还能提高访问速度。

    6.* 子分区

    子分区是针对 RANGE/LIST 类型的分区表中每个分区的再次分割。再次分割可以是 HASH/KEY 等类型。例如:

    CREATE TABLE users (

    uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(30) NOT NULL DEFAULT '',

    email VARCHAR(30) NOT NULL DEFAULT ''

    )

    PARTITION BY RANGE (uid) SUBPARTITION BY HASH (uid % 4) SUBPARTITIONS 2(

    PARTITION p0 VALUES LESS THAN (3000000)

    DATA DIRECTORY = '/data0/data'

    INDEX DIRECTORY = '/data1/idx',

    PARTITION p1 VALUES LESS THAN (6000000)

    DATA DIRECTORY = '/data2/data'

    INDEX DIRECTORY = '/data3/idx'

    ); 对 RANGE 分区再次进行子分区划分,子分区采用 HASH 类型。

    或者

    CREATE TABLE users (

    uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(30) NOT NULL DEFAULT '',

    email VARCHAR(30) NOT NULL DEFAULT ''

    )

    PARTITION BY RANGE (uid) SUBPARTITION BY KEY(uid) SUBPARTITIONS 2(

    PARTITION p0 VALUES LESS THAN (3000000)

    DATA DIRECTORY = '/data0/data'

    INDEX DIRECTORY = '/data1/idx',

    PARTITION p1 VALUES LESS THAN (6000000)

    DATA DIRECTORY = '/data2/data'

    INDEX DIRECTORY = '/data3/idx'

    ); 对 RANGE 分区再次进行子分区划分,子分区采用 KEY 类型。

    = 分区管理 == 分区管理 == 分区管理 == 分区管理 =

    1.删除分区  ALERT TABLE users DROP PARTITION p0;   删除分区 p0。

    2.重建分区

    RANGE 分区重建

    ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));

    将原来的 p0,p1 分区合并起来,放到新的 p0 分区中。

    LIST 分区重建

    ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));

    将原来的 p0,p1 分区合并起来,放到新的 p0 分区中。

    HASH/KEY 分区重建

    ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;

    用 REORGANIZE 方式重建分区的数量变成2,在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。

    3.新增分区

    新增 RANGE 分区

    ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)

    DATA DIRECTORY = '/data8/data'

    INDEX DIRECTORY = '/data9/idx');

    新增一个RANGE分区。

    新增 HASH/KEY 分区

    ALTER TABLE users ADD PARTITION PARTITIONS 8;

    将分区总数扩展到8个。

    4.给已有的表加上分区

    alter table results partition by RANGE (month(ttime))

    (PARTITION p0 VALUES LESS THAN (1),

    PARTITION p1 VALUES LESS THAN (2) , PARTITION p2 VALUES LESS THAN (3) ,

    PARTITION p3 VALUES LESS THAN (4) , PARTITION p4 VALUES LESS THAN (5) ,

    PARTITION p5 VALUES LESS THAN (6) , PARTITION p6 VALUES LESS THAN (7) ,

    PARTITION p7 VALUES LESS THAN (8) , PARTITION p8 VALUES LESS THAN (9) ,

    PARTITION p9 VALUES LESS THAN (10) , PARTITION p10 VALUES LESS THAN (11),

    PARTITION p11 VALUES LESS THAN (12),

    PARTITION P12 VALUES LESS THAN (13) );

    默认分区限制分区字段必须是主键(PRIMARY KEY)的一部分,为了去除此

    http://blog.csdn.net/tjcyjd/article/details/11194489

    展开全文
  • 1 MySQL锁概述锁是计算机协调多个进程或线程并发访问某一资源的机制。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。相对其他数据库而言...

    719f8033e7c5ac816b7e866ccb701bc0.png

    1 MySQL锁概述

    锁是计算机协调多个进程或线程并发访问某一资源的机制。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。

    相对其他数据库而言,MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。MyISAM和MEMORY存储引擎采用的是表级锁,InnoDB存储引擎既支持行级锁,也支持表级锁。

    • 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
    • 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

    2 MyISAM表锁

    MySQL的表级锁有两种模式:表共享读锁表独占写锁

    对MyISAM表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;对 MyISAM表的写操作,则会阻塞其他用户对同一表的读和写操作。

    MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作 (UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预。

    MyISAM存储引擎的读锁和写锁是互斥的,读写操作是串行的,但是MySQL认为写请求一般比读请求要重要,即使读请求先到锁等待队列,写请求后到,写锁也会插到读锁请求之前,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。不过可以人为修改设置来调节MyISAM 的调度行为。

    3 InnoDB锁

    InnoDB与MyISAM的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁。

    • 共享锁(s):又称读锁。允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
    • 排他锁(X):又称写锁。允许获取排他锁的事务更新数据,阻止其他事务取得相同的数据集共享读锁和排他写锁。

    mysql InnoDB引擎默认的修改数据语句:update,delete,insert都会自动给涉及到的数据加上排他锁,select语句默认不会加任何锁类型,如果加排他锁可以使用select …for update语句,加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的,也不能通过for update和lock in share mode锁的方式查询数据,但可以直接通过select …from…查询数据,因为普通查询没有任何锁机制。

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

    当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁

    4 死锁产生的场景

    数据库是一个多用户使用的共享资源,在实际应用中,如果两个事务需要一组有冲突的锁,而不能继续进行下去,这时便发生了死锁。

    死锁出现的常见原因

    • 事务之间对资源访问顺序的交替

    此类常见于两个用户分别首先访问A表和B表,并锁住当前表,但是双方都需要访问对方锁住的表,都在等待对方释放锁,也就是经典的刀叉问题。像这样的死锁,基本上就是程序员的编程逻辑出现了问题,需要调整程序的逻辑。

    • 并发修改同一记录

    此类常见于用户A查询一条纪录,然后修改该条纪录;这时用户B修改该条纪录,这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁,而用户B里的独占锁由于A有共享锁存在所以必须等A释放掉共享锁,而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁,于是出现了死锁。

    • 索引不当导致全表扫描

    此类常见于在事务中执行了一条不满足的语句,执行全表扫描,把行级锁上升为表级锁,多个这样的事务执行之后,就很容易产生死锁和阻塞。解决其办法有,SQL语句中不要使用太复杂的关联多表的查询;使用“执行计划”对SQL语句进行分析,对于有全表扫描的SQL语句,建立相应的索引进行优化。

    • 事务封锁范围大且相互等待

    在同一数据库中并发执行多个需要长时间运行的事务时通常发生死锁。事务运行时间越长,其持有排它锁或更新锁的时间也就越长,从而堵塞了其它活动并可能导致死锁。

    死锁查询和解决方法

    • 查询是否锁表,show OPEN TABLES where In_use > 0;
    • 查询进程,show processlist
    • 杀死进程id(就是上面命令的id列),kill id

    或者

    • 查看下在锁的事务,SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
    • 杀死进程id(就是上面命令的trx_mysql_thread_id列),kill 线程ID

    5 悲观锁和乐观锁

    • 概念区别

    悲观锁:正如其名字一样,悲观锁对数据加锁持有一种悲观的态度。因此,在整个数据处理过程中,将数据处于锁定状态。悲观锁的实现,往往依靠数据库提供的锁机制(也只有数据库层提供的锁机制才能真正保证数据访问的排他性,否则,即使在本系统中实现了加锁机制,也无法保证外部系统不会修改数据)。

    乐观锁:顾名思义,对加锁持有一种乐观的态度,即先进行业务操作,不到最后一步不进行加锁,"乐观"的认为加锁一定会成功的,在最后一步更新数据的时候再进行加锁。

    • 实现方式

    乐观锁:

    常用version方式:一般是在数据表中加上一个数据版本号version字段,表示数据被修改的次数,当数据被修改时,version值会加一。当线程A要更新数据值时,在读取数据的同时也会读取version值,在提交更新时,若刚才读取到的version值为当前数据库中的version值相等时才更新,否则重试更新操作,直到更新成功。乐观锁的实现全部依靠业务系统自身的逻辑控制,即CAS操作方式。

    update table set x=#{x}, version=version+1 where id=#{id} and version=#{version}; 

    悲观锁:

    是由数据库自己实现了的,要用的时候,我们直接调用数据库的相关语句就可以了(原理:共享资源每次只给一个线程使用,其它线程阻塞,用完后再把资源转让给其它线程),如行锁、读锁和写锁等,都是在操作之前加锁,在Java中,synchronized的思想也是悲观锁。

    • 使用场景

    乐观锁:比较适合读取操作比较频繁的场景,如果出现大量的写入操作,数据发生冲突的可能性就会增大,为了保证数据的一致性,应用层需要不断的重新获取数据,这样会增加大量的查询操作,降低了系统的吞吐量。

    悲观锁:比较适合写入操作比较频繁的场景,悲观锁的特点是先获取锁,再进行业务操作,即“悲观”的认为获取锁是非常有可能失败的,因此要先确保获取锁成功再进行业务操作。通常所说的“一锁二查三更新”即指的是使用悲观锁。

    展开全文
  • MySQL系列:MySQL(一)、InnoDB索引原理及优化MySQL(二)、事务简介锁是计算机用以协调多个进程间并发访问同一共享资源的一种机制。MySQL中为了保证数据访问的一致性与有效性等功能,实现了锁机制,MySQL中的锁是在...

    MySQL系列:

    MySQL(一)、InnoDB索引原理及优化

    MySQL(二)、事务


    简介

    锁是计算机用以协调多个进程间并发访问同一共享资源的一种机制。MySQL中为了保证数据访问的一致性与有效性等功能,实现了锁机制,MySQL中的锁是在服务器层或者存储引擎层实现的。

    锁级别

    MySQL引擎默认的锁级别

    • MyISAM:表级锁;

    • Memory:表级锁;

    • InnoDB:支持行级锁和表级锁 ,默认为行级锁。

    • BDB:页面锁或表级锁,默认为页面锁。(MySQL5.1之后弃用)

    (在后面的文章中会详细对比MySQL的常用引擎,下面对MySQL支持的锁进行介绍)

    全局锁

    对整个数据库实例加锁,加锁后整个库处于只读状态的时候,之后其他线程的以下语句会被阻塞:DML(增删改)、DDL、事务提交。不会影响DQL(查询请求),从而获取一致性视图,保证数据的完整性。

    全局锁:https://dev.mysql.com/doc/refman/8.0/en/lock-instance-for-backup.html

    全局锁命令:

    -- 加全局锁 (FTWRL)flush tables with read lock; -- 释放全局锁unlock tables;或断开加全局锁的会话连接

    错误信息:[Error Code: 1223, SQL State: HY000]  Can't execute the query because you have a conflicting read lock 

    18aef6ec37cef264901e3e7db21d6ced.png

    全局锁的场景是全库备份,但前面也介绍了,全局锁的时候库是只读状态。此时如果在主库加全局锁进行备份,业务就会停摆,如果在从库加全局锁,就无法同步主库的操作导致主从延迟。其实在实际应用中我们可以对不同的存储引擎使用不同的策略备份。

    使用mysqldump工具备份

    • InnoDB引擎:利用MVCC提供一致性视图,开启一个RR级别的事务,保证备份过程中数据可以正常更新,dump对应参数为:--single-transaction

    • MyISAM引擎:利用FTWRL加全局锁,dump对应参数为:--lock-all-tables

    表级锁

    表级锁(Table-level Locking):表示对当前操作的整张表加锁,它实现简单,资源消耗较少,被大部分 MySQL 引擎支持;

    • 优点:开销小,加锁快;不会出现死锁;

    • 缺点:锁定粒度大,发出锁冲突的概率最高,并发度最低。

    表级锁:https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html

    表锁命令:

    -- 加表锁lock tablesread/write; -- 释放表锁unlock tables;或断开加表锁的会话连接

    错误信息:[Error Code: 1099, SQL State: HY000]  Table 'csdn' was locked with a READ lock and can't be updated 

    表读锁: 

    63b177152f5d856016911e6ee1c4c1f9.png

    表写锁: 

    0729a1f6c30148ad347c1f0e15fd5ffb.png

    元数据锁(Metadata Locking):MySQL5.5之后新增,MDL也是表级别的锁,不需要显示的使用,当对一个表做增删改查操作的时候,加 MDL 读锁;当要对表做结构变更操作的时候,加 MDL 写锁。

    • 读锁之间不互斥,可以有多个线程同时对一张表增删改查;

    • 读写锁之间、写锁之间是互斥的,用来保证表结构变更操作的安全性。如果有两个线程要同时给一个表加字段,其中一个要阻塞等待另一个执行完才能开始执行。

    页级锁

    页级锁(Page-level Locking):锁定表中某些行集合(称做页),被锁定的行只对锁定最初的线程是可行。如果另外一个线程想要向这些行写数据,它必须等到锁被释放。不过其他页的行仍然可以使用。在MySQL5.1之前BDB引擎默认页级锁,之后被弃用。

    行级锁

    行级锁(Row-level Locking):基于索引数据结构实现,是 MySQL 中锁定粒度最细的一种锁,只有线程当前使用的行被锁定,其他行对于其他线程都是可用的;InnoDB引擎默认的锁级别。

    • 优点:发生锁冲突几率低,并发高

    • 缺点:开销大,加锁慢;会出现死锁的情况; 

    行级锁包括共享锁、排他锁和更新锁。在下面的内容中具体介绍。

    InnoDB行级锁是通过锁索引记录实现的,如果更新的列没建索引会锁住整个表。

    InnoDB锁

    InnoDB锁:https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

    共享锁S

    共享锁(Shared Locks):简称S锁,也被称为读锁。读锁允许多个连接可以同一时刻并发的读取同一资源,互不干扰。

    语法:

    SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE  

    排他锁X

    排他锁(Exclusive Locks):简称X锁,也叫独占锁、写锁。一个独占锁会阻塞其他的写锁或读锁,保证同一时刻只有一个连接可以写入数据,同时防止其他用户对这个数据的读写。执行数据更新命令,即INSERT、UPDATE 或DELETE 命令时,MySQL 会自动使用独占锁。但当对象上有其它锁存在时,无法对其加独占锁。独占锁一直到事务结束才能被释放。

    语法:

    SELECT * FROM table_name WHERE ... FOR UPDATE

    意向锁

    意向锁(Intention Locks):提前声明一个意向,并获取表级别的意向锁(共享意向锁 IS 或排他意向锁 IX),如果获取成功,则稍后将要或正在(才被允许),对该表的某些行加锁(S或X)了。(除了 LOCK TABLES ... WRITE,会锁住表中所有行,其他场景意向锁实际不锁住任何行)。

    意向锁包含

    • 意向共享锁(Intention Shared Lock):简称IS锁,事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁.

    • 意向独占锁(Intention Exclusive Lock):简称IX锁,事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁.

    意向锁协议:在事务能够获取表中行上的共享锁之前,它必须首先获取表上的IS锁或更强的锁。在事务能够获取表中的行上的独占锁之前,它必须首先获取表上的IX锁。 

    InnoDB锁兼容性列表

    XIXSIS
    X
    IX兼容兼容
    S兼容兼容
    IS兼容兼容兼容

    共享锁和排他锁都是标准的行级锁,意向锁是表级锁。
    意向锁是比X、S更弱的锁,存在一种预判的意义!先获取更弱的IX、IS锁,如果获取失败就不必再花费更大的开销获取更强的X、S锁。

    记录锁

    记录锁(Record Locks):最简单的行锁,行锁是加在索引上的,如果查询命令不走索引时,这条语句会锁住所有记录也就是锁表,如果语句的执行能够执行某一个字段的索引,那么仅会锁住满足 where 的行(Record Lock)。

    间隙锁

    间隙锁(Gap Locks):锁定索引记录之间的间隙([2]),或者锁定一个索引记录之前的间隙([1]),或者锁定一个索引记录之后的间隙([3])。

    a8437eb5465b7370c412242799b4c18c.png

    如图[1]、[2]、[3]部分。一般作用于我们的范围筛选查询> 、< 、between......

    例如, SELECT userId FROM t1 WHERE userId BETWEEN 1 and 4 FOR UPDATE; 阻止其他事务将值3插入到列 userId 中。因为该范围内所有现有值之间的间隙都是锁定的。

    对于使用唯一索引来搜索唯一行的语句 select a from ,不产生间隙锁定。(不包含组合唯一索引,也就是说 gapLock 不作用于单列唯一索引)。

    例如,如果id列有唯一的索引,下面的语句只对id值为100的行使用索引记录锁,其他会话是否在前一个间隙中插入行并不重要:

    SELECT * FROM t1 WHERE id = 100; 如果id没有索引或具有非唯一索引,则语句将锁定前面的间隙。

    间隙可以跨越单个索引值、多个索引值(如上图2,3),甚至是空的;

    间隙锁是性能和并发性之间权衡的一种折衷,用于某些特定的事务隔离级别,如RC级别(RC级别:REPEATABLE READ,我司为了减少死锁,关闭了gap锁,使用RR级别);

    在重叠的间隙中(或者说重叠的行记录)中允许gap共存;

    例如,同一个 gap 中,允许一个事务持有 gap X-Lock(gap 写锁\排他锁),同时另一个事务在这个 gap 中持有(gap 写锁\排他锁);

    Next-Key Lock

    记录锁与与间隙锁的结合。

    例:存在一个查询匹配 b=3 的行(b上有个非唯一索引),那么所谓 NextLock 就是:在b=3 的行加了 RecordLock 并且使用 GapLock 锁定了 b=3 之前(“之前”:索引排序)的所有行记录。

     innodb 中默认隔离级别(RR)下,next key Lock 自动开启;

    插入意向锁

    插入意向锁(Insert Intention Locks):是一种间隙锁,在行执行 INSERT 之前的插入操作设置。如果多个事务 INSERT 到同一个索引间隙之间,但没有在同一位置上插入,则不会产生任何的冲突。假设有值为4和7的索引记录,现在有两事务分别尝试插入值为 5 和 6 的记录,在获得插入行的排他锁之前,都使用插入意向锁锁住 4 和 7 之间的间隙,但两者之间并不会相互阻塞,因为这两行并不冲突。

    插入意向锁只会和 间隙或者 Next-key 锁冲突,正如上面所说,间隙锁作用就是防止其他事务插入记录造成幻读,正是由于在执行 INSERT 语句时需要加插入意向锁,而插入意向锁和间隙锁冲突,从而阻止了插入操作的执行。

    不同类型的锁之间的兼容如下表所示

     RECOREDGAPNEXT-KEYII GAP(插入意向锁)
    RECORED兼容兼容
    GAP兼容兼容兼容兼容
    NEXT-KEY兼容兼容
    II GAP兼容兼容

    自增锁

    自增锁(Auto-inc Locks):是一种特殊的表级别锁,专门针对事务插入AUTO_INCREMENT类型的列。如果一个事务正在往表中插入记录,所有其他事务的插入必须等待,以便第一个事务插入的行,是连续的主键值。

    站内摘来的一张时序图,帮助理解MySQL的事务和锁:

    010aa3b96d42a637a542e251832320e7.png

    扩展

    死锁

    死锁(Dead Lock):当并发系统中不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都进入无限等待的状态,称为死锁。

    产生死锁的4个必要条件

    • 互斥条件:一个资源每次只能被一个线程使用

    • 请求与保持条件:一个线程因请求资源而阻塞时,对已获得的资源保持不放

    • 不剥夺条件:进程已经获得的资源,在未使用完之前,不能强行剥夺

    • 循环等待条件:若干线程之间形成一种头尾相接的循环等待资源关系

    死锁检测:       

    对于死锁,我们可以通过参数 innodb_lock_wait_timeout 根据实际业务场景来设置超时时间,InnoDB引擎默认值是50s。
    发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on,表示开启这个逻辑(默认是开启状态)。

    wait-for graph 算法来主动进行死锁检测:innodb 还提供了 wait-for graph 算法来主动进行死锁检测,每当加锁请求无法立即满足需要并进入等待时,wait-for graph 算法都会被触发。

    避免死锁的方法:

    1. 如果不同程序会并发存取多个表,尽量约定以相同的顺序访问表,可以大大降低发生死锁的可能性;

    2. 在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率;

    3. 对于非常容易产生死锁的业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁产生的概率。

    如何解决热点行更新导致的性能问题?

    1. 如果你能确保这个业务一定不会出现死锁,可以临时把死锁检测关闭掉。一般不建议采用;

    2. 控制并发度,对应相同行的更新,在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了;

    3. 将热更新的行数据拆分成逻辑上的多行来减少锁冲突,但是业务复杂度可能会大大提高;

    MyISAM 中是不会产生死锁的,因为 MyISAM 总是一次性获得所需的全部锁,要么全部满足,要么全部等待。

    活锁

    活锁、死锁本质上是一样的:都是多个线程任务没有任何进展。原因是在获取共享资源时,并发多线程或多进程声明资源占用(即加锁)的顺序有冲突,死锁是加不上就死等(多线程都处于阻塞状态);活锁是加不上就放开已获得的资源重试,这种情况下线程并没有阻塞所以是活的状态,但是只是在做无用功(每次重试都是失败的),其实多核活锁不太常见(资源分配充足)。

    例:资源A和B,进程P1和P2

    startP1 lock AP2 lock BP1 lock B fail context switchP2 lock A fail context switchP1 release AP2 release Bgoto start    #活锁,不断尝试,做无用功

    悲观锁

    顾名思义,就是很悲观,它对于数据被外界修改持保守态度,认为数据随时会修改,所以整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系数据库提供的锁机制,事实上关系数据库中的行锁,表锁,读写锁都是悲观锁。

    乐观锁

    乐观锁一般是指用户自己实现的一种锁机制。乐观锁是指操作数据库时(更新操作),想法很乐观,认为这次的操作不会导致冲突,在操作数据时,并不进行任何其他的特殊处理(也就是不加锁),而在进行更新后,再去判断是否有冲突了。乐观锁适用于多读的应用类型,这样可以提高吞吐量。

    常见乐观锁实现方式:

    • 版本号机制一般是在数据表中加上一个数据版本号version字段,表示数据被修改的次数,当数据被修改时,version值会加一。当线程A要更新数据值时,在读取数据的同时也会读取version值,在提交更新时,若刚才读取到的version值为当前数据库中的version值相等时才更新,否则重试更新操作,直到更新成功。

    • CAS算法比较与交换(compare and swap),是一种很有名的无锁算法。无锁编程,即不使用锁的情况下实现多线程之间的变量同步,也就是在没有线程被阻塞的情况下实现变量的同步,所以也叫非阻塞同步(Non-blocking Synchronization)。CAS算法涉及到三个操作值:

      • 需要读写的内存值 V

      • 进行比较的值 A

      • 拟写入的新值 B

    当且仅当 V 的值等于 A时,CAS通过原子方式用新值B来更新V的值,否则不会执行任何操作(比较和替换是一个原子操作)。一般情况下是一个自旋操作,即不断的重试。

    注意:乐观锁只能用于本系统控制,无法阻止外系统更新;

    并发控制(MVCC)

    MVCC (multiple-version-concurrency-control):行级锁的变种, 在普通读情况下避免了加锁操作,因此开销更低。虽然实现不同,但通常都是实现非阻塞读,对于写操作只锁定必要的行。

    • 一致性读 (就是读取快照)select * from table ....

    • 当前读(就是读取实际的持久化的数据),特殊的读操作,插入/更新/删除操作,属于当前读,处理的都是当前的数据,需要加锁。select * from table where ... lock in share mode; select * from table where ... for update; insert; update ; delete;

    注意:select ...... from table ...... (没有额外加锁后缀)使用MVCC,保证了读快照(MySQL 称为 consistent read),所谓一致性读或者读快照就是读取当前事务开始之前的数据快照,在这个事务开始之后的更新不会被读到。

    InnoDB的 MVCC 通常是通过在每行数据后边保存两个隐藏的列来实现(其实是三列,第三列是用于事务回滚)一个保存了行的创建版本号,另一个保存了行的更新版本号(上一次被更新数据的版本号) 这个版本号是每个事务的版本号,递增的。这样保证了 InnoDB对读操作不需要加锁也能保证正确读取数据。

    在MySQL 默认的 Repeatable Read 隔离级别下, MVCC 的具体操作

    • Select(快照读,所谓读快照就是读取当前事务之前的数据)

      a.InnoDB 只 select 查找版本号早于当前版本号的数据行,这样保证了读取的数据要么是在这个事务开始之前就已经 commit 了的(早于当前版本号),要么是在这个事务自身中执行创建操作的数据(等于当前版本号)。

      b.查找行的更新版本号要么未定义,要么大于当前的版本号(为了保证事务可以读到老数据),这样保证了事务读取到在当前事务开始之后未被更新的数据。

      注意:这里的 select 不能有 for update、lock in share 语句。总之要只返回满足以下条件的行数据,达到了快照读的效果:

    (行创建版本号当前版本号 && (行更新版本号==null or 行更新版本号>当前版本号 ) )
    • Insert:InnoDB为这个事务中新插入的行,保存当前事务版本号的行作为行的行创建版本号。

    • Delete:InnoDB 为每一个删除的行保存当前事务版本号,作为行的删除标记。

    • Update:将存在两条数据,保持当前版本号作为更新后的数据的新增版本号,同时保存当前版本号作为老数据行的更新版本号。

    当前版本号—写—>新数据行创建版本号 && 当前版本号—写—>老数据更新版本号();

    希望本文对你有帮助,请点个赞鼓励一下作者吧~ 谢谢!

    展开全文
  • MySQL锁与调优① 锁的分类MySQL数据库的锁分为表级锁和行级锁。从数据库的角度看,行级锁又可以分为独占锁(排它锁)和共享锁。 参考链接:https://www.cnblogs.com/coder-wf/p/13386443.html② 行锁和表锁表锁和行锁...
  • 这个问题一直被很多人关注,基本上得到的答案是两种倾向,一种是锁一种是给记录打标记(也就是update)。对于应用来说,我并不提倡人为给记录加锁,这样会惹来很多麻烦,况且锁并不能解决所有问题,如果你有这方面好的...
  • mysql> explain partitions select * from TxnList where startTime>'2016-08-25 00:00:00' and startTime +----+-------------+-------------------+------------+------+---------------+------+-----...
  • 一、MySQL中的锁(表锁、行锁)背景:当数据库中有多个操作需要修改同一数据时,不可避免的会产生数据的脏读。这时就需要数据库具有良好的并发控制能力,这一切在 MySQL 中都是由服务器和存储引擎来实现的。解决并发...
  • 苏南大叔使用宝塔面板的过程中,已经看到过几次"磁盘分区的可用容量小于1GB,可能导致mysql自动停止"的提示了。说实话,看上去就一脸懵逼。主机提供商那边对这个系统盘的限制是很严格的。要不就拿银子来买,要不就...
  • 锁的优化synchronized--悲观锁、独占锁、互斥锁、排他锁Lock分为ReentrantLock、读写锁、公平锁和非公平锁,底层用了CAS无锁机制(自旋锁)。ReentrantLock锁底层使用AQS原理实现。自旋锁与互斥锁的区别互斥锁:线程会...
  • 开启事务 一堆SQL执行 for update 读表A ...6)mysql事务隔离级别:repeatable read [img=https://img-bbs.csdn.net/upload/201607/07/1467876182_655511.png][/img] 7)环境:WIN10+NGINX+PHP+MYSQL
  • I have multiple mysql databases and I want to perform some administration tasks on a particular database. How do I ensure that no one else can connect to that database while the tasks in progress?解决...
  • 写Secondlife智能体的知识系统小插件的时候遇到的这个问题。在把某个NPC的全部知识复制给另一个NPC的时候,对数据库操作,为了提升一点效率希望与数据库...MySqlConnection mycon= new MySqlConnection(MysqlDao.co...
  • thread_stack 线程栈信息使用内存default:192KB,用来存放每一个线程自身的标识信息及线程栈分配多大的内存,如thread_id,线程运行时基本信息mysql排序算法:第一种,mysql4.1之前算法,实现方...
  • mysql for update 独占

    2020-04-28 14:22:00
    for update 独占锁,仅适用于InnoDB,并且必须开启事务,在begin与commit之间才生效,锁住的数据不能在事务提交前被修改: 例子1:Select * from table1 where id=xx for update; 行锁; 例子2:select * from ...
  • 这里需要注意的一点是不同的数据库对select for update的实现和支持都是有所区别的,例如oracle支持select for update no wait,表示如果拿不到锁立刻报错,而不是等待,mysql就没有no wait这个选项。另外mysql还有...
  • 写Secondlife智能体的知识系统小插件的时候遇到的这个问题。 在把某个NPC的全部知识复制给另一个NPC的时候,对数据库...MySqlCommand mycmd = new MySqlCommand(); MySqlConnection mycon = new MySqlConnectio...
  • 写Secondlife智能体的知识系统小插件的时候遇到的这个问题。在把某个NPC的全部知识复制给另一个NPC的时候,对数据库操作,为了提升一点效率希望与数据库...MySqlConnection mycon= new MySqlConnection(MysqlDao.co...
  • 博主最近在复习 MySQL 和 Redis 的知识。顺便就记录一下这个 MySQL 排它锁和共享锁。本文以下内容基于数据表(test 表):+----+-------+|id|name|+----+-------+|1|111|+----+-------+|2|222|+----+-------+共享锁...
  • 1.2 独占锁 Exclusive Locks(独占锁),也称之为排他锁,简称X锁若想要改动一条数据,就得获得这条数据的写锁,所以它也叫写锁。 SELECT * FROM student WHERE id=1 FOR UPDATE; 复制代码 只要一个事务获取了一行...
  • mysql innodb 独立表空间和共享表空间 mysql> show variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_pe
  • Mysql共享锁、排他锁、悲观锁、乐观锁及其使用场景一、相关名词|--表级锁(锁定整个表)|--页级锁(锁定一页)|--行级锁(锁定一行)|--共享锁(S锁,MyISAM 叫做读锁)|--排他锁(X锁,MyISAM 叫做写锁)|--悲观锁(抽象性,不...
  • 随着数据量的增长,MySQL 单表数据越来越大,单个机器的磁盘空间不足以存储那么大的数据,此时通常会采用分库分表技术,将单表数据按某种规则分割到多个表中。分库分表可能导致的一个结果是库里同时存在很多表,少则...
  • 修改mysql配置文件innodb_file_per_table=1 为使用独占表空间innodb_file_per_table=0 为使用共享表空间#命令行执行alter table table_name engine=innodb;
  • 重新认识Mysql之MyISAM表锁(共享读与独占写)(一、Mysql锁的概念与特性)在Mysql数据库系统中,不同的存储引擎支持不同的锁机制。比如MyISAM和MEMORY存储引擎采用的表级锁,BDB采用的是页面锁,也支持表级锁,InnoDB...
  • title: mysql共享锁和排它锁author: Joe Tongtags:JAVAEEMYSQLcategories:ITdate: 2020-05-29 15:35:53一、相关名词|--表级锁(锁定整个表)|--页级锁(锁定一页)|--行级锁(锁定一行)|--共享锁(S锁,MyISAM 叫做读锁)|-...
  • 重新认识Mysql之MyISAM表锁(共享读与独占写)(一、Mysql锁的概念与特性)在Mysql数据库系统中,不同的存储引擎支持不同的锁机制。比如MyISAM和MEMORY存储引擎采用的表级锁,BDB采用的是页面锁,也支持表级锁,InnoDB...
  • 共享锁与独占锁简单介绍 共享锁:Shared Locks,简称S锁,可以多个事务共同持有,获得共享锁的事务只能读数据,不能修改数据。 假如事务1⾸先获取了⼀条记录的S锁,如果事务2想要获取这条记录的S锁,那么事务T2也能...
  • 我有一个名为电子邮件的表,其中三列表示id,emailFrom,emailTo -------------------------------------------------- | id | emailFrom | ... 标签:mysql,sql,db2 来源: https://codeday.me/bug/20190520/1142170.html

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 772
精华内容 308
关键字:

mysql独占

mysql 订阅