MySQL数据库排它操作-DBMNG数据库管理与应用网

目录 / 分类

数据库基础

Access

SQLServer

MySQL

SQLite

Oracle

PostgreSQL

移动应用

经验分享

服务器配置

网文摘录

下载中心

软件下载

CUBRID数据库

介绍及使用

验证/二维/条形码

当前位置：首页 > MySQL > 基础知识

MySQL数据库排它操作

在实际开发中，排他可以通过多种方式来实现。

①在数据库设计时增加数据记录的更新时间字段。这种方式是通过在进行更新操作前对更新时间进行比较，如果发现更新时间已经发生改变，则取消该操作。由于需要进行比较操作，在取得需要更新的数据时就需要同时取得该条记录的更新时间；而在更新操作前再取得一次更新时间（注意：在有些数据库中需要更新数据库连接才能取得最新数据）。由于这种方式在操作时并不对记录进行锁定，所以如果对数据处理的时间较长，就有可能产生编辑的数据已经被其他用户更新，而不能再次更新到数据库的情况发生，造成数据更新的失败次数增加。所以，这种方式的操作在实际使用中应针对于比较小型的系统。

②在数据库设计时增加排他字段。这种方式是通过在更新记录时对记录进行锁定来保证数据的正常更新。在这种方式下，可以保证数据更新在编辑时就能确定。在需要更新记录时，先根据排他字段的值来确定是否可以进行更新操作，如果记录没有被其他用户锁定，则锁定这条记录，直到用户提交或取消此条记录的更新请求。在这种情况下，如果某个用户对某（几）条数据的编辑操作持续时间太长，会影响其他用户的使用。所以，一般会限制对记录的操作时间来防止对某些记录的“死锁”。

一、ORACLE数据库

锁是数据库用来控制共享资源并发访问的机制，用于保护正在被修改的数据，且直到当前用户提交或回滚了事务之后，其他用户才可以更新数据

A.锁定的优点

①一致性- 一次只允许一个用户修改数据

②完整性- 为所有用户提供正确的数据。如果一个用户进行了修改并保存，所做的修改将反映给所有用户

B.锁的类型
①行级锁
对正在被修改的行进行锁定,其他用户可以访问除被锁定的行以外的行，行级锁是一种排他锁，防止其他事务修改此行。

在使用以下语句时，Oracle会自动应用行级锁：

[sql]view plaincopy
			
			INSERT UPDATE DELETE SELECT …FOR UPDATE SELECT …FOR UPDATE语句允许用户一次锁定多条记录进行更新

使用COMMIT或ROLLBACK语句释放锁

[sql]view plaincopy
			
			SELECT …FOR UPDATE语法:SELECT …FOR UPDATE [OF columns][WAIT n | NOWAIT];

OF : 子句用于指定即将更新的列，即锁定行上的特定列。

WAIT: 子句指定等待其他用户释放锁的秒数，防止无限期的等待。

PS： or update nowait和 for update的区别：

区别在于别的事务要对这个表进行写操作时，是等待一段时间还是马上就被数据库系统拒绝而返回。制定采用nowait方式来进行检索，所以当发现数据被别的session锁定中的时候，就会迅速返回ORA-00054错误，内容是资源正忙。
但指定以 NOWAIT 方式获取资源。所以在程序中我们可以采用nowait方式迅速判断当前数据是否被锁定中，如果锁定中的话，就要采取相应的业务措施进行处理。

使用FOR UPDATE WAIT子句的优点如下：
１防止无限期地等待被锁定的行；
２允许应用程序中对锁的等待时间进行更多的控制。
３对于交互式应用程序非常有用，因为这些用户不能等待不确定
４若使用了skip locked，则可以越过锁定的行，不会报告由wait n 引发的资源忙异常报告

问题：
①

[sql]view plaincopy
			
			select * from emp where empno = 7369 for update;

会对表中员工编号为7369的记录进行上锁。其他用户无法对该记录进行操作，只能查询。

②

[sql]view plaincopy
			
			select * from emp where empno = 7369 for update of sal;

这条语句是不是意味着只对表中的7369这一行的sal字段的数据进行了上锁，其他数据则可以被其他用户做更新操作呢。学员测试结果为二条语句的效果是一样的。其他用户对整行都无法更新。

从单独一张表的操作来看，上面二条语句的效果确实是相同的。但是如果涉及到多表操作的时候 for update of columns就起到了非常大的作用了。
现假定有二个用户，scott和mm
①scott执行语句：

[sql]view plaincopy
			
			select * from emp e,dept d where e.deptno = d.deptno for update;

对二张表都进行了整表锁定
②mm执行语句：

[sql]view plaincopy
			
			select * from dept for update wait 3;

试图锁定scott用户的dept表
结果是：
ERROR 位于第 1 行:
ORA-30006: 资源已被占用; 执行操作时出现 WAIT 超时

现在，scott用户先进行解锁rollback,再在for update语句后面加上of columns，进行测试
①scott执行语句：

[sql]view plaincopy
			
			select * from emp e,dept d where e.deptno = d.deptno for update of sal ;

②mm执行语句：

[sql]view plaincopy
			
			select * from dept for update wait 3;

结果是：

成功锁定了dept表的数据.

mm再次执行语句：

[sql]view plaincopy
			
			select * from dept for update wait 3;

结果是：
ERROR 位于第 1 行:
ORA-30006: 资源已被占用; 执行操作时出现 WAIT 超时

结论: for update of columns 用在多表连接锁定时，可以指定要锁定的是哪几张表，而如果表中的列没有在for update of 后面出现的话，就意味着这张表其实并没有被锁定，其他用户是可以对这些表的数据进行update操作的。这种情况经常会出现在用户对带有连接查询的视图进行操作场景下。用户只锁定相关表的数据，其他用户仍然可以对视图中其他原始表的数据来进行操作。

select --- for update of 这个of子句在牵连到多个表时，具有较大作用，如不使用of指定锁定的表的列，则所有表的相关行均被锁定，若在of中指定了需修改的列，则只有与这些列相关的表的行才会被锁定。

排它操作不仅仅 FOR UPDATE 就可以，它还需要一个更新标志。

举个例子：

要更新数据表TABLE表，表中字段ID为主键。然后A进行了如下操作

[sql]view plaincopy
				
				SELECT ID FROM TABLE WHERE * FOR UPDATE

[sql]view plaincopy
				
				UPDATE TABLE SET * WHERE ID=*;

A先锁了符合条件的数据，然后进行了更新操作，之后是COMMIT之类的。

问题来了：

TIME1时刻，A了解到数据库TABLE表ID=1的列不符合状况，他在TIME2时刻准备执行上面的操作进行修改；但是B也查到了这个状况，却在TIME1.5的时刻完成了TABLE的更改；这时候A修改的就是B改过后的数据。

解决：

需要一个参照标志，假设为UPD字段。

A要修改表中ID=1的数据，需要先进行查询ID=1数据对于的UPD字段的值；

[sql]view plaincopy
				
				SELECT UPD FROM TABLE WHERE ID=1

假设查找的UPD值为 vals=2014/11/14 1:04；

[sql]view plaincopy
				
				SELECT ID FROM TABLE WHERE ID=1 AND UPD=vals FOR UPDATE

之后进行更新操作就没有问题了。

上一篇：MySql索引算法原理解析（B-tree）
下一篇：Ubuntu安装MySQL