如何对“行、表、数据库”加锁？

1

如何锁一个表的某一行

SET TRANSACTION

ISOLATION LEVEL READ UNCOMMITTED

SELECT * FROM table ROWLOCK WHERE id = 1

2 锁定数据库的一个表

SELECT * FROM table WITH (HOLDLOCK)

加锁语句：

sybase:

update 表 set col1=col1 where 1=0

MSSQL:

select col1 from 表 (tablockx)

where

1=0

oracle:

LOCK TABLE 表 IN EXCLUSIVE MODE ；

加锁后其它人不可 *** 作，直到加锁用户解锁，用commit或rollback解锁

几个例子帮助大家加深印象

设table1(A,B,C)

A B C

a1 b1 c1

a2 b2 c2

a3 b3 c3

1）排它锁

新建两个连接

在第一个连接中执行以下语句

begin tran

update table1

set

A='aa'

where B='b2'

waitfor delay

'00:00:30' --等待30秒

commit tran

在第二个连接中执行以下语句

begin tran

select * from table1

where B='b2'

commit tran

若同时执行上述两个语句，则select查询必须等待update执行完毕才能执行即要等待30秒

2）共享锁

在第一个连接中执行以下语句

begin tran

select * from table1

holdlock

-holdlock人为加锁

where B='b2'

waitfor delay

'00:00:30' --等待30秒

commit tran

在第二个连接中执行以下语句

begin tran

select A,C

from

table1

where B='b2'

update table1

set

A='aa'

where B='b2'

commit tran

若同时执行上述两个语句，则第二个连接中的select查询可以执行

而update必须等待第一个事务释放共享锁转为排它锁后才能执行

即要等待30秒

3）死锁

增设table2(D,E)

D E

d1 e1

d2 e2

在第一个连接中执行以下语句

begin tran

update table1

set

A='aa'

where B='b2'

waitfor delay

'00:00:30'

update table2

set

D='d5'

where E='e1'

commit tran

在第二个连接中执行以下语句

begin tran

update table2

set

D='d5'

where E='e1'

waitfor delay

'00:00:10'

update table1

set

A='aa'

where B='b2'

commit tran

同时执行，系统会检测出死锁，并中止进程

补充一点:

Sql Server2000支持的表级锁定提示

HOLDLOCK 持有共享锁，直到整个事务完成，应该在被锁对象不需要时立即释放，等于SERIALIZABLE事务隔离级别

NOLOCK 语句执行时不发出共享锁，允许脏读 ，等于 READ

UNCOMMITTED事务隔离级别

PAGLOCK 在使用一个表锁的地方用多个页锁

READPAST 让sql

server跳过任何锁定行，执行事务，适用于READ UNCOMMITTED事务隔离级别只跳过RID锁，不跳过页，区域和表锁

ROWLOCK

强制使用行锁

TABLOCKX 强制使用独占表级锁，这个锁在事务期间阻止任何其他事务使用这个表

UPLOCK

强制在读表时使用更新而不用共享锁

应用程序锁:

应用程序锁就是客户端代码生成的锁，而不是sql server本身生成的锁

处理应用程序锁的两个过程

sp_getapplock 锁定应用程序资源

sp_releaseapplock

为应用程序资源解锁

注意: 锁定数据库的一个表的区别

SELECT * FROM table WITH (HOLDLOCK)

其他事务可以读取表，但不能更新删除

SELECT * FROM table WITH (TABLOCKX)

其他事务不能读取表,更新和删除

1

如何锁一个表的某一行

/*

测试环境：windows 2K server + Mssql 2000

所有功能都进行测试过，并有相应的结果集，如果有什么疑义在论坛跟帖

关于版权的说明：部分资料来自互联网，如有不当请联系版主，版主会在第一时间处理。

功能：sql遍历文件夹下的文本文件名,当然你修改部分代码后可以完成各种文件的列表。

*/

A

连接中执行

SET TRANSACTION

ISOLATION LEVEL REPEATABLE

READ

begin tran

select * from tablename

with

(rowlock) where id=3

waitfor delay '00:00:05'

commit tran

B连接中如果执行

update tablename set

colname='10' where id=3

--则要等待5秒

update tablename

set

colname='10' where id <>3

--可立即执行

2

锁定数据库的一个表

SELECT * FROM table WITH (HOLDLOCK)

注意: 锁定数据库的一个表的区别

SELECT * FROM table WITH (HOLDLOCK)

其他事务可以读取表，但不能更新删除

SELECT * FROM table WITH (TABLOCKX)

其他事务不能读取表,更新和删除

