What does lock mean?

Source: Internet
Author: User

What does lock mean?
The concept of locks in racle is very important. For other relational databases, not to mention, some databases require locks to issue select statements, but oracle has an independent undo (which can read modified data from undo) so no lock is applied.
The following describes several lock modes in oracle:

Lock code Lock mode name Abbreviated lock mode Lock mode alias Lock level
0 None None None
1 Null Null Null Table-Level Lock
2 ROW-S SS RS Table-Level Lock
3 ROW-X SX RX Table-Level Lock
4 Share S S Table-Level Lock
5 S/ROW-X SSX SRX Table-Level Lock
6 Exclusive X X Table/row-Level Lock
Lock code description:
0: none
1: null
2: Row-S row sharing (RS): share table lock (Row share)
3: Row-X (RX): row exclusive)
4: Share share lock (S): prevents other DML operations (Share)
5: S/Row-X dedicated shared rows (SRX): share row exclusive)
6: exclusive (X): exclusive lock, maximum level lock, exclusive)
(The larger the number, the higher the lock level, the more operations affected)
TX indicates row-level locks, and TM indicates table-level locks.
The following is about summary of table locks:
SQL Statement Mode of table lock RS RX S SRX X
Select * from table .... None Y Y Y Y Y
Insert into table .... RX Y Y N N N
Update table .... RX Y * Y * N N N
Delete from table .... RX Y * Y * N N N
Select... from table for update RS Y * Y * Y * Y * N
Lock table in row share mode RS Y Y Y Y N
Lock table in row execlusive RX Y Y N N N
Lock table in share mode S Y N Y N N
Lock table in share row execlusive mode SRX Y N N N N
Lock table in execlusive mode X N N N N N
Y (YES) N (NO)
Y *: if no conficting row locks are held by another transaction. otherwise, waits occur. (if there is no conflicting row lock, it is held by another transaction. Otherwise, wait)
It is important to understand the lock concept, especially when writing an application.
-Bash-3.2 $ lsb_release-
Description: Enterprise Linux Server release 5.5 (Carthage)
Release: 5.5
Codename: Carthage
SQL> select * from v $ version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
SQL> show user;
The USER is "HR"
SQL> create table t (id number, name varchar2 (10 ));
The table has been created.
SQL> insert into t values (1, 'diy ');
One row has been created.
SQL> insert into t values (2, 'OS ');
One row has been created.
SQL> COMMIT;
Submitted.
Block ):

SQL> update t set name = 'd where id = 1;

1 row updated.

SQL> select distinct sid from v $ mystat;


SID
----------
21


In another session:
SQL> select distinct sid from v $ mystat;


SID
----------
19


SQL> update t set name = 'O' where id = 1;

At this time, the system will wait until the first transaction ends (commit or rollback). This is a block, not a deadlock!

We analyze this process:

SQL> show user;
USER is "SYS"
SQL> select * from v $ lock where sid in (19,21) order by sid;


Addr kaddr sid ty ID1 ID2 LMODE REQUEST CTIME BLOCK
----------------------------------------------------------------------------------------
38687638 38687664 19 AE 100 0 4 0 810 0
38687ED4 38687F00 19 TX 262175 1888 0 6 230 0
004CA6B0 004CA6E0 19 TM 76714 0 3 0 230 0
004CA6B0 004CA6E0 21 TM 76714 0 3 0 240 0
37FBACC0 37FBAD00 21 TX 262175 1888 6 0 240 1 (a transaction is locked)
38687720 3868774C 21 AE 100 0 4 0 3730 0

You have selected 6 rows.

Note: AE: Edition Lock is the new Lock type of 11 GB. This is a session Lock, and there will be a Lock as long as there is a session. In this case, if the session number is 21, the row id = 1 is updated first, and a TM (RX) lock and a TX (X) Lock are obtained;
A tmlock (RX) is also obtained for session 22, but it is compatible with the above TM, so it is not blocked at this time,
But because the row lock is not compatible with the above row lock, the row lock X is not obtained, as can be seen from the above LMODE.


We can analyze the locked objects in the database through the following two views:
SQL> select * from v $ locked_object;
Xidusn xidslot xidsqn OBJECT_ID SESSION_ID ORACLE_USERNAME OS _USER_NAME PROCESS LOCKED_MODE
-------------------------------------------------------------------------------------------------------------------------------------------------
0 0 0 76714 19 HR oracle 6130 3
4 31 1888 76714 21 HR oracle 5022 3
SQL> SELECT * FROM DBA_OBJECTS WHERE OBJECT_ID = 76714;


OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME timestamp status t g s namespace EDITION_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hr t 76714 76714 TABLE 2-4 months-15 2-4 months-15 2015-04-22: 12: 39: 06 valid n 1

