Oracle programming Art Study Notes (12)-Oracle locks

The following is a summary of Oracle locking policies:
· Oracle only adds row-level locks to data when it is modified. Under normal circumstances, it will not be upgraded to block-level locks or table-level locks.
· Oracle will never lock data if it only reads data. It will not be locked on the Data row because of simple read operations.
· Writer does not block the reader ). In other words, read is not blocked by write. This is almost different from all other databases. In other databases, reading is often blocked by writing.
· The writer wants to write a certain row of data, but the other writer has locked this row of data, so this writer will be blocked. The reader will never block the writer.

You need to know the following:
· Transactions are the core of each database.
· Submit the transaction only when necessary. The transaction size should only be determined based on the business logic.
· As long as necessary, the lock on the data should be kept as long as possible. A lock is not a rare resource.
· In Oracle, row-level locks have no related overhead. The number of resources required to lock 1 000 rows is exactly the same as the number of resources required to lock 1 row. This is a fixed constant.
· Do not think that the lock upgrade is "better for the system" (for example, using a table lock instead of a row lock ). In Oracle, lock escalate has no benefits to the system and will not save any resources.
· Concurrency and consistency can be obtained at the same time.

Oracle mainly has three types of locks, specifically:
· DML lock: DML stands for Data Manipulation Language ). For example, SELECT, INSERT, UPDATE, MERGE, and DELETE statements. The DML lock mechanism allows concurrent data modification.
· DDL lock: DDL stands for Data Definition Language (Data Definition Language), such as CREATE and ALTER statements. DDL locks can protect the definition of object structures.
· Internal locks and latches: Oracle uses these locks to protect its internal data structures.
Details: http://www.bkjia.com/database/201110/108942.html

Lock
The process of locking a row in Oracle is as follows:
(1) locate the address of the row to be locked.
(2) arrive at that line.
(3) Lock this row (if this row has been locked, wait until the end of the transaction to lock it, unless the NOWAIT option is used ).

That's all. Because the latches are an attribute of data, Oracle does not need a traditional lock manager. The transaction only finds the data. If the data is not locked, it is locked.
Note that the data may seem locked when it is found, but this is not the case. When a row locks data in Oracle, the row points to a copy of the transaction ID, which is stored in the block containing the data. When the lock is released, the transaction ID is retained. The transaction ID is unique to the transaction, indicating the rollback segment number, slot, and serial number. The transaction ID is left on the block containing the data row and can tell other sessions that you "own" the data (not all the data on the block is yours, only the line you modified "belongs to you ").
When another session arrives, it will see the lock ID. Because the lock ID represents a transaction, you can quickly check whether the transaction holding the lock is still active. If the lock is not active, the session is allowed to access the data. If the lock is still active, the session requires a notification once the lock is released. Therefore, there is a queuing mechanism: sessions with request locks will be queued, waiting for the transaction that currently has the lock to execute, and then get the data.

How to use the data itself to manage locking and transaction information? This is part of the block overhead. There is an overhead space (overhead) at the beginning of the database block, which stores a transaction table for the block. For each "actual" transaction that locks some data in the block, there is a corresponding entry in this transaction table. The size of this structure is determined by the two physical attribute parameters in the CREATE statement when the object is created:
· INITRANS: the initial pre-allocated size of the structure. For indexes and tables, the default size is 2.
· MAXTRANS: the maximum size of this structure. After Oracle 10, this setting has been discarded, and MAXTRANS is always 255.
By default, each block has two transaction slots at the beginning. The number of concurrent active transactions on a block is limited by the MAXTRANS value and the availability of the block space.
If there is not enough space to expand this structure, the block won't be able to get 255 concurrent transactions. At this time, it will wait and cause blocking.
Therefore, you may need to add INITRANS settings for frequently modified tables, or, more often, you may need to do the same for frequently modified indexes, because the index block contains more rows than the table. You may need to add PCTFREE or INITRANS to reserve sufficient space in advance on the block to cope with the number of possible concurrent transactions. In particular, if you expect that the block is almost full at the beginning (this indicates that there is no space on the block to dynamically scale down the transaction structure), you need to add PCTFREE or INITRANS.

You can perform a test:
Create a table that contains many rows and stores them into a block. This makes the block full at the beginning, leaving only a small amount of space, which limits the growth of the transaction table.

Create table t
(X int primary key,
Y varchar2 (4000)
);
 
Insert into t (x, y)
Select rownum, rpad ('*', 148 ,'*')
From dual
Connect by level <= 46;
Create table t
(X int primary key,
Y varchar2 (4000)
);

