一、锁及锁的类型
1.锁机制
- 在多用户数据库系统中，用户可以锁定一个对象，可以防止其他用户修改锁定的对象
- 多个并发用户访问数据库数据的情况下，为了保证数据的完整性，锁是必需的
- 锁作为关联到数据项的对象，程序可以显示对数据加锁，数据库系统也可以隐式的对对象进行加锁
2.锁的分类
- Shared locks: 共享锁，多个用户可以同时读取相同的记录
- Exclusive locks: 排他锁，同一时间仅仅有一个用户可以读取相同的记录
- Promotable (Update) lock: 提升锁，可以升级 (从共享锁提升为排他锁) 或者降级
- Intent lock: 专一锁，是一种表级锁，标识在该表上有一个游标在读取数据
3.锁的类型介绍
（1）共享锁（lock-S）
- 如果一个对象上没有排他锁，则共享锁可以加在该对象上
- 防止其他事务更新数据
- 但同时，其他事务可以读取该数据 (其他事务可以加共享锁)
- 多个事务可以在同一个对象上加多个共享锁
（2）排他锁 (lock-X)
- 如果记录上没有任何锁，排他锁才可以加在该对象上
- 一旦在记录上加了排他锁，则不能在该记录上增加任何锁了，直至锁释放
- 防止其他事务读取和更修数据
（3）更新锁 (lock-U)
- 在更新Cursor中使用
- 更新锁由cursors 含有 ‘for update’ 选项执行时产生的 ，只能在没有排他锁或者更新锁的记录上加更新锁
- 当锁定的记录真正执行的时候，更新锁将提升为排他锁
（4）专一锁 (lock-IX or IS)
- 由IDS自动分配
- 如果一条记录上的记录被更新，一个排他锁将分配在该记录上，同时将该记录的表上自动加上专一锁
- 这能保证没有session可以在该表上增加排他锁，只要该表中有记录被增加了排他锁
4.锁的兼容矩阵
S | X | |
S | TRUE | FALSE |
X | FALSE | FALSE |
5.锁的有效期
- 程序控制数据库锁的有效期，当数据库关闭后，数据库锁将被释放
- 根据数据库是否使用了事务的情况，表锁的有效期不同【如果数据没有使用事务（没有事务日志，也不使用commit work语句），显示对一个表lock，当执行unload table时，锁将被释放;】
- 当数据库使用了事务，事务结束时，将释放事务所有的锁 table, row, page, and index locks
6.锁的粒度及应用
（1）锁的粒度介绍
锁粒度 | 说明 |
数据库锁 | 针对整个数据库的锁 |
表锁 | 针对整个表的锁 |
页锁 | 针对整页数据的锁 |
行锁 | 针对一个数据行的锁 |
字节锁 | 在包含varchar的行上的锁 |
键锁 | 在索引中的一个键值上的锁 |
（2）不同锁粒度的应用
一般来讲，锁的粒度越大，并发性就越低，程序控制将越简单。
数据库锁应用
数据库管理活动，比如： imports 和 exports
- 表锁应用
当整个表或者表的大部分数据需要更新，加表级锁效率高
- 页级锁应用
当按数据物理顺序进行访问和更新时，页级锁效率高
- 行级锁应用
OLTP 事务采用行级锁，效率高
以下语句将隐式的对表加锁，直到事务结束后释放:
ALTER FRAGMENT
ALTER INDEX
ALTER TABLE
CREATE INDEX (if not using ONLINE keyword)
DROP INDEX (if not using ONLINE keyword)
RENAME COLUMN
RENAME TABLE
（3）哪种锁粒度是最优的？取决于事务本身的特点
- 当更新表中相对较小一部分数据的时候，采用行级锁将获取最好的性能
- 如果一个事务只访问表中一小部分数据，那就采用行级锁
- 如果一个事务频繁的访问整个表的数据，设置更粗的粒度，比如表级锁
- 如果更新数据库中大部分表的大部分数据的情况下, 采用数据库级别的锁
（4）关于查看锁及事务情况的指令
- 查看表锁模式:
oncheck –pt dbname:tablename
dbschema –d dbname –t tablename -ss
- 查看用户进程锁等待情况使用: onstat –u
- 查看锁使用和等待情况使用: onstat -k
- 监控数据库事务及其状态使用: onstat -x
二、隔离级别的介绍
- 多个同时运行的事物之间不会互相影响彼此的执行
- 每个用户感觉自己的事务都是排他的执行
- 同时发生的其他事务应该出现在当前事务的前或者后
- 类似一个事务中的“序列”
1.脏读
设置方式：SET ISOLATION TO DIRTY READ;
- 采用这种隔离级别，数据库服务器不会分配任何锁
- 查询过程中，可以查询到任何数据row, 甚至那些被修改但尚未提交的记录
- Dirty-read隔离级别将产生幻影读，不可重复读的问题
- 非日志数据库中只有Dirty-read一种隔离级别
- Dirty-read 隔离级别应用场景
静态表 (no updates, read-only tables)
速度比100% 准确更重要的情况
不能等待锁的释放
2.可提交读
设置方式：SET ISOLATION TO COMMITTED READ;
- 确保读取的所有记录都是提交到数据库
- 能避免读到脏数据
- 读到的所有的记录都是已提交的
- 当一个进程读完记录后，其他进程就可以修改
- 在读取数据前，数据库server尝试在记录上加共享锁
加锁前，需要先检查是否可以对对象加共享锁，但是不加
如果可以加锁，则要保证要加锁的记录没有其他进程正在更新
- 当记录正在更新时，记录上有排他锁，此时我们不能对这些记录加共享锁
3.游标读
设置方式：SET ISOLATION TO CURSOR STABILITY;
- 非更新CURSOR, 游标读的所有记录加上共享锁
- 共享锁一致将保持，直到下一行记录被读取
- 通过游标检索数据, 共享锁将一直保持到执行下一个 FETCH语句
- 不仅可以看到提交的记录，也可以保证看到的记录不会被更新
- 其他进程不能更新 (UPDATE or DELETE) 你所看到的记录
- 当移动到下一行时，锁才会释放，记录就可以进行修改
4.可重复读
设置方式：SET ISOLATION TO REPEATABLE READ;
- 数据库在读取的记录上加共享锁，验证是否可以读取数据
- 直到事务提交，锁才能释放
- 其他用户可以读取数据，但是不能修改
- 可以保证在同一事务中前后两次读取记录是一致的
- 当你必须要信任所有读取的记录，保证记录不被修改。我们可以采用repeatable read. 举例:
关键的, 统计数据 (如：银行账号的余额情况)
关联查询多个表
5.最后可提交读
设置方式：SET ISOLATION TO COMMITTED READ LAST COMMITTED
- 解决Committed Read的不足: 记录被锁，其他进程需要等待；更新的记录不能访问，直到提交，除非采用脏读；由于需要等待更新记录的提交，应用程序性能较低；如果采用脏读则会得到非预期的结果；死锁的发生，也会消耗很多时间
解决办法: Last Committed Read
- Committed Read 隔离级别, 由于其他session在记录上加了排他锁（行级锁），将导致读取数据失败SET ISOLATION COMMITTED READ 的LAST COMMITTED 可以降低读取数据的锁冲突
6.监控session的隔离级别指令
使用: onstat –g sql 或者 onstat –g ses
查看sid的详细信息: onstat –g sql sid
7.关于隔离级别的示例
（1）脏读
优点：并发性高，不需要锁资源 缺点：脏读、幻影读、不可重复读
Session1 | Session2 | Session2 output |
Begin work; --With committed read default | Begin work; Set isolation dirty read; | |
Select * from lock_tb where c1<=3; | 1 abc 2 abc | |
Update lock_tb set c2='new' where c1=1; | Select * from lock_tb where c1<=3; | 1 new 2 abc |
Insert into lock_tb values(3, 'new'); | Select * from lock_tb where c1<=3; | 1 new 2 abc 3 new |
Rollback; | ||
Select * from lock_tb where c1<=3; | 1 abc 2 abc | |
--或者 Commit work; | ||
Select * from lock_tb where c1<=3; | 1 new 2 abc 3 new |
（2）可提交读
优点：相对dirty read能避免脏读的问题
缺点：并发性较dirty read有所降低，容易出现锁等待引起的问题。不能解决幻影读、不可重复读
Session1 | Session2 | Session2output |
Begin work; --With committed read default set lock mode to wait 5; | Begin work; set isolation committed read; set lock mode to wait 5; | |
Select * from lock_tb where c1<=3; | 1 abc 2 abc | |
Update lock_tb set c2='new' where c1=1; | ||
Select * from lock_tb where c1<=3; | SQL Error -244 锁错误 |
以上现象解释如下：
- 因为 session2采用committed read对读取的记录不加S-LOCK，所以session1可以对该记录进行更新，并在该记录上加X-LOCK
- session2采用committed read对读取的记录试图加S-LOCK，但检测到c1=1的记录上有X-LOCK，出现锁等待事件，超过锁等待时长5秒后报告锁等待超时错误：Error -244
Session1 | Session2 | Session2output |
Begin work; --With committed read default Set lock mode to wait 5; | Begin work; Set isolation committed read; Set lock mode to wait 5; | |
Select * from lock_tb; | 1 abc 2 abc | |
update lock_tb set c2='new' where c1=1; insert into lock_tb values(3, 'new'); | ||
Commit work; | ||
Select * from lock_tb; 当session 1结束后执行 | 1 new 2 abc 3 new | |
--或者 Roll back; | ||
Select * from lock_tb; 当session 1结束后执行 | 1 abc 2 abc |
（3）可重复读
优点：能够解决脏读、幻影读和不可重复读的问题
缺点：只能串行执行，并发性低，需要对读取的记录加s-lock，容易引起锁相关的问题
Session1 | Session2 | Session2output |
Begin work; --With committed read default set lock mode to wait 5; | Begin work; set isolation repeatable read; Set lock mode to wait 5; | |
Update lock_tb set c2='new' where c1=1; | ||
Select * from lock_tb where c1<=3; | SQL Error -244 锁错误 | |
Insert into lock_tb values(3, 'new'); | ||
Commit work; |
以上原因如下：
- Session1对修改的的记录（c1=1）加X-LOCK
- session2采用repeatable read需要对读取的记录（c1<=3）加S-LOCK，但是记录c1=1已经被加上了x-lock，故出现锁等待事件，当锁等待超时，则报告锁错误：Error -244
Session1 | Session2 | Session2output |
Begin work; --With committed read default Set lock mode to wait 5; | Begin work; Set isolation repeatable read; Set lock mode to wait 5; | |
Update lock_tb set c2='new‘ where c1=1; | ||
Insert into lock_tb values(3, 'new'); | ||
Commit work; | ||
Select * from lock_tb ; | 1 new 2 abc 3 new | |
--或者： Rollback; | Select * from lock_tb ; | 1 abc 2 abc |
（4）最后可提交读
优点：能解决脏读问题，并发性高；不需要锁资源，能避免committed read带来的锁问题，
缺点：幻影读、不可重复读
Session1 | Session2 | Session3 |
Begin work; --With committed read default set lock mode to wait 5; | Begin work; set isolation to committed read last committed; Set lock mode to wait 5; | |
Select * from lock_tb where c1>=3; | 1 abc 2 abc | |
update lock_tb set c2='new' where c1=1; | ||
select * from lock_tb where c1>=3; | 1 abc 2 abc | |
Update lock_tb set c2='new' where c1=1; | ||
Select * from lock_tb where c1>=3; | 1 abc 2 abc | |
rollback; | ||
Select * from lock_tb where c1>=3; | 1 abc 2 abc | |
--或者 Commit work; | ||
Select * from lock_tb where c1>=3; | 1 new 2 abc 3 new |
