数据库并发操作

在项目中，有一竞价模块，多个用户在竞价结束时，系统自动录入结果，系统操作为，先查询对应的结果，如果没有就向数据库插入数据。

出现的问题，第一次请求到时，无结果，插入；第二次请求到时，查询无结果（未查到插入的结果），再次插入了结果；第三次请求查到了结果。

解决方案：用行级排他锁。

具体实现：

//给数据加锁，防止数据重复插入
String sql = "select 1 from a s where s.b = ? for update ";
Db.query(sql, new Object[]{id});

数据库锁机制：

两种锁机制

共享锁（Share Lock）：即S锁，是通过对数据存取的高并行性来实现的。加了共享锁的数据库对象可以被其它事务读取，但是不能被其它事务修改。

独占锁（Exclusive Lock）：即X锁，又称排它锁，是用来防止同时共享相同资源的锁。加了独占锁的数据库对象不能被其它事务读取和修改。

默认情况下的select ...  for update 语句与DML语句相似，效果相当于启动了一个会话级别的事务，在对应的数据表（ select 所涉及的所有数据表）上加入一个数据表级共享锁（ TM ， lmode=3 ）。同时，在对应的数据行中加入独占锁（ TX ， lmode=6 ）。

以下来自也网络

从浅到深掌握Oracle的锁

 

1.分别模拟insert，update和delete造成阻塞的示例，并对v$lock中的相应的信息进行说明，给 出SQL演示。

Insert示例

会话 1：
SQL >  select  *  from t1;
        ID
          1
          2
          3
          4

SQL >  alter  table t1  add  primary  key(id);
表已更改。

SQL >  select  *  from v$ lock  where  type  in ( 'TX', 'TM');
未选定行

SQL >  insert  into t1  values( 5);
已创建  1 行。
SQL >  select sid, type,id1,id2,lmode,request,block  from v$ lock  where  type  in ( 'TX', 'TM');
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
- - - - - - - - - -  - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -
        141 TM       82975           0           3           0           0
        141 TX      131084        1787           6           0           0

会话二：
SQL >  insert  into t1  values( 5);
__
（阻塞）

会话一：
SQL >  select sid, type,id1,id2,lmode,request,block  from v$ lock  where  type  in ( 'TX', 'TM');
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
- - - - - - - - - -  - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -
         19 TX      131084        1787           0           4           0
（此处出现申请 4号锁， insert本身没有阻塞，而是在等待判断主键冲突否）
         19 TM       82975           0           3           0           0
        141 TM       82975           0           3           0           0
         19 TX      327697        1662           6           0           0
        141 TX      131084        1787           6           0           1

 

Update示例

会话一：
SQL >  update t1  set id = 5  where id = 4;
已更新  1 行。

SQL >  select sid, type,id1,id2,lmode,request,block  from v$ lock  where  type  in ( 'TX', 'TM');
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
- - - - - - - - - -  - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -
        141 TM       82975           0           3           0           0
        141 TX      327706        1663           6           0           0

SQL >  select sid  from v$mystat  where rownum = 1;
       SID
        141
SQL >  select sid,event  from v$session_wait   where sid  in( 141, 19);
       SID EVENT
- - - - - - - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
         19  SQL *Net message  from client
        141  SQL *Net message  from client

会话二：
SQL >   select sid  from v$mystat  where rownum = 1;
       SID
         19
SQL >  update t1  set id = 5  where id = 4;
__（阻塞）

会话一：
SQL >  select sid,event  from v$session_wait   where sid  in( 141, 19);
       SID EVENT
- - - - - - - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
         19 enq: TX  -  row  lock contention（等待事件出现tx锁，原因是行锁争用）
        141  SQL *Net message  from client

SQL >  select sid, type,id1,id2,lmode,request,block  from v$ lock  where  type  in ( 'TX', 'TM');
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
- - - - - - - - - -  - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -
         19 TX      327706        1663           0           6           0
（可以看到会话二在申请 6号锁，而 141的TX锁block为 1，表示阻塞了别的会话）
         19 TM       82975           0           3           0           0
        141 TM       82975           0           3           0           0
        141 TX      327706        1663           6           0           1

 

Delete示例

会话一：
SQL >  delete t1  where id = 4;
已删除  1 行。

SQL >  select sid, type,id1,id2,lmode,request,block  from v$ lock  where  type  in ( 'TX', 'TM');
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
- - - - - - - - - -  - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -
        141 TM       82975           0           3           0           0
        141 TX      327706        1663           6           0           0

