精华内容
下载资源
问答
  • MySQL行锁和表锁

    2021-03-22 21:34:19
    文章目录MySQL行锁和表锁表锁行锁注意事项间隙锁总结 MySQL行锁和表锁 MySQL常用引擎有MyISAM和InnoDB,而InnoDB是mysql默认的引擎。MyISAM不支持行锁,而InnoDB支持行锁和表锁。 MyISAM在执行select时,会自动给...

    MySQL行锁和表锁

    MySQL常用引擎有MyISAM和InnoDB,而InnoDB是mysql默认的引擎。MyISAM不支持行锁,而InnoDB支持行锁和表锁。

    MyISAM在执行select时,会自动给所有涉及的表加读锁,在执行update、delete、insert前,会给所有涉及的表加写锁,无需用户操作。
    读锁:

    select  math from zje where math>60 lock in share mode

    写锁:

    select math from zje where math >60 for update

    表锁

    适用表锁不会出现死锁,发生锁冲突的几率高,并发量低。
    MySQL的表锁表现形式:

    • 表共享读锁(对MyISAM表的读操作,不会阻塞其他线程对同一表的读请求,但无法进行写操作,除非读锁被释放)
    • 表独占写锁(对MyISAM表的写操作,会阻塞其他线程对同一表的读写操作,除非写锁别释放)

    MyISAM不适合作为主表的引擎,因为在进行写操作时,其他的线程全被阻塞了。

    行锁

    MySQL的行锁加载在索引响应的行上的,如果对应的sql语句没有适用索引的话,则会进行全盘扫描,无法适用行锁,而是适用表锁,其他事务无法对当前表进行更新或插入操作。
    如果如上所示在一条select后面加上for update 查询的数据会加上一条排他锁,其他事务可以进行读操作,但不能进行写操作。

    注意事项

    • 行锁必须是通过索引来实现的,否则的话就是表锁
    • 不同的事务不能锁住相同的索引
    • 事务中如果有写操作(update、delete、insert),会自动加上排他锁

    间隙锁

    当适用范围查询检索数据时,如果请求了共享锁或者排他锁,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内并不存在的记录,叫做间隙InnoDB也会对这个"间隙"加锁,这种锁机制就是所谓的间隙锁

    总结

    • 尽量控制事务大小,减少锁定资源量和时间长度
    • 合理设计索引,尽量缩小锁的范围
    • 尽可能减少索引条件,避免间隙锁
    • 尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁
    展开全文
  • 主要介绍了MySQL 行锁和表锁的含义及区别详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
  • mysql行锁和表锁

    2019-10-31 10:10:14
    在调用存储过程中,就会涉及到表锁行锁这一概念:所谓区别:有索引的时候就是行锁,没有索引的时候就是表索。 innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的. 一、表锁演示(无索引): Session1:...

    在调用存储过程中,就会涉及到表锁,行锁这一概念:所谓区别:有索引的时候就是行锁,没有索引的时候就是表索。

    innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的.

    一、表锁演示(无索引):

    Session1:

    mysql> set autocommit=0;

    mysql> select * from innodb_test;
    +------+-------------+
    | id   | name        |
    +------+-------------+
    |    1 | woshiceshi  | 
    |    2 | woshiceshi2 | 
    |    3 | woshiceshi3 | 
    +------+-------------+

    mysql> select * from innodb_test where id = 2 for update;
    +------+------------+
    | id   | name       |
    +------+------------+
    |    2 | woshiceshi2 | 
    +------+------------+

    Session2:

    mysql> update innodb_test set name='sjis' where id = 1 ;
    处于等待状态....

    再回到session1 commit以后,session2就出来结果了(锁定了8秒,过了8秒左右才去session1提交)。

    mysql> update innodb_test set name='sjis' where id = 1 ;
    Query OK, 1 row affected (8.11 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    实验结果是:我在session1的for update 操作看似只锁定ID为2的行其实锁定了全表,以至于后面session2的对ID为1的行update 需要等待Session1锁的释放。

    二、行锁演示(索引为ID)

    Session1:
    mysql> alter table innodb_test add index idx_id(id);
    Query OK, 4 rows affected (0.01 sec)
    Records: 4  Duplicates: 0  Warnings: 0


    mysql> select * from innodb_test where id = 2 for update;
    +------+------------+
    | id   | name       |
    +------+------------+
    |    2 | woshiceshi2 | 
    +------+------------+

    Session2:

    mysql> update innodb_test set name='wohaishiceshi' where id = 1 ;
    Query OK, 1 row affected (0.02 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    mysql> select * from innodb_test where id = 1;           
    +------+---------------+
    | id   | name          |
    +------+---------------+
    |    1 | wohaishiceshi | 
    +------+---------------+
    1 row in set (0.00 sec)

    实验结果:这次的锁定是锁定的行,所以没有被锁定的行(ID不为2的行)可以进行update..

    参考链接:https://blog.csdn.net/songwei128/article/details/43418343

     

     

    展开全文
  • Mysql行锁和表锁

    2021-02-08 12:42:48
    1.表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单,带来的系统负面影响最小。所以获取锁释放锁的速度很快。由于表级锁一次会将整个表锁定,所以可以很好的避免...

    1.表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单,带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定,所以可以很好的避免困扰我们的死锁问题。锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高,致使并大度大打折扣。

    2.行级锁定最大的特点就是锁定对象的颗粒度很小,也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小,所以发生锁定资源争用的概率也最小,能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。虽然能够在并发处理能力上面有较大的优势,但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小,所以每次获取锁和释放锁需要做的事情也更多,带来的消耗自然也就更大了。此外,行级锁定也最容易发生死锁。

    3.页级锁定是MySQL中比较独特的一种锁定级别,在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间,所以获取锁定所需要的资源开销,以及所能提供的并发处理能力也同样是介于上面二者之间。另外,页级锁定和行级锁定一样,会发生死锁。

    三种锁的区别
    1.表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低;
    2.行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高;
    3.页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

    适用:从锁的角度来说,表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用;而行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用

    表级锁定

    MySQL的表级锁有两种模式:表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)。锁模式的兼容性:
    对MyISAM表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;
    对MyISAM表的写操作,则会阻塞其他用户对同一表的读和写操作;

    MyISAM表的读操作与写操作之间,以及写操作之间是串行的。当一个线程获得对一个表的写锁后,只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待,直到锁被释放为止。

    MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此,用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁

    对于MyISAM存储引擎,虽然使用表级锁定在锁定实现的过程中比实现行级锁定或者页级锁所带来的附加成本都要小,锁定本身所消耗的资源也是最少。但是由于锁定的颗粒度比较到,所以造成锁定资源的争用情况也会比其他的锁定级别都要多,从而在较大程度上会降低并发处理能力。所以,在优化MyISAM存储引擎锁定问题的时候,最关键的就是如何让其提高并发度。由于锁定级别是不可能改变的了,所以我们首先需要尽可能让锁定的时间变短,然后就是让可能并发进行的操作尽可能的并发。

    (1)查询表级锁争用情况
    MySQL内部有两组专门的状态变量记录系统内部锁资源争用情况: mysql> show status like ‘table%’;
    在这里插入图片描述
    Table_locks_immediate:产生表级锁定的次数;
    Table_locks_waited:出现表级锁定争用而发生等待的次数;
    两个状态值都是从系统启动后开始记录,出现一次对应的事件则数量加1。如果这里的Table_locks_waited状态值比较高,那么说明系统中表级锁定争用现象比较严重,就需要进一步分析为什么会有较多的锁定资源争用了

    说到MyISAM的表锁,而且是读写互相阻塞的表锁,可能有些人会认为在MyISAM存储引擎的表上就只能是完全的串行化,没办法再并行了。大家不要忘记了,MyISAM的存储引擎还有一个非常有用的特性,那就是ConcurrentInsert(并发插入)的特性。

    MyISAM存储引擎有一个控制是否打开Concurrent Insert功能的参数选项:concurrent_insert,可以设置为0,1或者2。三个值的具体说明如下:
    concurrent_insert=2,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录;
    concurrent_insert=1,如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录。这也是MySQL的默认设置;
    concurrent_insert=0,不允许并发插入。
    可以利用MyISAM存储引擎的并发插入特性,来解决应用中对同一表查询和插入的锁争用。例如,将concurrent_insert系统变量设为2,总是允许并发插入;同时,通过定期在系统空闲时段执行OPTIMIZE TABLE语句来整理空间碎片,收回因删除记录而产生的中间空洞。

    MyISAM存储引擎的是读写互相阻塞的,那么,一个进程请求某个MyISAM表的读锁,同时另一个进程也请求同一表的写锁,MySQL如何处理呢?
    答案是写进程先获得锁。不仅如此,即使读请求先到锁等待队列,写请求后到,写锁也会插到读锁请求之前。
    这是因为MySQL的表级锁定对于读和写是有不同优先级设定的,默认情况下是写优先级要大于读优先级。
    所以,如果我们可以根据各自系统环境的差异决定读与写的优先级:
    通过执行命令SET LOW_PRIORITY_UPDATES=1,使该连接读比写的优先级高。如果我们的系统是一个以读为主,可以设置此参数,如果以写为主,则不用设置;
    通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性,降低该语句的优先级。
    虽然上面方法都是要么更新优先,要么查询优先的方法,但还是可以用其来解决查询相对重要的应用(如用户登录系统)中,读锁等待严重的问题。

    另外,MySQL也提供了一种折中的办法来调节读写冲突,即给系统参数max_write_lock_count设置一个合适的值,当一个表的读锁达到这个值后,MySQL就暂时将写请求的优先级降低,给读进程一定获得锁的机会。

    这里还要强调一点:一些需要长时间运行的查询操作,也会使写进程“饿死”,因此,应用中应尽量避免出现长时间运行的查询操作,不要总想用一条SELECT语句来解决问题,因为这种看似巧妙的SQL语句,往往比较复杂,执行时间较长,在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”,使每一步查询都能在较短时间完成,从而减少锁冲突。如果复杂查询不可避免,应尽量安排在数据库空闲时段执行,比如一些定期统计可以安排在夜间执行。

    行级锁定
    行级锁定不是MySQL自己实现的锁定方式,而是由其他存储引擎自己所实现的,如广为大家所知的InnoDB存储引擎
    InnoDB的行级锁定同样分为两种类型,共享锁和排他锁,而在锁定机制的实现过程中为了让行级锁定和表级锁定共存,InnoDB也同样使用了意向锁(表级锁定)的概念,也就有了意向共享锁和意向排他锁这两种。

    当一个事务需要给自己需要的某个资源加锁的时候,如果遇到一个共享锁正锁定着自己需要的资源的时候,自己可以再加一个共享锁,不过不能加排他锁。但是,如果遇到自己需要锁定的资源已经被一个排他锁占有之后,则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。而意向锁的作用就是当一个事务在需要获取资源锁定的时候,如果遇到自己需要的资源已经被排他锁占用的时候,该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁,那么就在表上面添加一个意向共享锁。而如果自己需要的是某行(或者某些行)上面添加一个排他锁的话,则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个,但是意向排他锁同时只能有一个存在。所以,可以说InnoDB的锁定模式实际上可以分为四种:共享锁(S),排他锁(X),意向共享锁(IS)和意向排他锁(IX),我们可以通过以下表格来总结上面这四种所的共存逻辑关系:
    在这里插入图片描述

    意向锁:
    InnoDB所用的表级锁,其设计目的主要是为了在一个事务中揭示下一步将要被请求的锁的类型。
    InnoDB中的两个表锁:
    意向共享锁(IS):表示事务准备给数据行加入共享锁,也就是说一个数据行加共享锁前必须先取得该表的IS锁
    意向排他锁(IX):类似上面,表示事务准备给数据行加入排他锁,说明事务在一个数据行加排他锁前必须先取得该表的IX锁。
    意向锁是InnoDB自动加的,不需要用户干预。

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

    意向锁是InnoDB自动加的,不需用户干预。对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);对于普通SELECT语句,InnoDB不会加任何锁;事务可以通过以下语句显示给记录集加共享锁或排他锁。

    共享锁(S):SELECT * FROM table_name WHERE … LOCK IN SHARE MODE

    排他锁(X):SELECT * FROM table_name WHERE … FOR UPDATE

    用SELECT … IN SHARE MODE获得共享锁,主要用在需要数据依存关系时来确认某行记录是否存在,并确保没有人对这个记录进行UPDATE或者DELETE操作。

    但是如果当前事务也需要对该记录进行更新操作,则很有可能造成死锁,对于锁定行记录后需要进行更新操作的应用,应该使用SELECT… FOR UPDATE方式获得排他锁。

    2.InnoDB行锁实现方式
    InnoDB行锁是通过给索引上的索引项加锁来实现的,只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁
    在实际应用中,要特别注意InnoDB行锁的这一特性,不然的话,可能导致大量的锁冲突,从而影响并发性能。下面通过一些实际例子来加以说明。
    (1)在不通过索引条件查询的时候,InnoDB确实使用的是表锁,而不是行锁。
    (2)由于MySQL的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突的。
    (3)当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。
    (4)即便在条件中使用了索引字段,但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的,如果MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下InnoDB将使用表锁,而不是行锁。因此,在分析锁冲突时,别忘了检查SQL的执行计划,以确认是否真正使用了索引。

    3.间隙锁(Next-Key锁)
    当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;
    对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。
    例:
    假如emp表中只有101条记录,其empid的值分别是 1,2,…,100,101,下面的SQL:
    mysql> select * from emp where empid > 100 for update;
    是一个范围条件的检索,InnoDB不仅会对符合条件的empid值为101的记录加锁,也会对empid大于101(这些记录并不存在)的“间隙”加锁。
    InnoDB使用间隙锁的目的:
    (1)防止幻读,以满足相关隔离级别的要求。对于上面的例子,要是不使用间隙锁,如果其他事务插入了empid大于100的任何记录,那么本事务如果再次执行上述语句,就会发生幻读;
    (2)为了满足其恢复和复制的需要。
    很显然,在使用范围条件检索并锁定记录时,即使某些不存在的键值也会被无辜的锁定,而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。
    除了间隙锁给InnoDB带来性能的负面影响之外,通过索引实现锁定的方式还存在其他几个较大的性能隐患:
    (1)当Query无法利用索引的时候,InnoDB会放弃使用行级别锁定而改用表级别的锁定,造成并发性能的降低;
    (2)当Query使用的索引并不包含所有过滤条件的时候,数据检索使用到的索引键所只想的数据可能有部分并不属于该Query的结果集的行列,但是也会被锁定,因为间隙锁锁定的是一个范围,而不是具体的索引键;
    (3)当Query在使用索引定位数据的时候,如果使用的索引键一样但访问的数据行不同的时候(索引只是过滤条件的一部分),一样会被锁定。
    因此,在实际应用开发中,尤其是并发插入比较多的应用,我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件。
    还要特别说明的是,InnoDB除了通过范围条件加锁时使用间隙锁外,如果使用相等条件请求给一个不存在的记录加锁,InnoDB也会使用间隙锁。

    4.死锁
    MyISAM表锁是deadlock free的,这是因为MyISAM总是一次获得所需的全部锁,要么全部满足,要么等待,因此不会出现死锁。但在InnoDB中,除单个SQL组成的事务外,锁是逐步获得的,当两个事务都需要获得对方持有的排他锁才能继续完成事务,这种循环锁等待就是典型的死锁。
    在InnoDB的事务管理和锁定机制中,有专门检测死锁的机制,会在系统中产生死锁之后的很短时间内就检测到该死锁的存在。当InnoDB检测到系统中产生了死锁之后,InnoDB会通过相应的判断来选这产生死锁的两个事务中较小的事务来回滚,而让另外一个较大的事务成功完成。
    那InnoDB是以什么来为标准判定事务的大小的呢?MySQL官方手册中也提到了这个问题,实际上在InnoDB发现死锁之后,会计算出两个事务各自插入、更新或者删除的数据量来判定两个事务的大小。也就是说哪个事务所改变的记录条数越多,在死锁中就越不会被回滚掉。
    但是有一点需要注意的就是,当产生死锁的场景中涉及到不止InnoDB存储引擎的时候,InnoDB是没办法检测到该死锁的,这时候就只能通过锁定超时限制参数InnoDB_lock_wait_timeout来解决。
    需要说明的是,这个参数并不是只用来解决死锁问题,在并发访问比较高的情况下,如果大量事务因无法立即获得所需的锁而挂起,会占用大量计算机资源,造成严重性能问题,甚至拖跨数据库。我们通过设置合适的锁等待超时阈值,可以避免这种情况发生。
    通常来说,死锁都是应用设计的问题,通过调整业务流程、数据库对象设计、事务大小,以及访问数据库的SQL语句,绝大部分死锁都可以避免。下面就通过实例来介绍几种避免死锁的常用方法:
    (1)在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低产生死锁的机会。
    (2)在程序以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定的顺序来处理记录,也可以大大降低出现死锁的可能。
    (3)在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应先申请共享锁,更新时再申请排他锁,因为当用户申请排他锁时,其他事务可能又已经获得了相同记录的共享锁,从而造成锁冲突,甚至死锁。
    (4)在REPEATABLE-READ隔离级别下,如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁,在没有符合该条件记录情况下,两个线程都会加锁成功。程序发现记录尚不存在,就试图插入一条新记录,如果两个线程都这么做,就会出现死锁。这种情况下,将隔离级别改成READ COMMITTED,就可避免问题。
    (5)当隔离级别为READ COMMITTED时,如果两个线程都先执行SELECT…FOR UPDATE,判断是否存在符合条件的记录,如果没有,就插入记录。此时,只有一个线程能插入成功,另一个线程会出现锁等待,当第1个线程提交后,第2个线程会因主键重出错,但虽然这个线程出错了,却会获得一个排他锁。这时如果有第3个线程又来申请排他锁,也会出现死锁。对于这种情况,可以直接做插入操作,然后再捕获主键重异常,或者在遇到主键重错误时,总是执行ROLLBACK释放获得的排他锁。

    5.什么时候使用表锁
    对于InnoDB表,在绝大部分情况下都应该使用行级锁,因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中,也可以考虑使用表级锁:
    (1)事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,不仅这个事务执行效率低,而且可能造成其他事务长时间锁等待和锁冲突,这种情况下可以考虑使用表锁来提高该事务的执行速度。
    (2)事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表,从而避免死锁、减少数据库因事务回滚带来的开销。
    当然,应用中这两种事务不能太多,否则,就应该考虑使用MyISAM表了。
    在InnoDB下,使用表锁要注意以下两点。
    (1)使用LOCK TABLES虽然可以给InnoDB加表级锁,但必须说明的是,表锁不是由InnoDB存储引擎层管理的,而是由其上一层──MySQL Server负责的,仅当autocommit=0、InnoDB_table_locks=1(默认设置)时,InnoDB层才能知道MySQL加的表锁,MySQL Server也才能感知InnoDB加的行锁,这种情况下,InnoDB才能自动识别涉及表级锁的死锁,否则,InnoDB将无法自动检测并处理这种死锁。
    (2)在用 LOCK TABLES对InnoDB表加锁时要注意,要将AUTOCOMMIT设为0,否则MySQL不会给表加锁;事务结束前,不要用UNLOCK TABLES释放表锁,因为UNLOCK TABLES会隐含地提交事务;COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁,必须用UNLOCK TABLES释放表锁。正确的方式见如下语句:
    例如,如果需要写表t1并从表t读,可以按如下做:
    SET AUTOCOMMIT=0;
    LOCK TABLES t1 WRITE, t2 READ, …;
    [do something with tables t1 and t2 here];
    COMMIT;
    UNLOCK TABLES;

    6.InnoDB行锁优化建议
    较低级别的事务隔离,以减少MySQL因为实现事务隔离级别所带来的附加成本。
    (2)由于InnoDB的行级锁定和事务性,所以肯定会产生死锁,下面是一些比较常用的减少死锁产生概率的小建议:
    a)类似业务模块中,尽可能按照相同的访问顺序来访问,防止产生死锁;
    b)在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率;
    c)对于非常容易产生死锁的业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁产生的概率。
    (3)可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况:
    mysql> show status like ‘InnoDB_row_lock%’;

    ±------------------------------±------+

    | Variable_name | Value |

    ±------------------------------±------+

    | InnoDB_row_lock_current_waits | 0 |

    | InnoDB_row_lock_time | 0 |

    | InnoDB_row_lock_time_avg | 0 |

    | InnoDB_row_lock_time_max | 0 |

    | InnoDB_row_lock_waits | 0 |

    ±------------------------------±------+
    InnoDB 的行级锁定状态变量不仅记录了锁定等待次数,还记录了锁定总时长,每次平均时长,以及最大时长,此外还有一个非累积状态量显示了当前正在等待锁定的等待数量。对各个状态量的说明如下:
    InnoDB_row_lock_current_waits:当前正在等待锁定的数量;
    InnoDB_row_lock_time:从系统启动到现在锁定总时间长度;
    InnoDB_row_lock_time_avg:每次等待所花平均时间;
    InnoDB_row_lock_time_max:从系统启动到现在等待最常的一次所花的时间;
    InnoDB_row_lock_waits:系统启动后到现在总共等待的次数;
    对于这5个状态变量,比较重要的主要是InnoDB_row_lock_time_avg(等待平均时长),InnoDB_row_lock_waits(等待总次数)以及InnoDB_row_lock_time(等待总时长)这三项。尤其是当等待次数很高,而且每次等待时长也不小的时候,我们就需要分析系统中为什么会有如此多的等待,然后根据分析结果着手指定优化计划。
    如果发现锁争用比较严重,如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高,还可以通过设置InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等,并分析锁争用的原因。
    锁冲突的表、数据行等,并分析锁争用的原因。具体方法如下:
    mysql> create table InnoDB_monitor(a INT) engine=InnoDB;
    然后就可以用下面的语句来进行查看:
    mysql> show engine InnoDB status;
    监视器可以通过发出下列语句来停止查看:
    mysql> drop table InnoDB_monitor;
    设置监视器后,会有详细的当前锁等待的信息,包括表名、锁类型、锁定记录的情况等,便于进行进一步的分析和问题的确定。可能会有读者朋友问为什么要先创建一个叫InnoDB_monitor的表呢?因为创建该表实际上就是告诉InnoDB我们开始要监控他的细节状态了,然后InnoDB就会将比较详细的事务以及锁定信息记录进入MySQL的errorlog中,以便我们后面做进一步分析使用。打开监视器以后,默认情况下每15秒会向日志中记录监控的内容,如果长时间打开会导致.err文件变得非常的巨大,所以用户在确认问题原因之后,要记得删除监控表以关闭监视器,或者通过使用“–console”选项来启动服务器以关闭写日志文件。

    展开全文
  • 一、前言对于行锁和表锁的含义区别,在面试中应该是高频出现的,我们应该对MySQL中的锁有一个系统的认识,更详细的需要自行查阅资料,本篇为概括性的总结回答。MySQL常用引擎有MyISAM和InnoDB,而InnoDB是mysql默认...

    一、前言

    对于行锁和表锁的含义区别,在面试中应该是高频出现的,我们应该对MySQL中的锁有一个系统的认识,更详细的需要自行查阅资料,本篇为概括性的总结回答。

    MySQL常用引擎有MyISAM和InnoDB,而InnoDB是mysql默认的引擎。MyISAM不支持行锁,而InnoDB支持行锁和表锁。

    如何加锁?

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

    显式加锁:

    上共享锁(读锁)的写法:lock in share mode,例如:select  math from zje where math>60 lock in share mode;

    上排它锁(写锁)的写法:for update,例如:select math from zje where math >60 for update;

    二、表锁

    不会出现死锁,发生锁冲突几率高,并发低。

    MyISAM引擎

    MyISAM在执行查询语句(select)前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁。

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

    表独占写锁

    读锁会阻塞写,写锁会阻塞读和写对MyISAM表的读操作,不会阻塞其它进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。

    对MyISAM表的写操作,会阻塞其它进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作。

    MyISAM不适合做写为主表的引擎,因为写锁后,其它线程不能做任何操作,大量的更新会使查询很难得到锁,从而造成永远阻塞

    三、行锁

    会出现死锁,发生锁冲突几率低,并发高。

    在MySQL的InnoDB引擎支持行锁,与Oracle不同,MySQL的行锁是通过索引加载的,也就是说,行锁是加在索引响应的行上的,要是对应的SQL语句没有走索引,则会全表扫描,行锁则无法实现,取而代之的是表锁,此时其它事务无法对当前表进行更新或插入操作。

    CREATE TABLE `user` (

    `name` VARCHAR(32) DEFAULT NULL,

    `count` INT(11) DEFAULT NULL,

    `id` INT(11) NOT NULL AUTO_INCREMENT,

    PRIMARY KEY (`id`)

    ) ENGINE=INNODB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

    -- 这里,我们建一个user表,主键为id

    -- A通过主键执行插入操作,但事务未提交

    update user set count=10 where id=1;

    -- B在此时也执行更新操作

    update user set count=10 where id=2;

    -- 由于是通过主键选中的,为行级锁,A和B操作的不是同一行,B执行的操作是可以执行的

    -- A通过name执行插入操作,但事务未提交

    update user set count=10 where name='xxx';

    -- B在此时也执行更新操作

    update user set count=10 where id=2;-- 由于是通过非主键或索引选中的,升级为为表级锁,-- B则无法对该表进行更新或插入操作,只有当A提交事务后,B才会成功执行

    for update

    如果在一条select语句后加上for update,则查询到的数据会被加上一条排它锁,其它事务可以读取,但不能进行更新和插入操作-- A用户对id=1的记录进行加锁

    select * from user where id=1 for update;

    -- B用户无法对该记录进行操作

    update user set count=10 where id=1;

    -- A用户commit以后则B用户可以对该记录进行操作

    行锁的实现需要注意:行锁必须有索引才能实现,否则会自动锁全表,那么就不是行锁了。

    两个事务不能锁同一个索引。

    insert,delete,update在事务中都会自动默认加上排它锁。

    行锁场景:

    A用户消费,service层先查询该用户的账户余额,若余额足够,则进行后续的扣款操作;这种情况查询的时候应该对该记录进行加锁。

    否则,B用户在A用户查询后消费前先一步将A用户账号上的钱转走,而此时A用户已经进行了用户余额是否足够的判断,则可能会出现余额已经不足但却扣款成功的情况。

    为了避免此情况,需要在A用户操作该记录的时候进行for update加锁

    扩展:间隙锁

    当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内并不存在的记录,叫做间隙

    InnoDB也会对这个"间隙"加锁,这种锁机制就是所谓的间隙锁-- 用户A

    update user set count=8 where id>2 and id<6

    -- 用户B

    update user set count=10 where id=5;

    如果用户A在进行了上述操作后,事务还未提交,则B无法对2~6之间的记录进行更新或插入记录,会阻塞,当A将事务提交后,B的更新操作会执行。

    建议:尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁

    合理设计索引,尽量缩小锁的范围

    尽可能减少索引条件,避免间隙锁

    尽量控制事务大小,减少锁定资源量和时间长度

    展开全文
  • 原标题:mysql表锁和行锁的区别是什么Mysql有很多这种锁机制,比如行锁表锁等,读锁,写锁等,都是在做操作之前先上锁;这些锁统称为悲观锁(Pessimistic Lock)。下面本篇就来带大家了解一下mysql中的锁,介绍表锁...
  • 一、前言对于行锁和表锁的含义区别,在面试中应该是高频出现的,我们应该对MySQL中的锁有一个系统的认识,更详细的需要自行查阅资料,本篇为概括性的总结回答。MySQL常用引擎有MyISAM和InnoDB,而InnoDB是mysql默认...
  • 一、前言对于行锁和表锁的含义区别,在面试中应该是高频出现的,我们应该对MySQL中的锁有一个系统的认识,更详细的需要自行查阅资料,本篇为概括性的总结回答。MySQL常用引擎有MyISAM和InnoDB,而InnoDB是mysql默认...
  • MySQLMySQL(InnoDB存储引擎)默认是自动提交事务的,所以这个测试,...这里我主要针对的是悲观锁,其实也就是行锁和表锁,SQL 加上 FOR UPDATE 即可行锁这个时候,我们再开启一个客户端访问MySQL,输入同一条加锁的SQ...
  • 对于行锁和表锁的含义区别,在面试中应该是高频出现的,我们应该对MySQL中的锁有一个系统的认识,更详细的需要自行查阅资料,本篇为概括性的总结回答。 MySQL常用引擎有MyISAM和InnoDB,而InnoDB是mysql默认的引擎。...
  • 在调用存储过程中,就会涉及到表锁行锁这一概念:所谓区别:有索引的时候就是行锁,没有索引的时候就是表索。innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的.表锁演示(无索引)Session1:mysql> set ...
  • MyISAM不支持行锁,而InnoDB支持行锁和表锁。 如何加锁? MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁,这个过程并...
  • 对于行锁和表锁的含义区别,在面试中应该是高频出现的,我们应该对MySQL中的锁有一个系统的认识,更详细的需要自行查阅资料,本篇为概括性的总结回答。 MySQL常用引擎有MyISAM和InnoDB,而InnoDB是mysql默认的引擎...
  • 在调用存储过程中,就会涉及到表锁行锁这一概念:所谓区别:有索引的时候就是行锁,没有索引的时候就是表索。innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的.表锁演示(无索引)Session1:mysql> set ...
  • 程序员的成长之路互联网/程序员/技术/资料共享关注阅读本文大概需要 4分钟。来自:网络一、前言对于行锁和表锁的含义区别,在面试中应该是高频出现的,我们应该对MySQL中的锁有一个系统...
  • 锁是计算机协调多个进程或纯线程并发访问某一资源的机制。在数据库中,除传统的计算资源(CPU、RAM、I/O)的争用以外,数据也是一种供许多...概述相对其他数据库而言,MySQL的锁机制比较简单,其最显著的特点是不同...
  • 在调用存储过程中,就会涉及到表锁行锁这一概念:所谓区别:有索引的时候就是行锁,没有索引的时候就是表索。innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的.表锁演示(无索引)Session1:mysql> set ...
  • 在调用存储过程中,就会涉及到表锁行锁这一概念:所谓区别:有索引的时候就是行锁,没有索引的时候就是表索。innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的.表锁演示(无索引)Session1:mysql> set ...
  • 锁在数据网络传输中是一个非常重要的概念,当多个用户对数据库进行操作时,会带来数据不一致的情况,所以,锁主要是在多用户情况下保证数据库数据完整性一致性。当然,数据库中的锁远不止于上面提到的两种。通常...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,151
精华内容 860
关键字:

mysql行锁和表锁

mysql 订阅