Concurrency and Transaction in Oracle

Locks Obtained by DML Statements in Oracle

from Oracle Database Concepts

Oracle provides data concurrency and integrity between transactions using its locking mechanisms. Because the locking mechanisms of Oracle are tied closely to transaction control, application designers need only define transactions properly, and Oracle automatically manages locking.

Keep in mind that Oracle locking is fully automatic and requires no user action. Implicit locking occurs for all SQL statements so that database users never need to lock any resource explicitly. Oracle's default locking mechanisms lock data at the lowest level of restrictiveness to guarantee data integrity while allowing the highest degree of data concurrency.

The purpose of a DML lock (data lock) is to guarantee the integrity of data being accessed concurrently by multiple users. DML locks prevent destructive interference of simultaneous conflicting DML or DDL operations. DML statements automatically acquire both table-level locks and row-level locks.

The following table summarize how Oracle automatically locks data on behalf of different DML operations.

DML Statement	Row Locks	Table Locks
SELECT ... FROM table
INSERT INTO table ...	Exclusive Row Locks	Row Exclusive Table Locks
UPDATE table ...	Exclusive Row Locks	Row Exclusive Table Locks
DELETE FROM table ...	Exclusive Row Locks	Row Exclusive Table Locks
SELECT ... FROM table ... FOR UPDATE OF ...	Exclusive Row Locks	Row Share Table Locks

• Exclusive Row Lock
  • A transaction acquires an exclusive row lock for each individual row modified by one of the following statements: INSERT, UPDATE, DELETE, and SELECT with the FOR UPDATE clause.
  • A modified row is always locked exclusively so that other transactions cannot modify the row until the transaction holding the lock is committed or rolled back.
• Row Exclusive Table Locks
  • A row exclusive table lock generally indicates that the transaction holding the lock has made one or more updates to rows in the table.
  • A row exclusive table lock held by a transaction allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, row exclusive table locks allow multiple transactions to obtain simultaneous row exclusive and row share table locks for the same table.
  • A row exclusive table lock held by a transaction prevents other transactions from manually locking the table for exclusive reading or writing.
• Row Share Table Locks
  • A row share table lock indicates that the transaction holding the lock on the table has locked rows in the table and intends to update them.
  • A row share table lock held by a transaction allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table.
  • A row share table lock held by a transaction prevents other transactions from exclusive write access to the same table using only LOCK TABLE table IN EXCLUSIVE MODE statement.

Locking for SELECT statement

• A query acquires no data locks. Therefore, other transactions can query and update a table being queried, including the specific rows being queried. Because queries lacking FOR UPDATE clauses do not acquire any data locks to block other operations, such queries are often referred to in Oracle as nonblocking queries.
• A query does not have to wait for any data locks to be released; it can always proceed. (Queries may have to wait for data locks in some very specific cases of pending distributed transactions.)

Locking for INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE statement

• The transaction that contains a DML statement acquires exclusive row locks on the rows modified by the statement. Other transactions cannot update or delete the locked rows until the locking transaction either commits or rolls back.
• The transaction that contains a DML statement does not need to acquire row locks on any rows selected by a subquery or an implicit query, such as a query in a WHERE clause. A subquery or implicit query in a DML statement is guaranteed to be consistent as of the start of the query and does not see the effects of the DML statement it is part of.
• A query in a transaction can see the changes made by previous DML statements in the same transaction, but cannot see the changes of other transactions begun after its own transaction.
• In addition to the necessary exclusive row locks, a transaction that contains a DML statement acquires at least a row exclusive table lock on the table that contains the affected rows. If the containing transaction already holds a share, share row exclusive, or exclusive table lock for that table, the row exclusive table lock is not acquired. If the containing transaction already holds a row share table lock, Oracle automatically converts this lock to a row exclusive table lock.

Data Dictionary

