Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to achieve the phenomenon of analog lock waiting in LOCK, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

1. Simulate the phenomenon of "lock waiting"

1) Open a terminal to create a test table test_lock, insert a piece of data, simulate and update the data, but do not submit

Sys@orcl > conn sec/sec

Connected.

Sec@orcl > create table test_lock (an int)

Table created.

Sec@orcl > insert into test_lock values (1)

1 row created.

Sec@orcl > commit

Commit complete.

Sec@orcl > select * from test_lock

A

-

one

Sec@orcl > update test_lock set a = 2

1 row updated.

2) Open another terminal and update the same row, and then the phenomenon of lock waiting occurs

Sys@orcl > conn sec/sec

Connected.

Sec@orcl >

Sec@orcl >

Sec@orcl > update test_lock set a = 3

Because the updates are all records of row 1 in the test_lock table, the first user submitted the change but did not submit it, and there will be a phenomenon that can not continue to be executed here. This phenomenon is "lock waiting". Never say that this phenomenon is a "deadlock". The "deadlock" Oracle will handle it on its own. Interested friends can query the four conditions generated by the deadlock. Our experiment is only to discuss the treatment of the most common "lock waiting" phenomenon.

two。 Detect the "lock wait" method

Sys@orcl > @ lock

Lock lock

Holder holder lock lock request blocked

Username session id SERIAL# type id1 id2 mode mode BLOCK session id

--

SEC 148 23007 TM 303038 030 0

SEC 153 18219 TM 303038 030 0

SEC 153 18219 TX 262159 306200 6 0 1 148

165 1 TS 3 1 3 0 0

166 1 CF 0 0 2 0 0

166 1 RS 25 1 2 0 0

166 1 XR 4 0 1 0 0

167 1 RT 1 0 6 0 0

8 rows selected.

From the execution of the above script, it is clear that, first, there is a lock wait phenomenon, because there is session id information that is not empty in the last column, and secondly, it can be judged from the lock information listed above that the SQL statement of the 148session cannot be executed because of the SQL statement executed in the 153session.

3. "Lock waiting" processing method

1) gentle method

Find the brother who operates the conversation through charisma and gently remind him to submit or roll back the uncommitted SQL statement in order to release the corresponding row-level lock.

2) methods of violence

Kill 153 sessions directly as follows:

Sys@orcl > alter system kill session '153J 18219'

System altered.

At this point, complain to you that the long-running SQL statement miraculously recovers the speed it should have.

4.OK, it's time to solemnly launch the script I used to detect "lock waiting". Please take a look.

-- Script. Function: Query the lock info--

-- Script. Name: lock.sql--

Author: secooler--

-Date: 2008.3.6--

Set pages 1000 lin 126

Col kaddr heading 'lock | address'

Col username heading 'lock | holder | username' for A18

Col sid heading 'lock | holder | session id' format 9999999999

Col type heading 'lock | type' format A6

Col id1 heading 'id1' format 9999999999

Col id2 heading 'id2' format 9999999999

Col lmode heading 'lock | mode' format 99999999

Col request heading 'request | mode' format 99999999

Col blocking_sid format 999999 heading 'blocked | session id'

Select / * + rule*/

-- a.kaddr

(select username from v$session where sid = a.sid) username

A.sid

(select serial# from v$session where sid = a.sid) serial#

(select ctime from v$lock where KADDR = a.kaddr) ctime,--

A.type

A.id1

A.id2

A.lmode

A.request

A.block

B.sid blocking_sid

From v$lock a

(select * from v$lock

Where request > 0

And type 'MR'

) b

Where a.id1 = b.id1 (+)

And a.id2 = b.id2 (+)

And a.lmode > 0

And a.type 'MR'

Order by username,a.sid,serial#,a.type

/

Column sid clear

Column type clear

Column request clear

Column username clear

5. Summary

The "lock waiting" phenomenon is a common database problem, which often occurs when multiple people (often technical support personnel) operate the database at the same time. When such problems occur, it is necessary to locate the problems calmly, seriously, quickly and accurately, and troubleshoot them. Of course, if we can adopt a very strict database operation system to prevent the occurrence of such problems, that is the best, so it is said that seven-point management and three-point treatment.

Good luck!

Thank you for reading this article carefully. I hope the article "how to achieve Analog Lock waiting in LOCK" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.