oracle的pin锁,oracle 锁机制原理

oracle的行锁的存储

Storage of Row Locks

Unlike some databases, which use a lock manager to maintain a list of

locks in memory, Oracle Database stores lock information in the data block that contains the locked row.

The database uses a queuing mechanism for acquisition of row locks.

If a transaction requires a lock for an unlocked row, then the

transaction places a lock in the data block. Each row modified by this

transaction points to a copy of the transaction ID stored in the block header (see "Overview of Data Blocks").

When a transaction ends, the transaction ID remains in the block

header. If a different transaction wants to modify a row, then it uses

the transaction ID to determine whether the lock is active. If the lock

is active, then the session asks to be notified when the lock is

released. Otherwise, the transaction acquires the lock.

经过测试行锁 是逐步锁定的，而不是符合条件的一起锁定，要想一个数据集合中所有的行更新之前都被锁定，需要用select for update

测试如下：test表100w条数据，先整张表更新，在另外一个session去更新其中的某一行，结果显示能成功(但比较慢)。截图如下。用了三个session做测试。第一个窗口用来更新整张表，第二个用来更新某一行 第三个用来查数据。

oracle 锁有line、block、table三种。

如何查看当前被锁定的对象？

根据当前session的sid，从v$locked_object 查看被锁定的对象。

select * from v$locked_object where sid=xxx

如何获取当前的sid。

从v$mystat或者用userenv函数。

select userenv('sid’) from dual; --userenv支持的参数有:CLIENT_INFO、ENTRYID、ISDBA、LANG、LANGUAGE、SID、SESSIONID、TERMINAL。select sid from v$mystat where rownum=1;

以下是oracle 11g文档对v$lock的描述

V$LOCK

V$LOCK lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch.

Column

Datatype

Description

ADDR

RAW(4 | 8)

Address of lock state object

KADDR

RAW(4 | 8)

Address of lock

SID

NUMBER

Identifier for session holding or acquiring the lock

TYPE

VARCHAR2(2)

Type of user or system lock

The locks on the user types are obtained by user applications. Any

process that is blocking others is likely to be holding one of these

locks. The user type locks are:

TM - DML enqueue

TX - Transaction enqueue

UL - User supplied

The system type locks are listed in Table 8-1. Be aware that not all types of locks are documented. To find a complete list of locks for the current release, query the V$LOCK_TYPE data dictionary view, described on "V$LOCK_TYPE".

ID1

NUMBER

Lock identifier #1 (depends on type)

ID2

NUMBER

Lock identifier #2 (depends on type)

LMODE

NUMBER

Lock mode in which the session holds the lock:

0 - none

1 - null (NULL)

2 - row-S (SS)

3 - row-X (SX)

4 - share (S)

5 - S/Row-X (SSX)

6 - exclusive (X)

REQUEST

NUMBER

Lock mode in which the process requests the lock:

0 - none

1 - null (NULL)

2 - row-S (SS)

3 - row-X (SX)

4 - share (S)

5 - S/Row-X (SSX)

6 - exclusive (X)

CTIME

NUMBER

Time since current mode was granted

BLOCK

NUMBER

A value of either 0 or 1, depending on whether or not the lock in question is the blocker.

Table 8-1 Values for the TYPE Column: System Types

System Type

Description

System Type

Description

AE

Edition enqueue

MR

Media recovery

AT

Lock held for the ALTER TABLE statement

NA..NZ

Library cache pin instance (A..Z = namespace)

BL

Buffer hash table instance

PF

Password File

CF

Control file schema global enqueue

PI, PS

Parallel operation

CI

Cross-instance function invocation instance

PR

Process startup

CU

Cursor bind

QA..QZ

Row cache instance (A..Z = cache)

DF

datafile instance

RT

Redo thread global enqueue

DL

Direct loader parallel index create

SC

System change number instance

DM

Mount/startup db primary/secondary instance

SM

SMON

DR

Distributed recovery process

SN

Sequence number instance

DX

Distributed transaction entry

SQ

Sequence number enqueue

FS

File set

SS

Sort segment

HW

Space management operations on a specific segment

ST

Space transaction enqueue

IN

Instance number

SV

Sequence number value

IR

Instance recovery serialization global enqueue

TA

Generic enqueue

IS

Instance state

TS

Temporary segment enqueue (ID2=0)

IV

Library cache invalidation instance

TS

New block allocation enqueue (ID2=1)

JQ

Job queue

TT

Temporary table enqueue

KK

Thread kick

UN

User name

LA .. LP

Library cache lock instance lock (A..P = namespace)

US

Undo segment DDL

MM

Mount definition global enqueue

WL

Being-written redo log instance

10g文档中对table lock的描述

Use the LOCK TABLE

statement to lock one or more tables, table partitions, or table subpartitions

in a specified mode. This lock manually overrides automatic locking and permits

or denies access to a table or view by other users for the duration of your

operation.

Some forms of locks can be placed on the same table at the same time. Other

locks allow only one lock for a table.

A locked table remains locked until you

either commit your transaction or roll it back, either entirely or to a

savepoint before you locked the table.

A lock never prevents other users from

querying the table. A query never places a lock on a table. Readers never block

writers and writers never block readers.

lockmode

Clause

Specify one of the following modes:

ROW SHARE

ROW SHARE permits concurrent access to the locked

table but prohibits users from locking the entire table for exclusive access.

ROW SHARE is synonymous with SHARE

UPDATE, which is included for compatibility with earlier versions

of Oracle Database.

ROW

EXCLUSIVE

ROW EXCLUSIVE is the same as ROW

SHARE, but it also prohibits locking in SHARE mode.

ROW EXCLUSIVE locks are automatically obtained when

updating, inserting, or deleting.

SHARE UPDATE

See ROW SHARE.

SHARE

SHARE permits concurrent queries but prohibits updates to the

locked table.

SHARE ROW

EXCLUSIVE

SHARE ROW EXCLUSIVE is used to look at

a whole table and to allow others to look at rows in the table but to prohibit

others from locking the table in SHARE mode or from updating

rows.

EXCLUSIVE

EXCLUSIVE permits queries on the locked table but prohibits any

other activity on it.

NOWAIT

Specify NOWAIT if you want the database to return control to you

immediately if the specified table, partition, or table subpartition is already

locked by another user. In this case, the database returns a message indicating

that the table, partition, or subpartition is already locked by another

user.

If you omit this clause, then the database waits until the table is

available, locks it, and returns control to you.