Oracle database links – locking strategy

In this post mechanism of Oracle database link technology along with locking strategy used by Oracle database will be described.

Today in almost all production environments based on Oracle databases where I’ve been called to help with optimization & tuning, it is a quite common to have at least one or more db links to connect/fetch data from remote Oracle database.

Although there many ways of connecting to a remote database such as:

• Oracle Heterogeneous Services (to get data from non Oracle database)
• Java stored procedures
• AQ / JMS
• REST…

, Oracle database link technology, thanks to its easy configuration, is definitely the most popular one.

Despite being so heavily used, db links is also one of the most misunderstood technologies out there.

Oracle database links uses two-phase commit protocol (2PC) to ensure when you commit transaction, Oracle will commit transaction on both sides of the database link connection: source and target database.

In a 2PC technology, a database which initiate connection is a so called coordinator for the distributed transaction.

2PC includes voting mechanism since one site will ask the other one to confirm if it is ready for commit.

Main goal of 2PC is to limit the window in which serious transaction errors might occur (e.g. in-doubt distributed transactions).

Background process (RECO) will try to resolve some issues, but you also might be forced to manually use COMMIT FORCE or ROLLBACK FORCE option.

Besides complexities that comes with distributed transactions, there are a few limitations you need to be aware of such as:

• remote site can’t commit transaction (only database which initiates distributed transaction – coordinator)
• it is not possible to use SAVEPOINT with distributed transactions
• it is not possible to execute DDL commands over a database link

You can control  which site (local – one that initiate connection or remote) will be responsible for commit with COMMIT_POINT_STRENGTH parameter.

To check db link locking behavior we need to have two databases (one local and one remote) and to prepare two tables (one local and one remote).

Let’s start by creating a test table on local database:

create table test_local_lock
tablespace USERS
nologging 
as
select * from all_tables 
where rownum < 1000;

Next step is to connect to remote database and create one table there by executing:

create table test_remote_lock
tablespace USERS
nologging 
as
select * from all_tables 
where rownum < 1000;

Now that we have two tables (one on local and one on remote database), I’m going to update local table by executing:

update test_local_lock a 
set pct_free = 99
where a.owner = 'SYS' and a.table_name = 'USER$';

It is important not to commit or rollback transaction (if you are using GUI tool like Oracle SQL Developer, check if auto commit option is enabled).

To check locks triggered by the previous UPDATE DML statement, you can execute the following:

select username,
 l.sid,
 l.type,
 l.lmode,
 l.request,
 s.process,
 s.sql_id,
 s.sql_exec_start
 from v$lock l, v$session s
 where 
 l.type <> 'AE'
 and l.sid = s.sid
 and s.username = USER;

Here I’ve excluded locks related to EBR (Edition Based redefinition) – a new feature from the 11g version of Oracle database.

Result you can check on the picture below.

There are two rows – locks generated by UPDATE statement.

If you take a look at the v$lock definition, you can find meaning of the most important columns.

TYPE – Type of user or system lock

• TM – DML enqueue
• TX – Transaction enqueue

LMODE – Lock mode in which the session holds the lock

• 3 – row-X (SX)
• 6 – exclusive (X)

REQUEST – Lock mode in which the process requests the lock

• 0 – none

If I execute the same DML over a db link

update test_remote_lock@db_link_to_remote_db a 
set pct_free = 99
where a.owner = 'SYS' and a.table_name = 'USER$';

and check generated locks by executing the following SQL:

select username,
 l.sid,
 l.type,
 l.lmode,
 l.request,
 s.process,
 s.sql_id,
 s.sql_exec_start
 from v$lock l, v$session s
 where 
 l.type <> 'AE'
 and l.sid = s.sid
 and s.username = USER;

output will still contain two rows similar to the next picture:

but this time TYPE and LMODE will contain different result set:

TYPE – will change from TM (DML enqueue)→ DX (Distributed transaction entry)

LMODE – will change from the 3 row-X (SX)→ 4 share (S)

Additionally two new entries/rows will show up in the Global transaction dictionary view:

select * from v$global_transaction gt;

We can even get the location of transaction within UNDO, but this will be beyond the scope of this article.

Instead, let’s inspect Oracle behavior if I replace UPDATE DML statement with SELECT.

I’ll start by querying the table from the local database.

select * from test_local_lock;

As you can observe, there will be no rows in any lock mode, which is expected behavior (SELECT is not DML statement and for that reason it shouldn’t produce row locks).

Let’s inspect what will happen if I execute SELECT statement over database link.

SELECT * FROM test_remote_lock@db_link_to_remote_db a 
WHERE a.owner = 'SYS' and a.table_name = 'USER$';

In another session you can execute the following SQL to check if there are any row locks:

select username,
 l.sid,
 l.type,
 l.lmode,
 l.request,
 s.process,
 s.sql_id,
 s.sql_exec_start
 from v$lock l, v$session s
 where 
 l.type <> 'AE'
 and l.sid = s.sid
 and s.username = USER;

This time we can see two rows as if I had executed a DML statement (UPDATE) instead of SELECT.

The column LMODE shows 4 & 6 – the identical lock modes as we had with UPDATE DML over a database link, and TYPE of user or system lock (column TYPE) is also identical which means that behavior of SELECT over database link is the same as DML over database link (same locks will be generated).

If you try to execute SELECT over a database link from some GUI tool such as Oracle SQL Developer and then try to close connection, before exiting SQL Developer will ask you if you want to commit, rollback or abort transaction.

Although that sounds weird, now that you understand what’s going on under the hood, it’s expected behavior.

Summary:

When you design enterprise architecture, you should understand all implications, especially when you have to deal with distributed database systems that need to communicate with each other.

In case of database links, the most important is to understand overhead and complexities involved with distributed transaction that I’ve described in this article.

What I’ve found frequently is that even experienced Oracle DBA’s & developers miss to spot that even a simple SELECT query changes its behavior significantly when executed over a database link.

In the next blog I’ll explain some of the issues and performance penalties related to queries and DMLs over a database link.

Get notified when a new post is published!

Name*

Email*

 Please accept terms & conditions