在mysql怎么写not deferrable

在mysql怎么写not deferrable

Oracle约束constraint是我们经常使用的一种数据库规则对象。constraint在数据库中的作用就是从静态角度对数据完整性进行维护。我们经常使用的主键primary key和外键foreign key，本质上就是约束的一种形式。

对Oracle的约束，我们有三个属性可以进行设置，分别为deferrable、deferred和validated。针对不同的需求设计场景，采用不同类型的属性，可以帮助我们实现不同的约束效果。下面我们分别来进行实验。

1、 环境准备

首先我们还是准备数据实验环境。

SQL>create table t (id number)

Table created

SQL>alter table T

2add constraint c_t_id1

3check (id>5)

Table altered

我们创建了数据表T，在列id上添加了约束c_t_id1。约束内容很简单，就是要求id值保证是大于5。约束c_t_id1使用的是默认选项，数据字典中对该约束的表示如下：

SQL>select constraint_name, constraint_type ctype, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from dba_constraints where table_name='T' and wner='SYS'

CONSTRAINT_NAME CTYPE COND STATUS DEFERRABLE DEFERRED VALIDATED

-------------------- ----- ---------- -------- -------------- --------- -------------

C_T_ID1 C id>5 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

注意，此时约束的三个属性取值分别为：deferrable: not deferrable；deferred：immediate；validated：validated；

我们观察一下此时数据表的插入现象：

SQL>insert into t values (1)//插入非法的数据；

insert into t values (1)

ORA-02290: 违反检查约束条件 (SYS.C_T_ID1) //立刻报错，将数据剔除！

SQL>insert into t values (6)

1 row inserted

SQL>commit

Commit complete

SQL>select * from t

ID

----------

6

结论：在默认情况下，Oracle的约束是不允许延迟（not deferrable）、立即应用和验证的（immediate、validated）。在数据变化的时候，立即进行约束验证。

2、 deferrable：约束应用可以延迟

deferrable默认值为not deferrable，字面含义是不可延迟。那么我们如果设置可以延迟，效果是什么呢？

SQL>alter table T

2drop constraint C_T_ID1

Table altered

SQL>alter table T

2add constraint C_T_ID1

3check (id>5)

4deferrable

Table altered

此时，数据字典中的情况是如下：

SQL>select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from dba_constraints where table_name='T' and wner='SYS'

CONSTRAINT_NAME CTYPE STATUS DEFERRABLE DEFERRED VALIDATED

-------------------- ----- -------- -------------- --------- -------------

C_T_ID1 C ENABLED DEFERRABLE IMMEDIATE VALIDATED

与默认情况相比，deferrable属性变化为了deferrable。我们观察一下现象：

SQL>insert into t values (3)

insert into t values (3)

ORA-02290: 违反检查约束条件 (SYS.C_T_ID1)

在插入数据的时候，立即进行约束验证。和默认情况下没有差异。那么怎么处理呢？

//手工设置deferred属性为deferred

SQL>set constraint c_t_id1 deferred

Constraints set

SQL>insert into t values (3)//此时插入数据时候，并不进行验证 *** 作了。

1 row inserted

SQL>insert into t values (7)

1 row inserted

SQL>commit

commit

ORA-02091: 事务处理已回退

ORA-02290: 违反检查约束条件 (SYS.C_T_ID1) //直到进行commit的时候，才会应用约束；

那么，如何设置回原有的属性呢？

SQL>set constraint c_t_id1 immediate

Constraints set

SQL>insert into t values (4)//又恢复插入立刻检查约束的状态了？

insert into t values (4)

ORA-02290: 违反检查约束条件 (SYS.C_T_ID1)

结论：单独deferrable的含义就是是否允许约束检查延后进行。单独设置deferrable为deferrable之后，约束检查延后是不可以直接使用的，要配合deferred参数，如果该参数是immediate，那么约束还是在DML的时候进行应用。如果deferred参数设置为deferred，约束就是在事务commit提交的时候应用，出现错误就连带回滚rollback整个事务。

3、 deferred：是否进行延迟应用

从上面的实验中，我们可以看出deferred属性是配合deferrable属性使用的。当deferrable设置为deferrable之后，可以通过set constraints进行deferred属性的设置，来确定约束应用时点。

