Shared and Exclusive Locks 共享和排它锁

来源：互联网发布：高考文言文阅读知乎编辑：程序博客网时间：2024/05/18 21:43
14.5 InnoDB Locking and Transaction Model InnoDB 锁和事务模型14.5.1 InnoDB Locking14.5.2 InnoDB Transaction Model14.5.3 Locks Set by Different SQL Statements in InnoDB14.5.4 Phantom Rows14.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;无法获取共享锁也无法获取排它锁
0 0