oracle 更新数据锁表,模拟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

分享：

喜欢

0

赠金笔

加载中，请稍候......

评论加载中，请稍候...

发评论

登录名： 密码： 找回密码 注册记住登录状态

昵   称：

评论并转载此博文

发评论

以上网友发言只代表其个人观点，不代表新浪网的观点或立场。