SQL >  select sid  from v$mystat  where rownum = 1;
       SID
        141
SQL >  select sid,event  from v$session_wait   where sid  in( 141, 19);
       SID EVENT
- - - - - - - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
         19  SQL *Net message  from client
        141  SQL *Net message  from client

会话二：
SQL >   select sid  from v$mystat  where rownum = 1;
       SID
         19
SQL >  delete t1  where id = 4;
__（阻塞）

会话一：
SQL >  select sid,event  from v$session_wait   where sid  in( 141, 19);
       SID EVENT
- - - - - - - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
         19 enq: TX  -  row  lock contention（等待事件出现tx锁，原因是行锁争用）
        141  SQL *Net message  from client

SQL >  select sid, type,id1,id2,lmode,request,block  from v$ lock  where  type  in ( 'TX', 'TM');
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
- - - - - - - - - -  - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -
         19 TX      327706        1663           0           6           0
（可以看到会话二在申请 6号锁，而 141的TX锁block为 1，表示阻塞了别的会话）
         19 TM       82975           0           3           0           0
        141 TM       82975           0           3           0           0
        141 TX      327706        1663           6           0           1

 

v$lock中的相应的信息

ADDR
KADDR
SID：锁所在的会话id
TYPE：锁的类型，类型有很多种，常用的有TX（事务锁）、TM锁（表级锁）
ID1：TM锁为对象的object_id，TX锁为v$ transaction 中 XIDUSN  *  2^ 16  +  XIDSLOT
ID2：TM锁为 0，TX锁为v$ transaction 中  XIDSQN
LMODE：锁的模式，有 2~ 6，越大锁的限制越高，
REQUEST：为 0表示没有申请锁，其他 2~ 6表示在申请该模式的锁
CTIME：表示锁从出现到现在的时间
BLOCK：为 1表示阻塞了其他会话申请锁，为 0表示没有阻塞别人。

 

2.模拟RI锁定导致阻塞的场景，并分析v$lock相应的锁定信息，给出SQL演示。

 

 

3.自己构想一个使用手工锁定解决一种业务需求的场景，并给出SQL演示。

例如业务中只允许同一时间只有一个用户可以更改表，就需要手动锁表，来组织其他用户dml操作。
会话一：
SQL >  lock  table t1  in  share  mode;
表已锁定。

会话二：
SQL >   update t1  set id = 11  where id = 1;
__(等待)

直到会话一 commit或roll，会话二执行。

 

4.给出从mode 2-6 的TM锁相互间的互斥示例。

TM分几种情况，TX目前见过4和6（insert有主键会申请4）

Oracle TM锁的类型：