Insert into t (x, y)
Select rownum, rpad ('*', 148 ,'*')
From dual
Connect by level <= 46;

The following statement shows that 46 rows are in the same block. The reason why we select 148 characters is because we need two more characters to put down these 46 lines.
If the current block size of your Oracle database is 8 K, you can get the same results as the following tests.

Select length (y ),
Dbms_rowid.rowid_block_number (rowid) blk,
Count (*), min (x), max (x)
From t
Group by length (y), dbms_rowid.rowid_block_number (rowid );

LENGTH (Y) blk count (*) MIN (X) MAX (X)
--------------------------------------------------
148 291 46 1 46
Select length (y ),
Dbms_rowid.rowid_block_number (rowid) blk,
Count (*), min (x), max (x)
From t
Group by length (y), dbms_rowid.rowid_block_number (rowid );
 
LENGTH (Y) blk count (*) MIN (X) MAX (X)
--------------------------------------------------
148 291 46 1 46
 

Next, let multiple transactions lock the data on this block through the select... for update nowait Statement at the same time. The first transaction locks the first row, the second transaction locks the second row, and so on.
If you need to wait, a ORA-54 resource busy error is generated, which indicates that the transaction table on this block is used up.
By using AUTONOMOUS_TRANSACTION, you can use a session to complete this test without running a large number of SQL * PLUS sessions.

Create or replace procedure do_update (p_n in number)
As
Pragma autonomous_transaction;
Rochelle rec t % rowtype;
Resource_busy exception;
Pragma exception_init (resource_busy,-54 );
Begin
Select *
Into l_rec
From t
Where x = p_n
For update NOWAIT;
 
Do_update (p_n + 1 );
Commit;
Exception
When resource_busy
Then
Dbms_output.put_line ('locked out trying to select row' | p_n );
Commit;
When no_data_found
Then
Dbms_output.put_line ('We finished-no problems ');
Commit;
End;
/
Create or replace procedure do_update (p_n in number)
As
Pragma autonomous_transaction;
Rochelle rec t % rowtype;
Resource_busy exception;
Pragma exception_init (resource_busy,-54 );
Begin
Select *
Into l_rec
From t
Where x = p_n
For update NOWAIT;

Do_update (p_n + 1 );
Commit;
Exception
When resource_busy
Then
Dbms_output.put_line ('locked out trying to select row' | p_n );
Commit;
When no_data_found
Then
Dbms_output.put_line ('We finished-no problems ');
Commit;
End;
/

To execute this process, you can get the following results:

Tony @ ORA11GR2> exec do_update (1 );
Locked out trying to select row 38
 
The PL/SQL process is successfully completed.
Tony @ ORA11GR2> exec do_update (1 );
Locked out trying to select row 38

The PL/SQL process is successfully completed.
 

Blocking
There are five common DML statements that cause blocking: INSERT, UPDATE, DELETE, MERGE, and select for update.
FOR a blocked select for update, simply add the NOWAIT clause, which will not be blocked and will report an error of ORA-00054: Resource Busy.

1) blocked Insert
The most common case is that there is a table with a primary key or the table has a unique constraint, but two sessions try to insert a row with the same value. In this case, one of the sessions will be blocked.
In addition, if a foreign key is used, insertion of the child table may be blocked because the parent table on which it depends is being created or deleted.

2) blocked Update and Delete
If an uncommitted transaction performs the Update or Delete operation on this row, blocking occurs.

3) blocked Merge
Merge is only a combination of Insert and UPDATE (10 Gb later and Delete. Therefore, the result is the same as above.

Deadlock
If both sessions hold the resources required by the other session, a deadlock occurs ).
Based on experience, the top cause of the deadlock is that the foreign key is not indexed (the second reason is that the bitmap index on the table is updated concurrently ).
In the following two cases, Oracle adds a full table lock to the child table after modifying the parent table:
· If the primary key of the parent table is updated, the child table will be locked because there is no index on the foreign key.
· If a row in the parent table is deleted, the entire sub-table will also be locked because there is no index on the foreign key.
In Oracle9i and later versions, these full table locks are short-lived, meaning they only exist during DML operations, not throughout the transaction. Even so, these full table locks can cause serious locking problems.