锁是计算机协调多个进程或线程并发访问某一资源的机制，在数据库中，除传统的计算资源（CPU、RAM、I/O）争用外，数据也是一种供许多用户共享的资源，如何保证数据并发访问的一致性，有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素，从这个角度来说，锁对数据库而言是尤其重要，也更加复杂。MySQL中的锁，按照锁的粒度分为:1、全局锁，就锁定数据库中的所有表。2、表级锁，每次 *** 作锁住整张表。3、行级锁，每次 *** 作锁住对应的行数据。

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新 *** 作的事务提交语句都将阻塞。其典型的使用场景就是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。但是对数据库加全局锁是有弊端的，如在主库上备份，那么在备份期间都不能执行更新，业务会受影响，第二如果是在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志，会导致主从延迟。

解决办法是在innodb引擎中，备份时加上--single-transaction参数来完成不加锁的一致性数据备份。

添加全局锁: flush tables with read lock解锁 unlock tables。

表级锁,每次 *** 作会锁住整张表.锁定粒度大,发送锁冲突的概率最高,并发读最低,应用在myisam、innodb、BOB等存储引擎中。表级锁分为: 表锁、元数据锁(meta data lock, MDL)和意向锁。

表锁又分为: 表共享读锁 read lock、表独占写锁write lock

语法: 1、加锁 lock tables 表名 ... read/write

2、释放锁 unlock tables 或者关闭客户端连接

注意: 读锁不会阻塞其它客户端的读，但是会阻塞其它客户端的写，写锁既会阻塞其它客户端的读，又会阻塞其它客户端的写。大家可以拿一张表来测试看看。

元数据锁,在加锁过程中是系统自动控制的，无需显示使用，在访问一张表的时候会自动加上，MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入 *** 作。为了避免DML和DDL冲突，保证读写的正确性。

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变更 *** 作时，加MDL写锁(排他).

查看元数据锁:

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema_metadata_locks

意向锁，为了避免DML在执行时，加的行锁与表锁的冲突，在innodb中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。意向锁分为,意向共享锁is由语句select ... lock in share mode添加。意向排他锁ix,由insert，update，delete，select。。。for update 添加。

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_lock

行级锁，每次 *** 作锁住对应的行数据，锁定粒度最小，发生锁冲突的概率最高，并发读最高，应用在innodb存储引擎中。

innodb的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁，对于行级锁，主要分为以下三类:

1、行锁或者叫record lock记录锁，锁定单个行记录的锁，防止其他事物对次行进行update和delete *** 作，在RC,RR隔离级别下都支持。

2、间隙锁Gap lock,锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其他事物在这个间隙进行insert *** 作，产生幻读，在RR隔离级别下都支持。

3、临键锁Next-key-lock,行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap,在RR隔离级别下支持。

innodb实现了以下两种类型的行锁

1、共享锁 S: 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

2、排他锁 X: 允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

insert 语句 排他锁 自动添加的

update语句 排他锁 自动添加

delete 语句 排他锁 自动添加

select 正常查询语句 不加锁 。。。

select 。。。lock in share mode 共享锁 需要手动在select 之后加lock in share mode

select 。。。for update 排他锁 需要手动在select之后添加for update

默认情况下，innodb在repeatable read事务隔离级别运行，innodb使用next-key锁进行搜索和索引扫描，以防止幻读。

间隙锁唯一目的是防止其它事务插入间隙，间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用的间隙锁。

并发控制

封锁机制是并发控制的主要手段。封锁是使事务对它要 *** 作的数据有一定的控制能力。封锁具有3个环节：第一个环节是申请加锁，即事务在 *** 作前要对它欲使用的数据提出加锁请求；第二个环节是获得锁，即当条件成熟时，系统允许事务对数据加锁，从而事务获得数据的控制权；第三个环节是释放锁，即完成 *** 作后事务放弃数据的控制权。为了达到封锁的目的，在使用时事务应选择合适的锁，并要遵从一定的封锁协议。

基本的封锁类型有两种：排它锁(Exclusive Locks，简称X锁)和共享锁(Share Locks，简称S锁)。

(1)排它锁

排它锁也称为独占锁或写锁。一旦事务T对数据对象A加上排它锁(X锁)，则只允许T读取和修改A，其他任何事务既不能读取和修改A，也不能再对A加任何类型的锁，直到T释放A上的锁为止。

(2)共享锁

共享锁又称读锁。如果事务T对数据对象A加上共享锁(S锁)，其他事务对A只能再加S锁，不能加X锁，直到事务T释放A上的S锁为止。

简单地对数据加X锁和S锁并不能保证数据库的一致性。在对数据对象加锁时，还需要约定一些规则。例如，何时申请X锁或S锁、持锁时间、何时释放等。这些规则称为封锁协议(Locking Protocol)。对封锁方式规定不同的规则，就形成了各种不同的封锁协议。封锁协议分三级，各级封锁协议对并发 *** 作带来的丢失修改、不可重复读取和读“脏”数据等不一致问题，可以在不同程度上予以解决。

---