数据库学习8-锁

锁和表分区 *重点：1行级锁在什么情况下触发(insert,update,delete,select...for update)；2怎么释放行级锁(commit;rollback)；


锁：自动保护数据的机制；




表级锁：
lock table 表名 in 锁定模式 mode [nowait];
行共享(row share, rs)：允许其他用户访问和锁定表，禁止排他锁定整个表 
行排他(row exclusive, rx)：在行共享模式基础上，禁止其他用户在表上使用共享锁 
共享(share, s)：共享锁将锁定表，只允许其他用户查询表中的行，不允许添加、更新或删除行，多个用户可同时在同一表中设置共享锁(允许资源共享) 例如，每天的结帐操作时需更新日销售额表，可在更新该表示设置共享锁以确保数据一致性 
共享行排他(share row exclusive, srx)：比共享锁更多的限制，防止其他事务在表上使用共享锁、共享行排他锁以及排他锁 
排他(exclusive, x)：对表执行的最大限制，其他用户只能查询该表的记录，该锁防止其他事务对表做任何更新或在表上设置任何类型的锁 




悲观锁：假设并发操作会出现歧义；
乐观锁：假设并发操作不会出现歧义；
        1.update操作时，会拿操作之前和操作之后的          数据进行对比；
        2.进行数据操作时，操作之前和操作之后会有        一个列作为版本号，并对版本号进行比对；
        3.进行数据操作时，操作之前和操作之后会有           一个列作为时间戳，并对时间戳进行比对；

表分区

--创建表空间
CREATE TABLESPACE zj_space
     DATAFILE 'D:\zj_space.dbf'
     SIZE 40m;

--创建用户
CREATE USER zj
    IDENTIFIED BY zj
    DEFAULT TABLESPACE zj_space  
    TEMPORARY TABLESPACE temp;   

--赋权限给用户
GRANT SELECT ON emp TO zj;
GRANT CREATE SESSION TO zj;
GRANT CREATE TABLE TO zj;

--赋角色给用户
--通常的操作赋以下两个角色
GRANT CONNECT TO zj;
GRANT RESOURCE TO zj;

REVOKE DBA FROM scott;

GRANT INSERT ON emp TO zj;

SELECT * FROM all_users;

--查看 dba 的所有权限视图
SELECT * FROM dba_sys_privs;
--查看当前所有的权限视图
SELECT * FROM session_privs;
--查看当前用户所有权限视图
SELECT * FROM user_sys_privs;
--查看当前用户所有的角色
SELECT * FROM user_role_privs;
--修改用户密码(必须是管理员)
ALTER USER SYSTEM IDENTIFIED BY SYSTEM;
--解锁用户
ALTER USER scott ACCOUNT UNLOCK;

-- 锁
--行级锁 排它锁
--进行数据操作 insert,update,delete
UPDATE emp SET ename='WEI01' WHERE empno=8001;

SELECT * FROM emp FOR UPDATE;

SELECT * FROM emp FOR UPDATE WAIT 3;
SELECT * FROM emp FOR UPDATE NOWAIT;

SELECT * FROM emp FOR UPDATE OF ename,sal;

COMMIT;
ROLLBACK;

SELECT * FROM emp

--表级锁 
LOCK TABLE emp exclusive MODE;

--表分区
--范围分区
CREATE TABLE bfq
(
  b_id NUMBER,
  b_sal NUMBER
)
PARTITION BY RANGE(b_sal)
(
   PARTITION p1 VALUES LESS THAN(1000),
   PARTITION p2 VALUES LESS THAN(2000),
   PARTITION p3 VALUES LESS THAN(MAXVALUE)
)
ALTER TABLE bfq SPLIT PARTITION p2 AT(1500) INTO(PARTITION p21,PARTITION p22);
ALTER TABLE bfq MERGE PARTITIONS p21,p22 INTO PARTITION p2;
SELECT * FROM bfq PARTITION(p2);

INSERT INTO bfq VALUES(1,900);
INSERT INTO bfq VALUES(2,1900);
INSERT INTO bfq VALUES(3,3900);
INSERT INTO bfq VALUES(4,4900);
INSERT INTO bfq VALUES(5,999);
INSERT INTO bfq VALUES(6,2000);
INSERT INTO bfq VALUES(7,1000);
COMMIT;
--散列分区
CREATE TABLE bfq1
(
  b_id NUMBER,
  b_name NUMBER
)
PARTITION BY HASH(b_id)
(
  PARTITION d1,
  PARTITION d2,
  PARTITION d3
)
--列表分区
CREATE TABLE bfq2
(
  b_id NUMBER,
  b_address VARCHAR2(20)
)
PARTITION BY LIST(b_address)
(
  PARTITION d1 VALUES('长沙','株洲','湘潭'),
  PARTITION d2 VALUES('岳阳','益阳','郴州'),
  PARTITION d3 VALUES('常德','衡阳','邵阳'),
  PARTITION d4 VALUES('张家界','娄底','吉首')
)

INSERT INTO bfq2 VALUES(1,'长沙');
INSERT INTO bfq2 VALUES(1,'株洲');
INSERT INTO bfq2 VALUES(1,'岳阳');
INSERT INTO bfq2 VALUES(1,'衡阳');
INSERT INTO bfq2 VALUES(1,'邵阳');
INSERT INTO bfq2 VALUES(1,'常德');
INSERT INTO bfq2 VALUES(1,'娄底');
INSERT INTO bfq2 VALUES(1,'常德');
INSERT INTO bfq2 VALUES(1,'吉首');
INSERT INTO bfq2 VALUES(1,'张家界');
INSERT INTO bfq2 VALUES(1,'益阳');
INSERT INTO bfq2 VALUES(1,'郴州');COMMIT;

SELECT * FROM bfq2 PARTITION(d3);

--复合分区

CREATE TABLE bfq3
(
  b_id NUMBER,
  b_sal NUMBER
)
PARTITION BY RANGE(b_sal)
SUBPARTITION BY HASH(b_id)
SUBPARTITIONS 2
(
   PARTITION p1 VALUES LESS THAN(1000),
   PARTITION p2 VALUES LESS THAN(2000),
   PARTITION p3 VALUES LESS THAN(MAXVALUE)
)

DELETE FROM sales PARTITION(d1)

--添加分区
ALTER TABLE sales ADD PARTITION d4 VALUES LESS THAN(4000);
--删掉分区
ALTER TABLE sales DROP PARTITION d4;
--清除或截断分区数据
ALTER TABLE sales TRUNCATE PARTITION d3;

--合并表分区
ALTER TABLE sales MERGE PARTITIONS d1,d2 INTO PARTITION d2;
--拆分表分区 d2的范围从1500的位置拆分
ALTER TABLE sales SPLIT PARTITION d2 AT(1500) INTO(PARTITION d21,PARTITION d22);