In addition to full table locks, foreign keys that are not indexed may also cause performance problems.
· If there is on delete cascade and no index is added to the sub-table
For example, EMP is a sub-table of DEPT, and delete deptno = 10 should be CASCADE (CASCADE) to EMP [4]. If DEPTNO in EMP has no index, a full table scan is performed on EMP when each row in the DEPT table is deleted. This full table scan may be unnecessary. If multiple rows are deleted from the parent table, the child table will be scanned every time a row is deleted from the parent table.
· Query sub-tables from the parent table
For example, using DEPTNO to query EMP tables is quite common. If you frequently run the following queries, the query speed will be slowed down if no index is available:
Select * from dept, emp where emp. deptno = dept. deptno and dept. deptno =: X;

Generally, you do not need to add an index to a foreign key only when the following conditions are met:
· The row is not deleted from the parent table.
· The unique key/primary key value of the parent table is not updated (note that the tool sometimes inadvertently updates the primary key !).
· No child tables are joined from the parent table

You can use the following script to search for Foreign keys without indexes.

Column columns format a30 word_wrapped
Column tablename format a15 word_wrapped
Column constraint_name format a15 word_wrapped
 
Select table_name, constraint_name,
Cname1 | nvl2 (cname2, ',' | cname2, null) |
Nvl2 (cname3, ',' | cname3, null) | nvl2 (cname4, ',' | cname4, null) |
Nvl2 (cname5, ',' | cname5, null) | nvl2 (cname6, ',' | cname6, null) |
Nvl2 (cname7, ',' | cname7, null) | nvl2 (cname8, ',' | cname8, null)
Columns
From (select B. table_name,
B. constraint_name,
Max (decode (position, 1, column_name, null) cname1,
Max (decode (position, 2, column_name, null) cname2,
Max (decode (position, 3, column_name, null) cname3,
Max (decode (position, 4, column_name, null) cname4,
Max (decode (position, 5, column_name, null) cname5,
Max (decode (position, 6, column_name, null) cname6,
Max (decode (position, 7, column_name, null) cname7,
Max (decode (position, 8, column_name, null) cname8,
Count (*) col_cnt
From (select substr (table_name, 1, 30) table_name,
Substr (constraint_name, 1, 30) constraint_name,
Substr (column_name, 1, 30) column_name,
Position
From user_cons_columns),
User_constraints B
Where a. constraint_name = B. constraint_name
And B. constraint_type = 'R'
Group by B. table_name, B. constraint_name
) Cons
Where col_cnt> ALL
(Select count (*)
From user_ind_columns I
Where I. table_name = cons. table_name
And I. column_name in (cname1, cname2, cname3, cname4,
Cname5, cname6, cname7, cname8)
And I. column_position <= cons. col_cnt
Group by I. index_name
)
/
Column columns format a30 word_wrapped
Column tablename format a15 word_wrapped
Column constraint_name format a15 word_wrapped

Select table_name, constraint_name,
Cname1 | nvl2 (cname2, ',' | cname2, null) |
Nvl2 (cname3, ',' | cname3, null) | nvl2 (cname4, ',' | cname4, null) |
Nvl2 (cname5, ',' | cname5, null) | nvl2 (cname6, ',' | cname6, null) |
Nvl2 (cname7, ',' | cname7, null) | nvl2 (cname8, ',' | cname8, null)
Columns
From (select B. table_name,
B. constraint_name,
Max (decode (position, 1, column_name, null) cname1,
Max (decode (position, 2, column_name, null) cname2,
Max (decode (position, 3, column_name, null) cname3,
Max (decode (position, 4, column_name, null) cname4,
Max (decode (position, 5, column_name, null) cname5,
Max (decode (position, 6, column_name, null) cname6,
Max (decode (position, 7, column_name, null) cname7,
Max (decode (position, 8, column_name, null) cname8,
Count (*) col_cnt
From (select substr (table_name, 1, 30) table_name,
Substr (constraint_name, 1, 30) constraint_name,
Substr (column_name, 1, 30) column_name,
Position
From user_cons_columns),
User_constraints B
Where a. constraint_name = B. constraint_name
And B. constraint_type = 'R'
Group by B. table_name, B. constraint_name
) Cons
Where col_cnt> ALL
(Select count (*)
From user_ind_columns I
Where I. table_name = cons. table_name
And I. column_name in (cname1, cname2, cname3, cname4,
Cname5, cname6, cname7, cname8)
And I. column_position <= cons. col_cnt
Group by I. index_name
)
/
 

There is a way to quickly locate the code segment that causes the sub-table to be locked. Run the following command:
Alter table <child table name> disable table lock;
Now, any UPDATE or DELETE that may cause the child table to be locked will receive the following error:
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for <child table name>
This helps you track problematic code segments (for example, you do not think the primary key of the parent table is updated or deleted ).

Author: NowOrNever