When re-indexing, in order not to affect system performance, we often:
Alter index index_name rebuild online;
But why not: alter index index_name rebuild
The following is a simple example:
SQL> create table ttt as select * from dba_objects;
The table has been created.
SQL> select count (*) from dba_objects;


COUNT (*)
----------
72746
SQL> create index index_id on TTT (OBJECT_ID );


The index has been created.

SQL> set autotrace traceonly;
SQL> SELECT * FROM TTT;


Row 72746 has been selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 774701505


--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 51569 | 10 M | 283 (1) | 00:00:04 |
| 1 | table access full | TTT | 51569 | 10 M | 283 (1) | 00:00:04 |
--------------------------------------------------------------------------


Note
-----
-Dynamic sampling used for this statement (level = 2)




Statistics
----------------------------------------------------------
308 recursive cballs
0 db block gets
5909 consistent gets
1035 physical reads
0 redo size
8067725 bytes sent via SQL * Net to client
53755 bytes encoded ed via SQL * Net from client
4851 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72746 rows processed

SQL> alter index index_id rebuild;


The index has been changed.
SQL> SELECT * FROM TTT;


Row 72746 has been selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 774701505


--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 51569 | 10 M | 283 (1) | 00:00:04 |
| 1 | table access full | TTT | 51569 | 10 M | 283 (1) | 00:00:04 |
--------------------------------------------------------------------------


Note
-----
-Dynamic sampling used for this statement (level = 2)




Statistics
----------------------------------------------------------
36 recursive cballs
0 db block gets
5886 consistent gets
0 physical reads
0 redo size
8067725 bytes sent via SQL * Net to client
53755 bytes encoded ed via SQL * Net from client
4851 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72746 rows processed

SQL> alter index index_id rebuild online;


The index has been changed.


SQL> SELECT * FROM TTT;


Row 72746 has been selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 774701505


--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 51569 | 10 M | 283 (1) | 00:00:04 |
| 1 | table access full | TTT | 51569 | 10 M | 283 (1) | 00:00:04 |
--------------------------------------------------------------------------


Note
-----
-Dynamic sampling used for this statement (level = 2)




Statistics
----------------------------------------------------------
5 recursive cballs
0 db block gets
5879 consistent gets
0 physical reads
0 redo size
8067725 bytes sent via SQL * Net to client
53755 bytes encoded ed via SQL * Net from client
4851 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72746 rows processed

Both scan methods are full table scan, and sorting (sort operation) occurs. However, the rebulid online operation performs better than the rebulid operation. We can see from the number of logical reads.
The rebulid operation blocks the dml operation, but the online operation does not (the online operation reduces the lock level)
Deadlock:
SQL> show user;
The USER is "HR"
SQL> select * from t;
ID NAME
--------------------
1 diy
2 OS
SQL> SELECT DISTINCT SID FROM V $ MYSTAT;
SID
----------
21
SQL> update t set name = 'd where id = 1;
1 row updated.




SQL> SHOW USER;
The USER is "HR"
SQL> select * from t;
ID NAME
--------------------
1 diy
2 OS
SQL> SELECT DISTINCT SID FROM V $ MYSTAT;
SID
----------
19
SQL> update t set name ='s 'where id = 2;
1 row updated.



SQL> SHOW USER;
The USER is "HR"
SQL> select distinct sid from v $ mystat;
SID
----------
21
SQL> update t set name = 'y' where id = 2;
Update t set name = 'y' where id = 2
*
Row 3 has an error:
ORA-00060: deadlock detected while waiting for resources



SQL> show user;
The USER is "HR"
SQL> select distinct sid from v $ mystat;
SID
----------
19
SQL> update t set name ='s 'where id = 1;

The preceding steps are arranged in the order of operation.
There is a warning in the alarm log (alert:
Weds Apr 22 14:43:05 2015
ORA-00060: Deadlock detected. More info in file/u01/app/oracle/diag/rdbms/orcl3939/orcl3939/trace/orcl3939_ora_5024.trc.
View the dump file:
.......
.......
* ** 14:43:04. 053
Deadlock detected (ORA-00060)
 
[Transaction Deadlock]
 
The following deadlock is not an ORACLE error. It is
Deadlock due to user error in the design of an application
Or from issuing incorrect ad-hoc SQL. The following
Information may aid in determining the deadlock:
 
Deadlock graph:
--------- Blocker (s) -------- --------- Waiter (s )---------
Resource Name process session holds waits
TX-00030014-000009b7 24 21X32 19 X
TX-0006000d-00000a27 32 19X24 21 X
 
Session 21: DID 0001-0018-00000019 session 19: DID 0001-0020-00000014
Session 19: DID 0001-0020-00000014 session 21: DID 0001-0018-00000019
 
Rows waited on:
Session 21: obj-rowid = 00012BAA-AAASuqAAEAAABuvAAB
.........
.........
Is the content above too detailed! Which session does rowid tell us! We can further study deadlocks.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.