Data dictionary is data(meta-data) for a database. The structure, relationship and usage of schema objects are maintained at data dictionary.
The implementation and feature of data dictionary is different by database products.

Oracle

Oracle maintains tables and views for data dictionary under SYS user(schema). Plain users can access views according to their roles and privileges.
User accessible views are named with prefix of 'USER_', 'ALL_', or 'DBA_'.

Oracle also maintain data or statistics of database's activity and performance in so-called dynamic performance views. Actually they are views named with prefix of 'V_$' and also owned by SYS schema.
Oracle provides public synonyms named with prefix of 'V$' for users properly privileged.

References

• Data Dictionary and Dynamic Performance Views
• Static Data Dictionary Views

DB2

DB2 UDB provides data dictionary under SYSCAT schema. SYSCAT.TABLES, SYSCAT.VIEWS, SYSCAT.COLUMNS, SYSCAT.INDEXES, SYSCAT.TRIGGERS are views describing the structure of database instance.

For dynamic behavior of database, DB2 UDB provides views under SYSSTAT schema.

References

• DB2 System Catalog Views: Everything You Need to Know About Your DB Objects

Characteristics of Oracle

Quoted identifier is case-sensitive in Oracle

For oracle, identifier such as table name or column name can be quoted with double quotation mark (").
Quoted identifier can be oracle reserved word such as comment, check, add, group, lock or set. But unlike non-quoted identifier, quoted identifier is case sensitive.

Empty string is treated as a null

Oracle treats a character value with a length of zero(empty string) as null.
So, the following statement would set null to descn column of produt of which id is 'PD100'.

insert into products (id, name, descn) values ('PD100', 'Vega', '');

Oracle says that this may not continue to be true in future releases, but it seems very difficult to change this feature.

• http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements005.htm#i59110

Naming Rules of Schema Objects by Database

The max. length of identifiers

	Database	Table	View	Sequence	Synonym	Index	Constraint	Trigger	Procedure/Function	Database Link	Column	Alias
Oracle	8	30	30	30	30	30	30	30	30	128	-	-
MySQL 5.0	64	64	64	-	64	64	-	64	64	-	64	255
PostgreSQL 8.x	63	63	63	63	-	63	63	63	63	-	63	63

Tips on Oracle for Non-admin User []

Moved to the page http://3rdstage.wikia.com/ in my wiki as of 15th Nov. 2012.

Readings : References, Tutorials, and Articles

• Oracle Database 10g Documentation Library
• System Privileges of Oracle Database 10g

Finding roles and privileges of a user or current session.

With more emphasis on securities, default privileges for a regular(non-DBA) user is very restrictive. As of Oracle database 10.2, CONNECT and RESOURCE which are typical predefined roles for a regular user don't include create view and create synonym privileges.

So often, you should have to check what roles and privileges are given for a user. You can do that using the following statements.

-- finding all roles for the current session
SELECT * FROM SESSION_ROLES;

-- finding all privileges for the current session
SELECT * FROM SESSION_PRIVS;

Required roles to access dynamic performance (V$) views.

To access the dynamic performance views such as V$SESSION, V$PARAMETER and V$LOCK as well as static dictionary views of DBA such as DBA_TABLES, DBA_SEQUENCES and DBA_SYNONYMS, the user need SELECT_CATALOG_ROLE

DBA user can grant the role to the user using the following statement.

GRANT select_catalog_role TO :username;

Finding the values of parameters currently in effect.

Checking the context of current operation is really one of the most basic thing you can do in any circumstances. With Oracle, that is parameters. You can identify the current value of SGA max size, max sessions, optimizer mode or other settings throughout the parameter.

SELECT * FROM V$PARAMETER ORDER BY name;

V$PARAMETER is one of public synonyms in default installation.

Identifying the character-set of the Oracle instance

Use the following statement.

select * from nls_database_parameters
where parameter = 'NLS_CHARACTERSET';

Changing the password of the user

Use the following statement.

ALTER USER :username IDENTIFIED BY :password;

Be cautious not to mark single quotation mark around values of :username and :password. The following is simple example.

ALTER USER hr IDENTIFIED BY hr88rh;

When you are connected to Oracle using SQL*Plus, you can use password command to change your password.
The following is sample command line when changing password of hr user

SQL>password
Changing password for hr
Old password :
New password : 
Retype new password :
Password changed

Viewing information about users

About current user, access the following dictionary views.

View	Description
USER_USERS	Describes only the current user.
ALL_USERS	Lists users visible to the current user, but does not describe them.
USER_TS_QUOTAS	Describes tablespace quotas for current user.
USER_RESOURCE_LIMITS	Displays the resource limits for the current user.
USER_PASSWORD_LIMITS	Describes the password profile parameters that are assigned to the user.

More privileged users can use the following views to access the information of all users.

View	Description
DBA_USERS	Describes all users of the database.
DBA_TS_QUOTAS	Describes tablespace quotas for users.
DBA_PROFILES	Displays all profiles and their limits.
V$SESSION	Lists session information for each current session, includes user name.

Use connect command of SQL*Plus without tnsnames.ora

• How to connect SQLPlus without tnsnames.ora

Identifying execution plan of query

Using DBMS_XPLAN.DISPLAY_CURSOR function

The execution plan of last executed query in plan table can be printed out using DBMS_XPLAN.DISPLAY_CURSOR function.

DBMS_XPLAN package was introduced at Oracle 9i, but DISPLAY_CURSOR function was added with Oracle 10g.
DBMS_XPLAN.DISPLAY_CURSOR function accesses V$SESSION, so the user need to have SELECT_CATALOG_ROLE role to correctly execute select * from table(DBMS_XPLAN.DISPLAY_CURSOR)

select employee_id, first_name || ' ' || last_name as name, department_id, job_id, manager_id  
from hr.employees  
where job_id = 'SA_REP'  
and manager_id in (146, 147);  

select * from table(DBMS_XPLAN.DISPLAY_CURSOR);

If you want to see just execution plan and not to actually execute the query. You can use EXPLAIN PLAN statement.

explain plan for
select employee_id, first_name || ' ' || last_name as name, department_id, job_id, manager_id  
from hr.employees  
where job_id = 'SA_REP'  
and manager_id in (146, 147);  

select * from table(DBMS_XPLAN.DISPLAY_CURSOR);

Using autotrace with SQL*Plus

When using SQL*Plus, you can turn on autotrace feature for the current session using SET AUTOTRACE ON command.

After the autotrace is enabled, execution of query would display normal result data and the execution plan right after it.

To use this feature, you must create a PLAN_TABLE table in your schema and then have the PLUSTRACE role granted to you.

SQL>SET AUTOTRACE ON
SQL>SELECT employee_id, first_name || ' ' || last_name as name, department_id, job_id, manager_id
2 FROM hr.employees
3 WHERE job_id = 'SA_REP'  
4 AND manager_id IN (146, 147);  

If you want to see just execution plan and not to actually execute the query, you can use SET AUTOTRACE TRACEONLY command.

SQL>SET AUTOTRACE TRACEONLY
SQL>SELECT employee_id, first_name || ' ' || last_name as name, department_id, job_id, manager_id
2 FROM hr.employees
3 WHERE job_id = 'SA_REP'  
4 AND manager_id IN (146, 147);  

Readings

• DBMS_XPLAN of Oracle Database 10.2
• Tracing Statements of Oracle Database 10.2
• EXPLAIN PLAN statement of Oracle Database 10.2

Session Limits on Oracle

Oracle manages the global(instant level) limit for concurrent session and can be configured to manage per-user session limit through profile

Global concurrent session limit

The SESSIONS of initialization parameter specifies the max. number of sessions that can be created in the system at the same time. The OPEN_CURSORS and SESSIONS specify the max. number of concurrent open cursors for the system.

• http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams191.htm#i1133629
• http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams138.htm#CHDFEAFG

Per-user concurrent session limit

Using profile, Oracle can be configured to manage resource limits to a specific user. The configurable resources include SESSIONS_PER_USER, CPU_PER_SESSION, CPU_PER_CALL, and so on.

• http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/authoriz.htm#i1009631
• http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6010.htm#i2065930

Paginated Query

The statements to retrieve partial rows from selected data are like followings according to the DBMS.

Oracle (8i or higer)

Use ROW_NUMBER() function and subquery. In some cases, such as hierarchical query (START WITH ~ CONNECT BY ~ clause) using ORDER SIBLINGS BY, ROW_NUMBER() function can't be used in subquery, so in those cases, ROWNUM with double-nested subquery can be used.

• Syntax :

SELECT *
FROM (
   SELECT ROW_NUMBER() OVER (ORDER BY ~) AS row_no, ~
   FROM ~ 
   WHERE ~ )
WHERE row_no BETWEEN m AND n

• Example :

select *
from(
  select row_number(order by product_name) as row_no,
      product_id, product_name, price, manufacturer
  from tb_product
  where category_id = 101)
where row_no between 21 and 30

Microsoft SQL Server (2005 or higher)

• Syntax :

WITH ~ AS(
   SELECT ~ , ROW_NUMBER() OVER(~) AS row_no
   FROM ~
   WHERE ~
)
SELECT ~
FROM ~
WHERE row_no BETWEEN m AND n

• Example :

with v_product as (
   select row_number() over(order by product_name) as row_no,
      product_id, product_name, price, manufacturer
   from tb_product
   where category_id = 101)
select *
from v_product
where row_no between 21 and 30

MySQL

• Syntax :

SELECT ~ 
FROM ~ 
WHERE ~ 
ORDER BY ~
LIMIT offset, row_count

• Example :

select product_id, product_name, price, manufacturer
from tb_product
where category_id = 101
order by product_name
limit 20, 10

The query with both LIMIT clause and ORDER BY clause sort the result before limit unlike Oracle so, you need not use subquery.

PostgresSQL

• Syntax :

SELECT ~ 
FROM ~ 
WHERE ~ 
ORDER BY ~ 
LIMIT row_count OFFSET offset

• Example :

select product_id, product_name, price, manufacturer
from tb_product
where category_id = 101
order by product_name
limit 10 offset 20

The query with both LIMIT/OFFSET clause and ORDER BY clause sort the result before limit unlike Oracle so, you need not use subquery.

Informix

• Syntax :

SELECT SKIP offset FIRST row_count selection_list
FORM ~
WHERE ~
ORDER BY ~

Readings

• Limiting result rows of SELECT (from Wikipedia)

Maximum Length of VARCHAR Type in Major DBMS

• Oracle 8 ~ 10g : 4,000 Byte
• Microsoft SQL Server : 8,000 Byte (not sure)
• MySQL 5.0.3 before : 255 Byte
• MySQL 5.0.3 or higher : 65,535 Byte

DbUnit with Oracle

When using DbUnit with Oracle, the followings are recommened to avoid exceptions or something like that.

• Provide the schema name when creating the database connection.
• Use the OracleDataTypeFactory.
• Disable FEATURE_QUALIFIED_TABLE_NAMES
• Enable FEATURE_SKIP_ORACLE_RECYCLEBIN_TABLES

IDatabaseConnection conn = new OracleConnection(ds.getConnection(), "MYSCHEMA");
DatabaseConfig config = conn.getConfig();
config.setFeature(DatabaseConfig.FEATURE_QUALIFIED_TABLE_NAMES, false);
config.setFeature(DatabaseConfig.FEATURE_SKIP_ORACLE_RECYCLEBIN_TABLES, true);
config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new OracleDataTypeFactory());