本部分确定deferred定义的方式和与deferrable属性的关系。是可以在定义约束是使用initially关键字来确定约束的deferred属性取值。

SQL>alter table T

2add constraint C_T_ID1

3check (id>5)

4deferrable initially deferred

Table altered

运行中的mysql状态查看

(1)QPS(每秒Query量)

QPS

=

Questions(or

Queries)

/

seconds

mysql

>

show

global

status

like

'Question%'

(2)TPS(每秒事务量)

TPS

=

(Com_commit

+

Com_rollback)

/

seconds

mysql

>

show

global

status

like

'Com_commit'

mysql

>

show

global

status

like

'Com_rollback'

(3)key

Buffer

命中率

mysql>show

global

status

like

'key%'

key_buffer_read_hits

=

(1-key_reads

/

key_read_requests)

*

100%

key_buffer_write_hits

=

(1-key_writes

/

key_write_requests)

*

100%

(4)InnoDB

Buffer命中率

mysql>

show

status

like

'innodb_buffer_pool_read%'

innodb_buffer_read_hits

=

(1

-

innodb_buffer_pool_reads

/

innodb_buffer_pool_read_requests)

*

100%

(5)Query

Cache命中率

mysql>

show

status

like

'Qcache%'

Query_cache_hits

=

(Qcahce_hits

/

(Qcache_hits

+

Qcache_inserts

))

*

100%

(6)Table

Cache状态量

mysql>

show

global

status

like

'open%'

比较

open_tables

与

opend_tables

值

(7)Thread

Cache

命中率

mysql>

show

global

status

like

'Thread%'

mysql>

show

global

status

like

'Connections'

Thread_cache_hits

=

(1

-

Threads_created

/

connections

)

*

100%

(8)锁定状态

mysql>

show

global

status

like

'%lock%'

Table_locks_waited/Table_locks_immediate=0.3%

如果这个比值比较大的话，说明表锁造成的阻塞比较严重

Innodb_row_lock_waits

innodb行锁，太大可能是间隙锁造成的

(9)复制延时量

mysql

>

show

slave

status

查看延时时间

(10)

Tmp

Table

状况(临时表状况)

mysql

>

show

status

like

'Create_tmp%'

Created_tmp_disk_tables/Created_tmp_tables比值最好不要超过10%，如果Created_tmp_tables值比较大，

可能是排序句子过多或者是连接句子不够优化。

1 锁机制

当前MySQL已经支持 ISAM, MyISAM, MEMORY (HEAP) 类型表的表级锁了，BDB 表支持页级锁，InnoDB 表支持行级锁。很多时候，可以通过经验来猜测什么样的锁对应用程序更合适，不过通常很难说一个锁比别的更好，这全都要依据应用程序来决定，不同的地方可能需要不同的锁。

想要决定是否需要采用一个支持行级锁的存储引擎，就要看看应用程序都要做什么，其中的查询、更新语句是怎么用的。例如，很多的web应用程序大量的做查询，很少删除，主要是基于索引的更新，只往特定的表中插入记录。采用基本的MySQL MyISAM 表就很合适了。

MySQL中对表级锁的存储引擎来说是释放死锁的。避免死锁可以这样做到：在任何查询之前先请求锁，并且按照请求的顺序锁表。

MySQL中用于 WRITE(写) 的表锁的实现机制如下：

如果表没有加锁，那么就加一个写锁。否则的话，将请求放到写锁队列中。 MySQL中用于 READ(读) 的表锁的实现机制如下：

如果表没有加写锁，那么就加一个读锁。否则的话，将请求放到读锁队列中。 当锁释放后，写锁队列中的线程可以用这个锁资源，然后才轮到读锁队列中的线程。

这就是说，如果表里有很多更新 *** 作的话，那么 SELECT 必须等到所有的更新都完成了之后才能开始。

从 MySQL 3.23.33 开始，可以通过状态变量 Table_locks_waited 和 Table_locks_immediate 来分析系统中的锁表争夺情况：

mysql>SHOW STATUS LIKE 'Table%'

+-----------------------+---------+

| Variable_name | Value |

+-----------------------+---------+

| Table_locks_immediate | 1151552 |

| Table_locks_waited | 15324 |

+-----------------------+---------+

---