精华内容
下载资源
问答
  • mysql锁
    千次阅读
    2022-05-10 16:00:25

    如果同事告诉你他现在跑一个删除操作超时了,他能提供具体哪张表,让我们帮忙确认一下,谁堵他的语句。

    首先查询对应表的实时锁状态,找到堵塞他的blocking_trx_id


    select * from sys.innodb_lock_waits where locked_table="`q1`.`cece`";

    假设输出的是16

    吧16带入

    SELECT a.sql_text,
    c.id,
    d.trx_started
    FROM performance_schema.events_statements_current a
    join performance_schema.threads b
    ON a.thread_id = b.thread_id
    join information_schema.processlist c
    ON b.processlist_id = c.id
    join information_schema.innodb_trx d
    ON c.id = d.trx_mysql_thread_id
    where c.id=16
    ORDER BY d.trx_started;

    就可以确定

    更多相关内容
  • 本文实例讲述了MySQL锁机制与用法。分享给大家供大家参考,具体如下: MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAM和MEMORY存储引擎采用的是表级锁;BDB存储引擎采用的是...
  • MySQL锁

    千次阅读 2022-03-09 07:28:51
    机制 事务与是不同的。事务具有ACID( 原子性、一致性、隔离性和持久性),是用于解决隔离性的一种机制。事务的隔离级别通过的机制来实现。 为了保证数据并发访问时的一致性和有效性,任何一个数据库都存在...

    锁机制

    事务与锁是不同的。事务具有ACID( 原子性、一致性、隔离性和持久性),锁是用于解决隔离性的一种机制。事务的隔离级别通过锁的机制来实现。

    为了保证数据并发访问时的一致性和有效性,任何一个数据库都存在锁机制。锁机制的优劣直接影响到数据库的并发处理能力和系统性能,所以锁机制也就成为了各种数据库的核心技术之一。

    锁机制是为了解决数据库的并发控制问题而产生的。如在同一时刻,客户端对同一个表做更新或查询操作,为了保证数据的一致性,必须对并发操作进行控制。同时,锁机制也为实现 MySQL 的各个隔离级别提供了保证。

    可以将锁机制理解为使各种资源在被并发访问时变得有序所设计的一种规则

    如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说,锁对数据库显得尤其重要,也更加复杂。

    image-20220303140824038

    锁级别分类,可分为共享锁、排他锁和意向锁。

    • 共享锁

    共享锁的代号是 S,是 Share 的缩写,也可称为读锁。是一种可以查看但无法修改和删除的数据锁

    共享锁的锁粒度是行或者元组(多个行)一个事务获取了共享锁之后,可以对锁定范围内的数据执行读操作。会阻止其它事务获得相同数据集的排他锁。

    • 排他锁

    排他锁的代号是 X,是 eXclusive 的缩写,也可称为写锁,是基本的锁类型。

    排他锁的粒度与共享锁相同,也是行或者元组一个事务获取了排他锁之后,可以对锁定范围内的数据执行写操作允许获得排他锁的事务更新数据,阻止其它事务取得相同数据集的共享锁和排他锁

    如有两个事务 A 和 B,如果事务 A 获取了一个元组的共享锁,事务 B 还可以立即获取这个元组的共享锁,但不能立即获取这个元组的排他锁,必须等到事务 A 释放共享锁之后才可以。

    如果事务 A 获取了一个元组的排他锁,事务 B 不能立即获取这个元组的共享锁,也不能立即获取这个元组的排他锁,必须等到 A 释放排他锁之后才可以。

    • 意向锁

    为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB 还有两种内部使用的意向锁

    意向锁是一种表锁,锁定的粒度是整张表,分为意向共享锁(IS)和意向排他锁(IX)两类。

    意向共享锁表示一个事务有意对数据上共享锁或者排他锁。“有意表示事务想执行操作但还没有真正执行

    锁和锁之间的关系,要么是相容的,要么是互斥的。

    • 锁 a 和锁 b 相容是指:操作同样一组数据时,如果事务 t1 获取了锁 a,另一个事务 t2 还可以获取锁 b;
    • 锁 a 和锁 b 互斥是指:操作同样一组数据时,如果事务 t1 获取了锁 a,另一个事务 t2 在 t1 释放锁 a 之前无法释放锁 b。

    其中共享锁排他锁意向共享锁、意向排他锁相互之间的兼容/互斥关系如下表所示,其中 Y 表示相容,N 表示互斥。

    image-20220303132033771

    如果一个事务请求的锁模式与当前的锁兼容InnoDB 就将请求的锁授予该事务;反之,如果两者不兼容,该事务就要等待锁释放。

    为了尽可能提高数据库的并发量,需每次锁定的数据范围越小越好,越小的锁其耗费的系统资源越多,系统性能下降。为在高并发响应和系统性能两方面进行平衡,这样就产生了“锁粒度”的概念。

    也可以按锁粒度分类,可分为行级锁、表级锁和页级锁

    • 行级锁

    行级锁的锁定颗粒度在 MySQL 中是最小的只针对操作的当前行进行加锁,所以行级锁发生锁定资源争用的概率也最小。

    行级锁能够给予应用程序尽可能大的并发处理能力,从而提高需要高并发应用系统的整体性能。虽然行级锁在并发处理能力上面有较大的优势,但也因此带来了不少弊端。

    由于锁定资源的颗粒度很小,所以每次获取锁和释放锁需要做的事情也就更多,带来的消耗自然也就更大。此外,行级锁也最容易发生死锁。所以说行级锁最大程度地支持并发处理的同时,也带来了最大的锁开销。

    行级锁主要应用于 InnoDB 存储引擎。

    随着锁定资源颗粒度的减小,锁定相同数据量的数据所需要消耗的内存数量也越来越多,实现算法也会越来越复杂。不过,随着锁定资源颗粒度的减小,应用程序的访问请求遇到锁等待的可能性也会随之降低,系统整体并发度也会随之提升。

    • 表级锁

    表级锁为表级别的锁定,会锁定整张表,可以很好的避免死锁,是 MySQL 中最大颗粒度的锁定机制。

    一个用户在对表进行写操作(插入、删除、更新等)时,需要先获得写锁(也叫排斥锁),这会阻塞其它用户对该表的所有读写操作。没有写锁时,其它读取的用户才能获得读锁,读锁之间是不相互阻塞的。

    表级锁最大的特点就是实现逻辑非常简单,带来的系统负面影响最小。所以获取锁和释放锁的速度很快。当然,锁定颗粒度大带来最大的负面影响就是出现锁定资源争用的概率会很高,致使并发度大打折扣。

    不过在某些特定的场景中,表级锁也可以有良好的性能。例如,READ LOCAL 表级锁支持某些类型的并发写操作。另外,写锁也比读锁有更高的优先级,因此一个写锁请求可能会被插入到读锁队列的前面(写锁可以插入到锁队列中读锁的前面,反之读锁则不能插入到写锁的前面)。

    使用表级锁的主要是 MyISAMMEMORYCSV 等一些非事务性存储引擎。

    尽管存储引擎可以管理自己的锁,MySQL 本身还是会使用各种有效的表级锁来实现不同的目的。例如,服务器会为诸如 ALTER TABLE 之类的语句使用表级锁,而忽略存储引擎的锁机制。

    • 页级锁

    页级锁是 MySQL 中比较独特的一种锁定级别,在其他数据库管理软件中并不常见。

    页级锁的颗粒度介于行级锁与表级锁之间,所以获取锁定所需要的资源开销,以及所能提供的并发处理能力同样也是介于上面二者之间。另外,页级锁和行级锁一样,会发生死锁。

    页级锁主要应用于 BDB 存储引擎。

    image-20220303134128774

    从锁的角度来说,表级锁适合以查询为主只有少量按索引条件更新数据的应用,如 Web 应用。而行级锁更适合于有大量按索引条件,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。

    InnoDB锁算法 中的三种行锁

    在 MySQL 中,InnoDB 行锁通过给索引上的索引项加锁来实现,如果没有索引,InnoDB 将通过隐藏的聚簇索引来对记录加锁。

    InnoDB 支持 3 种行锁定方式:

    • 行锁(Record Lock):直接对索引项加锁。也叫记录锁
    • 间隙锁(Gap Lock):锁加在索引项之间的间隙,也可以是第一条记录前的“间隙”或最后一条记录后的“间隙”。
    • 临键锁(Next-Key Lock):行锁与间隙锁组合起来用就叫做 Next-Key Lock。 前两种的组合,对记录及其前面的间隙加锁。

    默认情况下,InnoDB 工作在可重复读(默认隔离级别)下,并且以 Next-Key Lock 的方式对数据行进行加锁,这样可以有效防止幻读的发生。

    Next-Key Lock 是行锁与间隙锁的组合,这样,当 InnoDB 扫描索引项的时候,会首先对选中的索引项加上行锁(Record Lock),再对索引项两边的间隙(向左扫描扫到第一个比给定参数小的值, 向右扫描扫到第一个比给定参数大的值, 然后以此为界,构建一个区间)加上间隙锁(Gap Lock)。如果一个间隙被事务 T1 加了锁,其它事务不能在这个间隙插入记录。

    要禁止间隙锁的话,可以把隔离级别降为读已提交(READ COMMITTED),或者开启参数 innodb_locks_unsafe_for_binlog。

    注意:以上语句描述的情况,与 MySQL 所设置的事务隔离级别有较大的关系。

    开启一个事务时,InnoDB 存储引擎会在更新的记录上加行级锁,此时其它事务不可以更新被锁定的记录。

    1. innodb对于⾏的查询使⽤next-key lock

    2. Next-locking keying为了解决Phantom Problem幻读问题

    3. 当查询的索引含有唯⼀属性时,将next-key lock降级为record key

    4. Gap锁设计的⽬的是为了阻⽌多个事务将记录插⼊到同⼀范围内,⽽这会导致幻读问题的产⽣

    5. 有两种⽅式显式关闭gap锁:(除了外键约束和唯⼀性检查外,其余情况仅使⽤record lock) A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1

    实例:

    • 行锁(Record Lock)

    分别在 A 窗口和 B 窗口中查看事务隔离级别,A 窗口和 B 窗口的事务隔离级别需要保持一致。

    # A 窗口查看隔离级别的 SQL 语句和运行结果
    show variables like 'tx_isolation';
    image-20220303141013025
    # B 窗口查看隔离级别 SQL 语句和运行结果
    show variables like 'tx_isolation';
    image-20220303141204472

    A窗口和 B窗口的事务隔离级别都为可重复读 REPEATABLE-READ

    # 在 A窗口中开启一个事务,并修改 tb_student 表
    begin;
    update test.tb_student set age ='30' where id = 1;
    # 在 B窗口中也开启一个事务,并修改 tb_student 表
    begin;
    update test.tb_student set age ='30' where id = 1;
    # 会发现 update 语句一直在执行
    # 这时我们在 A 窗口中提交事务。
    commit;
    # B 窗口中的 UPDATE 语句执行成功。
    # 查询 tb_student 表中的数据
    select * from test.tb_student;
    image-20220303141629968

    当有不同的事务同时更新同一条记录时,另外一个事务需要等待另一个事务把锁释放。

    # 查看 MySQL 中 InnoDB 存储引擎的状态
    show engine innodb status;
    image-20220303141815968

    从上面运行结果可以看出,SQL 语句 UPDATE test.tb_student SET age ='30' WHERE id = 1 在等待,RECORD LOCKS space id 197 page no 3 n bits 80 index PRIMARY of table test.tb_student trx id 19555 lock_mode X locks rec but not gap 表示锁住的资源,locks rec but not gap 代表锁住的是一个索引,不是一个范围。

    MySQL thread id 14, OS thread handle 4568, query id 886 localhost ::1 root updating”表示第 2 个事务连接的 ID 为 14,当前状态为正在更新,同时正在更新的记录需要等待其它事务将锁释放。当超过事务等待锁允许的最大时间,此时会提示“ERROR 1205(HY000):Lock wait timeout exceeded; try restarting transaction" 及当前事务执行失败,则自动执行回滚操作。

    MySQL 数据库采用 InnoDB 模式,默认参数 innodb_lock_wait_timeout 设置锁等待的时间是 50s,一旦数据库锁超过这个时间就会报错。

    # 命令查看当前数据库锁等待的时间
    show global variables like 'innodb_lock_wait_timeout';
    image-20220303142251934
    • 间隙锁(Gap Lock)
    # 在保证 A 窗口和 B 窗口的前提下,将 tb_student 表中的 id 字段设为外键,并开启一个事务,修改 tb_student 表中 id 为 1 的 age。
    alter table test.tb_student add unique key idx_id(id);
    
    begin;
    update test.tb_student set age ='31' where id = 1;
    # B 窗口中开启一个事务,修改 tb_student 表中 id 为 2 的 age
    begin;
    update test.tb_student set age ='28' where id = 2;
    # 分别提交 A窗口和 B窗口的事务
    commit;
    # 查询 tb_student 表的数据
    select  * from test.tb_student;
    image-20220303142633667

    由于 InnoDB 行级锁为间隙锁,只锁定需要的记录,因此 B窗口中的事务可以更新其它记录,两个事务之间互不影响。

    展开全文
  • MYSQL 解锁与表介绍

    2021-01-19 22:41:09
    MySQL锁概述   相对其他数据库而言,MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);BDB存储引擎采用的是页面锁...
  • mysql锁详解

    2019-07-23 02:36:45
    资源名称:mysql锁详解资源截图: 资源太大,传百度网盘了,链接在附件中,有需要的同学自取。
  • 锁,在现实生活中是为我们...MySQL锁 相对于其他的数据库而言,MySQL的锁机制比较简单,最显著的特点就是不同的存储引擎支持不同的锁机制。根据不同的存储引擎,MySQL中锁的特性可以大致归纳如下: 行锁 表锁 页锁
  • MYSQL锁机制

    2018-07-26 19:23:56
    本文详细描述了MYSQL锁机制,掌握锁机制,对于避免死锁,提高软件的工作效率,有很大的帮助.
  • MySQL锁详解

    2017-10-23 10:50:40
    MySQL锁详解,锁还是非常重要的,无论解决工作的死锁还是面试都会有
  • mysql锁机制

    千次阅读 2021-12-09 15:31:36
    首先对mysql锁进行划分: 按照锁的粒度划分:行锁、表锁、页锁 按照锁的使用方式划分:共享锁、排它锁(悲观锁的一种实现) 还有两种思想上的锁:悲观锁、乐观锁。 InnoDB中有几种行级锁类型:Record Lock、Gap ...

    MySQL的锁机制


    首先对mysql锁进行划分:

    1. 按照锁的粒度划分:行锁、表锁、页锁
    2. 按照锁的使用方式划分:共享锁、排它锁(悲观锁的一种实现)
    3. 还有两种思想上的锁:悲观锁、乐观锁。
    4. InnoDB中有几种行级锁类型:Record Lock、Gap Lock、Next-key Lock

    MySQL的锁机制最显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);BDB存储引擎采用的是页面锁(page-level locking),但也支持表级锁;InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。

    1.行锁

    ​ 行级锁是Mysql中锁定粒度最细的一种锁,表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小,但加锁的开销也最大。有可能会出现死锁的情况。 行级锁按照使用方式分为共享锁和排他锁。

    共享锁用法(S锁 读锁):
    ​ 若事务T对数据对象A加上S锁,则事务T可以读A但不能修改A,其他事务只能再对A加S锁,而不能加X锁,直到T释放A上的S锁。这保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。

    select ... lock in share mode;
    

    共享锁就是允许多个线程同时获取一个锁,一个锁可以同时被多个线程拥有。

    排它锁用法(X 锁 写锁):
    ​ 若事务T对数据对象A加上X锁,事务T可以读A也可以修改A,其他事务不能再对A加任何锁,直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

    select ... for update
    

    排它锁,也称作独占锁,一个锁在某一时刻只能被一个线程占有,其它线程必须等待锁被释放之后才可能获取到锁。

    MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁,比如 MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁,对于这种引擎的表,同 一张表上任何时刻只能有一个更新在执行,这就会影响到业务并发度。InnoDB是支持行锁的, 这也是MyISAM被InnoDB替代的重要原因之一。

    2.表锁

    表级锁是mysql锁中粒度最大的一种锁,表示当前的操作对整张表加锁,资源开销比行锁少,不会出现死锁的情况,但是发生锁冲突的概率很大,因为同 一张表上任何时刻只能有一个更新在执行。被大部分的mysql引擎支持,MyISAM和InnoDB都支持表级锁,但是InnoDB默认的是行级锁

    表级锁按照使用方式也可分为共享锁和排他锁。
    共享锁用法:

    LOCK TABLE table_name [ AS alias_name ] READ
    

    排它锁用法:

    LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE
    

    解锁用法:

    unlock tables;
    

    3.页锁

    页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快,但冲突多,行级冲突少,但速度慢。所以取了折衷的页级,一次锁定相邻的一组记录。BDB存储引擎支持页级锁

    全局锁
    除了行锁,表锁,页锁外,这里还补充一种全局锁,这种锁的范围最大。
    全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法,命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候,可以使用这个命 令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括 建表、修改表结构等)和更新类事务的提交语句。

    全局锁的典型使用场景是,做全库逻辑备份。 全 也就是把整库每个表都select出来存成文本。

    4.乐观锁和悲观锁

    4.1悲观锁

    在关系数据库管理系统里,悲观并发控制又名“悲观锁”,Pessimistic Concurrency Control,缩写“PCC”)是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作对某行数据应用了锁,那只有当这个事务把锁释放,其他事务才能够执行与该锁冲突的操作。悲观并发控制主要用于数据争用激烈的环境,以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

    悲观锁,正如其名,它指的是对数据被外界(包括本系统当前的其他事务,以及来自外部系统的事务处理)修改持保守态度(悲观),因此,在整个数据处理过程中,将数据处于锁定状态 悲观锁的实现,往往依靠数据库提供的锁机制 (也只有数据库层提供的锁机制才能真正保证数据访问的排他性,否则,即使在本系统中实现了加锁机制,也无法保证外部系统不会修改数据)

    mysql中实现悲观锁的具体流程:

    在对任意记录进行修改前,先尝试为该记录加上排他锁(exclusive locking)
    如果加锁失败,说明该记录正在被修改,那么当前查询可能要等待或者抛出异常。 具体响应方式由开发者根据实际需要决定。
    如果成功加锁,那么就可以对记录做修改,事务完成后就会解锁了。
    其间如果有其他对该记录做修改或加排他锁的操作,都会等待我们解锁或直接抛出异常。

    总而言之就是一句话:mysql中悲观锁的实现是通过排他锁来实现的

    在mysql(InnoDB)中使用悲观锁:

    1.开始事务
    begin;/begin work;/start transaction; (三者选一就可以)
    2.查询出商品信息
    select ... for update;(这里是使用的行锁的排他锁)
    4.提交事务
    commit;/commit work;
    

    通过下面的例子来说明:
    1.当你手动加上排它锁,但是并没有关闭mysql中的autocommit。

    1.这里先给user表所有的行加上行锁的排他锁
    mysql> select * from user for update;
    +----+------+--------+
    | id | name | psword |
    +----+------+--------+
    |  1 | a    | 1      |
    |  2 | b    | 2      |
    |  3 | c    | 3      |
    +----+------+--------+
    3 rows in set
    
    2.再对加了排他锁的行更改数据时,这里他会一直提示Unknown
    mysql> update user set name=aa where id=1;
    1054 - Unknown column 'aa' in 'field list'
    mysql> insert into user values(4,d,4);
    1054 - Unknown column 'd' in 'field list'
    
    

    2.关闭mysql中的autocommit后的正常流程

    窗口1:
    mysql> set autocommit=0;(先关闭mysql中的autocommit)
    Query OK, 0 rows affected
    我这里锁的是表中的所有行
    mysql> select * from user for update;
    +----+-------+
    | id | price |
    +----+-------+
    |  1 |   500 |
    |  2 |   800 |
    +----+-------+
    2 rows in set
    
    窗口2:
    mysql> update user set price=price-100 where id=1;
    执行上面操作的时候,会显示等待状态,一直到窗口1执行commit提交事务才会出现下面的显示结果
    Database changed
    Rows matched: 1  Changed: 1  Warnings: 0
    
    窗口1:执行commit手动提交事务
    mysql> commit;
    Query OK, 0 rows affected
    再查询一下user表,发现已经执行了窗口2的更新操作
    mysql> select * from user;
    +----+-------+
    | id | price |
    +----+-------+
    |  1 |   400 |
    |  2 |   800 |
    +----+-------+
    2 rows in set
    

    上面的例子展示了排它锁的原理:一个锁在某一时刻只能被一个线程占有,其它线程必须等待锁被释放之后才可能获取到锁或者进行数据的操作。

    悲观锁的优点和不足:
    悲观锁实际上是采取了“先取锁在访问”的策略,为数据的处理安全提供了保证,但是在效率方面,由于额外的加锁机制产生了额外的开销,并且增加了死锁的机会。并且降低了并发性;当一个事务加锁一行数据的时候,其他事务必须等待该事务提交之后,才能操作这行数据。

    4.2乐观锁

    在关系数据库管理系统里,乐观并发控制又名“乐观锁”,Optimistic Concurrency Control,缩写“OCC”)是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响,各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前,每个事务会先检查在该事务读取数据后,有没有其他事务又修改了该数据。如果其他事务有更新的话,正在提交的事务会进行回滚。

    乐观锁( Optimistic Locking ) 相对悲观锁而言,乐观锁假设认为数据一般情况下不会造成冲突,所以在数据进行提交更新的时候,才会正式对数据的冲突与否进行检测,如果发现冲突了,则让返回用户错误的信息,让用户决定如何去做。

    相对于悲观锁,在对数据库进行处理的时候,乐观锁并不会使用数据库提供的锁机制一般的实现乐观锁的方式就是记录数据版本。

    mysql实现乐观锁一般来说有2种方式:
    1.使用数据版本(Version)记录机制实现,这是乐观锁最常用的一种实现方式。
    一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时,将version字段的值一同读出,数据每更新一次,对此version值加一。
    当提交更新的时候,判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对,如果数据库表当前版本号与第一次取出来的version值相等,就进行更新操作,否则认为是过期数据,正在提交的事务会进行回滚。
    2.第二种实现方式和第一种差不多,同样是在需要乐观锁控制的table中增加一个字段,名称无所谓,字段类型使用时间戳(timestamp), 和上面的version类似,也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比,如果一致就更新,否则就是版本冲突。

    乐观锁的优点和不足:
    ​ 乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的,因此尽可能直接做下去,直到提交的时候才去锁定,所以不会产生任何锁和死锁。但如果直接简单这么做,还是有可能会遇到不可预期的结果,例如两个事务都读取了数据库的某一行,经过修改以后写回数据库,这时就遇到了问题。

    乐观锁和MVCC的区别?
    在数据库中,并发控制是指在多个用户/进程/线程同时对数据库进行操作时,如何保证事务的一致性和隔离性的,同时最大程度地并发。

    当多个用户/进程/线程同时对数据库进行操作时,会出现3种冲突情形:

    1. 读-读,不存在任何问题
    2. 读-写,有隔离性问题,可能遇到脏读(会读到未提交的数据) ,幻影读等。
    3. 写-写,可能丢失更新

    要解决冲突,一种办法是是锁,即基于锁的并发控制,比如2PL两阶段锁协议,这种方式开销比较高,而且无法避免死锁。而基于无锁的并发控制有两种方式:就是MVCC多版本并发控制和OCC乐观并发控制,这两种方式分别解决上面的第2,3种情况。

    多版本并发控制(MVCC)是一种用来解决读-写冲突的无锁并发控制,也就是为事务分配单向增长的时间戳,为每个修改保存一个版本,版本与事务时间戳关联,读操作只读该事务开始前的数据库的快照。 这样在读操作不用阻塞写操作,写操作不用阻塞读操作的同时,避免了脏读和不可重复读

    乐观并发控制(OCC)是一种用来解决写-写冲突的无锁并发控制,认为事务间争用没有那么多,所以先进行修改,在提交事务前,检查一下事务开始后,有没有新提交改变,如果没有就提交,如果有就放弃并重试。乐观并发控制类似自旋锁。乐观并发控制适用于低数据争用,写冲突比较少的环境。

    多版本并发控制可以结合基于锁的并发控制来解决写-写冲突,即MVCC+2PL,也可以结合乐观并发控制来解决写-写冲突。

    5.1InnoDB锁的特性

    1. 在不通过索引条件查询的时候,InnoDB使用的确实是表锁(锁的是整张表)!
    2. 由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突的。
    3. 当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论 是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。
    4. 即便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同 执行计划的代价来决定的,如果 MySQL 认为全表扫效率更高,比如对一些很小的表,它 就不会使用索引,这种情况下 InnoDB 将使用表锁,而不是行锁。因此,在分析锁冲突时, 别忘了检查SQL 的执行计划(explain查看),以确认是否真正使用了索引。

    1.通过非索引项检索数据,加表锁!

    price属性并没有加索引,因此这时候虽然是用的行锁,锁的却是整张表
    窗口1:
    mysql> select * from product where price=88 for update;
    +----+------+-------+-----+
    | id | name | price | num |
    +----+------+-------+-----+
    |  2 | 蒙牛 |    88 |   1 |
    +----+------+-------+-----+
    
    窗口2:
    mysql> update product set price=price-100 where id=6;
    这里会等待,直到窗口1 commit后显示下面结果!
    Query OK, 1 row affected
    Rows matched: 1  Changed: 1  Warnings: 0
    

    2.使用相同索引值但是不同行引发的冲突

    这里的num属性 加上了普通索引,price属性并没有索引
    窗口1:
    mysql> set autocommit=0;
    Query OK, 0 rows affected
    
    mysql> select * from product where num=1 and price=68 for update;
    +----+------+-------+-----+
    | id | name | price | num |
    +----+------+-------+-----+
    |  1 | 伊利 |    68 |   1 |
    +----+------+-------+-----+
    
    窗口2:
    mysql> update product set price=price+100 where num=1 and price=88;
    这里会发生等待,直到窗口1 commit 显示下面结果
    Query OK, 1 row affected
    Rows matched: 1  Changed: 1  Warnings: 0
    mysql> select * from product;
    +----+----------+-------+-----+
    | id | name     | price | num |
    +----+----------+-------+-----+
    |  1 | 伊利     |    68 |   1 |
    |  2 | 蒙牛     |   188 |   1 |
    +----+----------+-------+-----+
    

    3.当使用索引检索数据时不同事务可以操作不同行数据

    锁一行数据,DML操作其他行并没有影响
    窗口1:
    mysql> select * from user where id=1 for update;
    +----+-------+
    | id | price |
    +----+-------+
    |  1 |   400 |
    +----+-------+
    
    窗口2:
    mysql> update user set price=price+100 where id=2;
    无需等待窗口1 commit
    Database changed
    Rows matched: 1  Changed: 1  Warnings: 0
    

    DML(Data Manipulation Language)数据操纵语言,对数据库中的数据进行一些简单操作,如insert、delete、update、select等。DML操作是可以手动控制事务的开启、提交和回滚的。
    DDL(Data Definition Language)数据定义语言,对数据库中的某些对象(例如database、table)进行管理,如create、alter和drop。DDL操作是隐性提交的,不能rollback。
    DDL有助于更改数据库的结构,而DML有助于管理数据库中的数据。

    参考:MySQL的锁机制和加锁原理

    展开全文
  • MYSQL锁机制全揭秘

    2017-10-12 14:15:07
    MYSQL锁机制全揭秘,锁对数据库而言显得尤其重要,也更加复杂。
  • mysql 表与解锁

    千次阅读 2022-05-25 18:30:58
    #查看是否开启定时器 MYSQL 解锁与表和批解锁 第一种 show processlist; 找到进程,kill id ; 第二种 mysql>UNLOCK TABLES; 表 锁定数据表,避免在备份过程中,表被更新 mysql>LOCK TABLES tbl_name READ; 为...

    show processlist;
    kill 6924
    show full processlist;  #列出全部进程
    select * from information_schema.PROCESSLIST t;
    kill 6911
    SELECT * FROM information_schema.innodb_trx ORDER BY trx_started ;
    SELECT * FROM information_schema.innodb_locks;
    SELECT * FROM information_schema.innodb_trx t where t.trx_state = 'LOCK WAIT';
    select * from information_schema.PROCESSLIST t where t.id = {lock_trx_id}

    #状态一直在休眠,关闭事件调节器[7]:
    SET GLOBAL event_scheduler = OFF;
     #查看是否开启定时器
    SHOW  VARIABLES  LIKE 'event_scheduler'; SHOW  VARIABLES  LIKE 'event_scheduler';   #查看是否开启定时器

    MYSQL 解锁与锁表和批解锁

    第一种

    show processlist;

    找到锁进程,kill id ;

    第二种

    mysql>UNLOCK TABLES;

    锁表

    锁定数据表,避免在备份过程中,表被更新

    mysql>LOCK TABLES tbl_name READ;

    为表增加一个写锁定:

    mysql>LOCK TABLES tbl_name WRITE;

    查询是否锁表

    show OPEN TABLES where In_use > 0;

    SHOW OPEN TABLES;

    // 解锁

    UNLOCK TABLES;

    查看表的状态

    SHOW STATUS LIKE 'table%';

    SHOW STATUS LIKE 'innodb_row_lock%';

    第三种

    SELECT concat('KILL ',id,';') FROM information_schema.processlist WHERE user='root';

    KILL 755;

    KILL 756;

    展开全文
  • 本文实例讲述了MYSQL锁表问题的解决方法。分享给大家供大家参考,具体如下: 很多时候!一不小心就锁表!这里讲解决锁表终极方法! 案例一 mysql>show processlist; 参看sql语句 一般少的话 mysql>kill thread_...
  • 腾讯云数据库负责人林晓斌说过:“我们面试MySQL同事时只考察两点,索引和锁”。言简意赅,MySQL锁的重要性不言而喻。 本文通过同事“侨总”的一场面试,带你通俗易懂的掌握MySQL各种锁机制,希望可以帮到你!
  • mysql锁

    千次阅读 2022-05-04 09:51:16
    mysql锁表的原因是什么 在mysql中,锁表的原因是一个程序执行了对表的insert、update或者delete操作还未commite时,另一个程序也对同一个表进行相同的操作,则此时会发生资源正忙的异常,也就是锁表。 本教程操作...
  • 前几天同事在晚上上线的时候执行sql语句造成表,想总结一下以避免后续发生。 (1) 遇到表快速解决办法 依次执行1-6步,运行第6步生成的语句即可。 如果...
  • mysql锁表处理

    千次阅读 2022-03-28 15:30:18
    mysql锁表处理
  • mysql锁表如何解锁

    千次阅读 2021-02-01 21:42:15
    什么是MySQL锁表?为了给高并发情况下的mysql进行更好的优化,有必要了解一下mysql查询更新时的锁表机制。MySQL有三种锁的级别:页级、表级、行级。MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);BDB...
  • mysql锁表查询(mysql查询锁表语句)

    千次阅读 2021-01-18 18:31:59
    mysql锁表查询(mysql查询锁表语句)2020-07-24 11:44:55共10个回答怎么查找mysql中的锁表语句showprocesslist;SHOWPROCESSLIST显示哪些线程正在运行.您也可以使用mysqladminprocesslist语句得到此信息.如果您有SUPER...
  • 解决mysql锁表的终极方法

    千次阅读 2021-01-18 20:30:08
    解决mysql锁表的终极方法案例一mysql>showprocesslist;参看sql语句,一般少的话mysql>killthread_id;就可以解决了,kill掉第一个锁表的进程,依然没有改善。既然不改善,就想办法将所有锁表的进程kill掉吧,...
  • MYSQL数据库MYSQL 解锁与表介绍

    千次阅读 2020-12-21 04:25:16
    MYSQL学习MySQL锁概述相对其他数据库而言,MySQL的锁机制比拟简单,其最显著的特点是不同的存储引擎支持不同的锁机制.比如,MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);BDB存储引擎采用的是页面锁(.....
  • 数据库篇:mysql锁详解

    千次阅读 2022-03-28 17:53:23
    排他(X),当前事务给记录上后(insert update delete),可以进行读写,其他事务不可以加任何 共享(S),是指当前事务给一条记录上后,其他事务也可以给当前记录加共享。共享只用于锁定读,如...
  • Mysql锁机制之行锁、表锁、死锁

    千次阅读 2022-03-08 22:15:52
    乐观用的最多的就是数据的版本记录来体现 version ,其实就是一个标识。 例如:update test set a=a-1 where id=100 and a> 0; 对应的version就是a字段,并不一定非得要求有一个字段叫做version,要求的是有这个...
  • MySQL锁类型以及查询锁表问题、解锁MySQL中select * for update锁表的范围MySQL中select * for update锁表的问题由
  • mysql锁表1

    2022-08-08 18:46:23
    mysql锁表1
  • MySql锁表-解锁方法

    千次阅读 2022-03-02 20:23:21
    -- 表解决方法 show full processlist; -- 显示完整的进程列表 select * from information_schema.innodb_trx; -- 查看有是哪些事务占据了表资源 kill 436;
  • mysql 库处理

    千次阅读 2022-03-29 16:03:37
    查询所有的进程: show FULL PROCESSLIST 查询情况列表: SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 解决锁定: kill 2843016; 2843016 为进程ID
  • 一篇学会:mysql锁表查询和解锁操作

    千次阅读 2021-12-13 09:29:20
    在做数据库操作时,有时会因为自己的粗心或者程序设计上的缺陷导致表,在mysql中查看表和解锁的步骤如下: //1.查看当前数据库表的情况 SELECT * FROM information_schema.INNODB_TRX; //2.杀掉查询结果...
  • mysql 表查询

    千次阅读 2021-11-26 14:16:31
    ①.id列,用户登录mysql时,系统分配的"connection_id",可以使用函数connection_id()查看 ②.user列,显示当前用户。如果不是root,这个命令就只显示用户权限范围的sql语句 ③.host列,显示这个语句是从哪个ip的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 282,455
精华内容 112,982
关键字:

mysql锁

mysql 订阅
友情链接: Matrix.rar