• Shared and Exclusive Locks 共享和排它锁


    14.5 InnoDB Locking and Transaction Model InnoDB 锁和事务模型
    
    14.5.1 InnoDB Locking
    14.5.2 InnoDB Transaction Model
    14.5.3 Locks Set by Different SQL Statements in InnoDB
    14.5.4 Phantom Rows
    14.5.5 Deadlocks in InnoDB
    
    
    实现一个大规模的,繁忙的,或者可扩展的数据库应用,从不同数据库系统的大量代码,或者调整MySQL 性能,
    
    它是重要的了解InnoDB 锁和InnoDB  事务模型
    
    
    本节讨论几个议题关于InnoDB 锁和InnoDB 事务模型:
    
    14.5.1 InnoDB Locking
    
    这个章节描述InnoDB使用的锁的类型:
    
    
    
        Shared and Exclusive Locks
    
        Intention Locks
    
        Record Locks
    
        Gap Locks
    
        Next-Key Locks
    
        Insert Intention Locks
    
        AUTO-INC Locks 
    
    Shared and Exclusive Locks 共享和排它锁
    
    InnoDB 实现标准的航机所 有两种类型的锁,shared (S) locks and exclusive (X) locks.  共享和排它锁
    
    1.一个共享锁允许事务是有一个锁来读取一行
    
    2.一个排它锁允许事务持有锁来更新或者删除记录
    
    
    如果事务T1 持有一个共享锁在记录r, 另外一个事务T2 请求一个lock 在记录r 是如下处理:
    
    
    1.T2请求S锁可以立即被授予,作为结果,T1和T2 都持有S锁在记录r上
    
    
    2. T2请求X锁不能马上授权
    
    
    如果一个事务是有一个排它锁在记录r上,从其他事务T2请求任何类型的锁都不能被立即授予。
    
    相反, 事务T2 只能等待事务T1释放它在记录r上的锁
    
    
    ·共享锁(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
    
    ·排他锁(X):SELECT * FROM table_name WHERE ... FOR UPDATE。
    
    
    
    模式:
    
    第一种情况 session1 持有共享锁:
    
    
    Session 1加共享锁:
    
    
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from aa where username='admin' LOCK IN SHARE MODE;
    +----------+----------------------------------+-----------+
    | username | password                         | authority |
    +----------+----------------------------------+-----------+
    | admin    | 0192023a7bbd73250516f069df18b500 |         1 |
    +----------+----------------------------------+-----------+
    1 row in set (0.00 sec)
    
    Session 2此时也可以加共享锁:
    
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql>  select * from aa where username='admin' LOCK IN SHARE MODE;
    +----------+----------------------------------+-----------+
    | username | password                         | authority |
    +----------+----------------------------------+-----------+
    | admin    | 0192023a7bbd73250516f069df18b500 |         1 |
    +----------+----------------------------------+-----------+
    1 row in set (0.00 sec)
    
    
    
    
    mysql>  select * from aa where username='admin' for update;
    
    加排它锁会HANG
    
    
    
    
    第一种情况 session2 持有排它锁:
    
    Session 1持有排它锁:
    
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from aa where username='admin' for update;
    +----------+----------------------------------+-----------+
    | username | password                         | authority |
    +----------+----------------------------------+-----------+
    | admin    | 0192023a7bbd73250516f069df18b500 |         1 |
    +----------+----------------------------------+-----------+
    1 row in set (0.00 sec)
    
    
    Session 2:
    
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from aa where username='admin' LOCK IN SHARE MODE;
    
    无法获取共享锁
    
    也无法获取排它锁

  • 相关阅读:
    fiddler居然有mac版本了
    java学习笔记02 导入,方法调用,私有公有,静态非静态
    apscheduler笔记
    java学习笔记01 类型,List,Set,循环
    fiddler笔记
    为什么有些端口不能用?
    ubuntu借网
    filecoin
    django密码生成
    python-panda
  • 原文地址:https://www.cnblogs.com/hzcya1995/p/13350140.html
Copyright © 2020-2023  润新知