锁模式	锁描述	含义	锁定表的SQL
0	None
1	Null	空，本模式是oracle预留模式
2	Row Share(RS) 又叫(SS）	行级共享锁，是限制最少的TM锁，可以提供最高程度的并发性。其他会话可以对锁定的表进行任何类型的DML操作，还可以与其他会话锁并存	Lock table t1 in row share mode;
3	Row Exclusive Table Lock(RX) 又叫(SX)	行级排他锁，通常已经有事务在修改行或者select…for update 修改结果集。允许其他事务对锁定的表进行select  insert  update  delete 或 lock table 同时锁定一张表	Lock table t1 in row exclusive mode;
4	Share Table Lock(S)	共享锁，其他事务可以查询锁定的表但不能修改，只允许当前事务修改，但可以多个事务持有它	Lock table t1 in share mode;
5	Share Row Exclusive Table Lock(SRX) 又叫SSX	共享行级排他锁，同一时间只允许一个事务持有和修改锁定的表，其他事务可以查询但不能修改	Lock table t in share row exclusive mode;
6	Exclusive Table Lock (X)	排他锁，是限制最高的TM锁，禁止其他事务执行任何类型的DML语句或者锁表 一个表一般只能有一个6号锁	Lock table t1 in exclusive mode;

 

Oracle锁模式互斥关系图（TM与TM之间的）：

锁模式	锁名称	允许级别	互斥级别
2	行级共享锁	2 3 4 5 6	6
3	行级排他锁	2 3	4 5 6
4	共享锁	2 4	3 5 6
5	共享行级排他锁	2	3 4 5 6
6	排他锁		2 3 4 5 6

 

TM锁产生的情况：

 

坛子上关于TM精华帖截取：

0、无
1、 NULL，可以某些情况下，如分布式数据库的查询会产生此锁。
2、SS，表结构共享锁
3、SX，表结构共享锁 +被操作的记录的排它锁
4、S， 表结构共享锁 +所有记录共享锁
5、SRX 表结构共享锁 +所有记录排它锁
6、X   表结构排它锁 +所有记录排它锁

2 　SS,　表结构共享 +加上部分数据共享．虽然有人说，ORACLE里,数据没有S状态，但我还是愿意这    样理解：第 1个S代表表结构共享，第 2个代表表里的数据共享． 你可以想象一下，当往子表里增    加纪录时，主表的相关主键是不是得处于共享模式．
3    SX,   用于DML操作，第 1个S代表表结构共享，第 2个代表表里被操作的数据独占.
4    S, 代表表结构＋表里的数据都是处于共享模式．当对表创建索引时，在创建期间，表处于这种模式．
5    SRX =S( 4) +SX( 3), 
6    X, 删除表是会用上．

 

LMODE=2开始的情况

141 >  lock  table t1  in  row  share  mode;         / /给t1上 2号锁
表已锁定。

141 >  select sid, type,id1,id2,lmode,request,block  from v$ lock  where  type  in ( 'TX', 'TM');
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
- - - - - - - - - -  - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -
        141 TM       82975           0           2           0           0

19 >  select  *  from t1  for  update;         / /另开会话给t1上 3和 6号锁
        ID
- - - - - - - - - -
          1
          2
          3
          4

141 >  select sid, type,id1,id2,lmode,request,block  from v$ lock  where  type  in ( 'TX', 'TM');
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
- - - - - - - - - -  - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -
         19 TM       82975           0           3           0           0
        141 TM       82975           0           2           0           0
         19 TX      393221        1754           6           0           0
可以看到 236可以共存，没有互斥

19 >  lock  table t1  in  share  mode;         / /给表再上 4号锁
表已锁定。

141 >  select sid, type,id1,id2,lmode,request,block  from v$ lock  where  type  in ( 'TX', 'TM');
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
- - - - - - - - - -  - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -
         19 TM       82975           0           5           0           0
        141 TM       82975           0           2           0           0
         19 TX      393221        1754           6           0           0
因为 3和 4不能共存，因此 3 + 4升级为 5号锁

19 >  lock  table t1  in  exclusive  mode;         / /给表上 6号锁，发生等待
__

141 >  select sid, type,id1,id2,lmode,request,block  from v$ lock  where  type  in ( 'TX', 'TM');
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
- - - - - - - - - -  - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -
         19 TM       82975           0           5           6           1
        141 TM       82975           0           2           0           1
         19 TX      393221        1754           6           0           0
因为同是 19号会话，上 6号TM只需将原有 5升级，但是已经存在 2号TM，产生互斥，所以等待

 

证明6号TX和6号TM可以共存：

SQL >  Lock  table t1  in  exclusive  mode;
表已锁定。

SQL >  select sid, type,id1,id2,lmode,request,block  from v$ lock  where  type  in ( 'TX', 'TM');
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
- - - - - - - - - -  - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -
        141 TM       82975           0           6           0           0

SQL >  update t1  set id = 5  where id = 4;
已更新  1 行。

SQL >  select sid, type,id1,id2,lmode,request,block  from v$ lock  where  type  in ( 'TX', 'TM');
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
- - - - - - - - - -  - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -
        141 TM       82975           0           6           0           0
        141 TX      262155        1324           6           0           0

 

证明2号TM和6号TX可以共存：

141 >  Lock  table t1  in  row  share  mode;
表已锁定。

141 >  select sid, type,id1,id2,lmode,request,block  from v$ lock  where  type  in ( 'TX', 'TM')  order  by  1;
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
- - - - - - - - - -  - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -
        141 TM       82975           0           2           0           0

19 >  update t1  set id = 6  where id = 4;
已更新  1 行。

141 >  select sid, type,id1,id2,lmode,request,block  from v$ lock  where  type  in ( 'TX', 'TM');
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
- - - - - - - - - -  - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -  - - - - - - - - - -
         19 TM       82975           0           3           0           0
        141 TM       82975           0           2           0           0
         19 TX      458778        1387           6           0           0

 

5.给出一个导致死锁的SQL示例。

死锁就是a在等待b释放锁，而b又在等待a释放，形成交叉。
会话一：
SQL >  update t1  set id = 11  where id = 1;
已更新  1 行。

会话二：
SQL >  update t2  set id = 11  where id = 1;
已更新  1 行。

会话一：
SQL >  update t2  set id = 11  where id = 1;
__(等待)
会话二：
SQL >   update t1  set id = 11  where id = 1;
__(等待)

会话一：
SQL >  update t2  set id = 11  where id = 1;
update t2  set id = 11  where id = 1
        *
第  1 行出现错误:
ORA - 00060: 等待资源时检测到死锁

 

（以下部分引用自http://blog.youkuaiyun.com/changyanmanman/article/details/7451043）

    在讲到lock的上文中已经提到，这种控制机制需要resource，lock联众数据结构，但是需要内存分配lock数据结构，对于粗粒度（可以理解为文件很大）或者数量有限的资源，使用这种机制还可以接受，因为分配的内存并不多。但是对于表的数据记录，动辄几百G 的表，每个记录如果都分配一个resource 和lock 数据结构对，无论从内存需求还是维护开销上都是一个噩梦。所以，对于数据记录这种细粒度的资源，oracle使用的是行级锁（row level  lock）。记数据块内存储的是一条条的用户记录，用户记录也是按照一定的格式保存的，每条记录可以分成 记录头 和 记录体 两部分。记录头中是描述信息，比如列宽度，和事务有关的是ITL Entry pointer字段。

1）ITL：这个比较熟悉，用于记录哪些事务修改了这个数据块的内容，可以把他想象成一个表格，每个表格对应一个事务，包括事务号，事务是否提交等重要信息。

2）记录头ITL索引：每条记录的记录头部有一个字段，用于记录ITL表项号，可以看做是指向ITL表的指针，如果一个进程来访问记录，那就先得根据这个指针去ITL中看看事务已经提交了没啊。。前一个事务是用的什么锁啊。。等待

下面我们详细说一下运行机制：

    当一个事务开始时，必须先申请一个TX锁，注意 这种锁保护的资源是回滚段，回滚数据块。因此这个申请也就意味着：用户必须先申请到一个回滚段资源后才能开始一个事务，才能执行语句修改数据。申请到回滚段资源后，用户事务就可以修改数据了。在修改数据表的记录时，需要遵循下面的操作顺序：

1、首先获得这个表的TM锁，这个锁用于保护事务执行过程中其他用户不能修改表结构（但是可以修改表内的数据）。

2、事务修改某个数据块中的记录时，首先要在数据块块头的ITL表中申请一个空闲表项，并且在其中记录事务号，实际就是记录这个事务要使用的回滚段地址。

3、事务修改该数块中的某条记录时，会设置该记录头部的ITL索引指向上一步申请到的表项。然后再修改记录的内容，修改前现在回滚段对记录修改该钱的状态做一个拷贝，然后才能修改该数据记录，这个拷贝用于以后的回滚，恢复，或者一致性读。

4、当其他用户并发修改该这条记录时，会根据记录头的ITL索引读取ITL表项内容。查看这个事务石头已经提交。

5、如果没有提交，则这个用户的TX锁会等待前一个用户的TX锁的释放。仅仅释放前一个用户的行锁是不行的，Oracle只检测TX，因为定期检测行锁资源消耗太大。

从上面的工作机制可以看出，无论一个事务修改多少个表的多少条记录，盖世五真正需要的只是一个TX锁，每个表一个的TM锁，内存开销非常小。而所谓的行级锁，其实只是数据块头，数据记录的一些字段，不会消耗额外的内存资源。

 

6.查找锁的SID和被锁的SQL

如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待

以下的语句可以查询到谁锁了表，而谁在等待。

结果第一行表示产生锁的，子节点表示被锁的

 

SQL> SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username

  2  User_name,s.sid,s.sql_id,

  3  o.owner,o.object_name,o.object_type,s.sid,s.serial#

  4  FROM v$locked_object l,dba_objects o,v$session s

  5  WHERE l.object_id=o.object_id

  6  AND l.session_id=s.sid

  7  ORDER BY o.object_id,xidusn DESC;

 

USER_NAME   SID SQL_ID        OWNER      OBJECT_NAM OBJECT_TYP  SID    SERIAL#

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

SYS          16 a18zt89tq6c0s SCOTT      EMP        TABLE        16        751

   BIDPRO   202 3f06gzyfyg1xj SCOTT      EMP        TABLE       202        793

 

网络资源来自于：http://www.cnblogs.com/kissdb/p/4009898.html