Oracle数据库—锁和表

锁：是用来保护正在被修改的数据，直到提交或者回滚事务之后，其他用户才可以更新数据。

锁的优点是：

一致性：一次只允许一个用户修改数据。

完整性：为所有用户提供正确的数据。也就是说，当一个用户修改并且保存所做的修改之后，将反映给所有的用户。

并发性：永续多个用户访问同一数据。

锁的有两种分类：一个是行级锁，一个是表级锁。

行级锁也是一种排他锁，对正在被修改的行进行锁定，会保证在同一时刻不允许其他用户修改同一行数据，用户只能访问除此行之外的数据。当涉及到 insert、update、delete、select…for update 时，会自动应用行级锁。下面重点讲解一下 select…for update。

语法：select…for update [of column_list] [wain n | nowait]

Of子句用于指定预先被更新的列。

Wait或nowait： wait制定等待等待他们用户释放锁的秒数。防止无限期等待。Nowait表示不等待。

通过一个例子讲解一下：

select *from  tbl_category where categoryid=2 forupdate;

用scott表示锁定了tbl_category中的categoryid为2的这一行

切换用户为abxl登录，再去访问这一行

select *from  scott.tbl_category wherecategoryid=2 for update wait 1;

则提示一下错误， wait 1 表示 只等待一秒。 nowait表示直接显示，不等待。

第 1 行出现错误:

ORA-30006: 资源已被占用; 执行操作时出现 WAIT 超时

当scott用户发出 commit或者rollback时才释放锁，其他用户才可以更新该行数据。

表级锁将锁定的是整张表，在锁定中限制其他用户对整个表的访问。语法是：

LOCK TABLEtable_name IN lock_name MODE [nowait]

Lock_name是锁定的模式。

nowait防止无限期等待其他用户释放资源。

表级锁包含：

行共享（ROW SHARE, RS）：允许其他用户访问或锁定该表，但是禁止排他锁定整个表使用select…for update语句会在表上自动应用行共享。

行排他（ROW EXCLUSIVE, RX）:和行共享相同，同时禁止其他用户在此表上使用行共享。

共享锁（SHARE, S）：仅允许其他用户查询表中的行，但是不允许插入、更新和删除。多个用户可以同时在同一张表上添加共享锁。

共享行排他锁（SHARE ROW EXCLU, SRX）:执行比共享锁更多的限制。防止在表上应用共享锁。

排他锁（EXCLUSIVE， X）：对表执行最大的限制。除了允许其他用户查询该表数据，将不允许做任何操作和添加任何锁。

死锁处理

Create table A(X number); 创建两个表，并且插入数据

Create table B(Xnumber);

Insert into Avalues(1);

Insert into Bvalues(1);

Update A setx=x+1;scott用户更新A表

Update B setx=x+; abxl用户更新B表

Update A setx=x+;abxl用户更新A表

Update B setx=x+;scott用户更新B表

就会导致死锁，两个会话中会有一个被选为“牺牲者”,它的语句将会被回滚，并返回错误信息。

表分区允许用户把一个表中的所有数据分成几个部分，并将这些部分存储在不同的位置，被分区的表成为分区表，分成的每一个部分成为一个分区。

优点是：提高数据库的可管理型，提高数据库的性能，提高可用性。

有四种分区的方法

范围分区，散列分区，列表分区，复合分区。

范围分区：

create tabletbl_tag

( tagidnumber(10) not null,

  tag varchar2(50) not null,

  count number(10) not null,

  constraint"tbl_tag_tagid_pk"primary key(tagid)

)

  partition by range(count)

  (

  partition tag_p1 values less than (1000),

  partition tag_p2 values less than (3000),

  partition tag_p3 values less than (maxvalue)

);

散列分区：

create tabletbl_tag

( tagidnumber(10) not null,

  tag varchar2(50) not null,

  count number(10) not null,

  constraint"tbl_tag_tagid_pk"primary key(tagid)

)

 Partition by hash ( tagid)

Partitions 8;   平均分成8份

 

create tabletbl_tag

( tagidnumber(10) not null,

  tag varchar2(50) not null,

  count number(10) not null,

  constraint"tbl_tag_tagid_pk"primary key(tagid)

)

 Partition by hash ( tagid)

Partition p1,

Partition p2 分成2分分区

复合分区：根据 hirdate创建范围分区，共创建三个分区，让这些分区在根据 tagid创建自分区。

create tabletbl_tag

( tagidnumber(10) not null,

  name varchar2(50) not null,

  hirdate date

)

partition byhash ( hirdate)

subpartition byhash ( tagid)

subpartition 2

(

 partition tag_p1 values less than (to_date(‘01’,’YYYYMMDD’)),

  partition tag_p2 values less than (to_date(‘02’,’YYYYMMDD’)),

 partition tag_p3 values less than (maxvalue)

);

列表分区：

 create table tbl_tag

( tagidnumber(10) not null,

  name varchar2(50) not null,

  add varchar2(50)

)

partition bylist (add)

(

partition tag_p1values (‘北京’),

partition tag_p2values (‘上海’,’天津’),

partition tag_p3values (default)

)

按地区分为 北京，  上海和天津  如果输入前面分区没有的值，则存入p3分区。

 

关于分区表的维护

添加分区：

Alert table 表 add partition 分区 values lessthan (400);

删除分区：

alert table 表 drop partition 分区;

截断分区,将删除表分区中的所有记录：

alert table 表 truncate partition 分区;

合并分区：

alert table 表  merge  partitions p1,p2 into partitionp2;

拆分分区：

 

alter table tbl_tag

 split partition tag_p2 at(2000)

 into(partition tag_p4,partition tag_p5);

 

alter table tbl_tag rename partition tag_p4to tag_p2;

alter table tbl_tag rename partition tag_p3to tag_p4;

alter table tbl_tag rename partition tag_p5to tag_p3;