模拟oracle数据库update锁表现象及处理办法

Session1创建测试表：

SQL>create table test (id number (10) not null , name varchar(20), primary key(id))

Table created.

SQL>desc test

Name Null? Type

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

ID NOT NULL NUMBER(10)

NAME VARCHAR2(20)

SQL>insert into test values(001,'tom')

1 row created.

SQL>insert into test values(002,'lisa')

1 row created.

SQL>insert into test values(003,'joy')

1 row created.

SQL>insert into test values(004,'jia')

1 row created.

查看test表信息

SQL>update test set name='xue' where name='joy'

1 row updated.

SQL>commit

Commit complete.

SQL>select * from test updata

ID NAME

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

1 tom

2 lisa

3 xue

4 jia

重新打开session 2：

SQL>select * from test；

ID NAME

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

1 tom

2 lisa

3 xue

4 jia

update模拟锁表

SQL>update test set name='da' where name='tom'

1 row updated.

注：不提交

Session2查询：

SQL>select * from test；

ID NAME

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

1 tom

2 lisa

3 xue

4 jia

查看哪个表被锁

SQL>select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id

OWNER

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

OBJECT_NAME

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

SESSION_ID LOCKED_MODE

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

SYS

TEST

23 3

查看是哪个session引起的

SQL>select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time

USERNAME SID SERIAL# LOGON_TIM

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

SYS 23 23 02-JAN-20

杀掉对应进程

SQL>alter system kill session'23,23'

System altered.

其中23为sid,23为serial#.

SQL>select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id

no rows selected

SQL>select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time

no rows selected

session 1查询：

SQL>select * from test

select * from test

*

ERROR at line 1:

ORA-00028: your session has been killed

SQL>select * from test

select * from test

*

ERROR at line 1:

ORA-01012: not logged on

Process ID: 5366

Session ID: 23 Serial number: 23

重新连接SQL

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 2 11:39:53 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>select * from test updata

ID NAME

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

1 tom

2 lisa

3 xue

4 jia

会死锁，当资源被别的几个进程互相占用的时候，就会死锁，举个简单的列子

出现循环等待资源。

当sql发出一个update请求之后，数据库会对表中的每条记录加上U锁。然后数据库会根据where条件，将符合条件的记录转换为X锁。对不满足条件的记录释放U锁。

上面死锁的原因就是更新的时候先要查询相关的记录才能更新，这个时候就有可能会产生死锁。当然还有很多其它的原因也有可能产生死锁。

---