Monday, July 11, 2011

Lock Waits ... Who is locking Who and What ?

Database row level locking mechanism is critical to achieve data consistency. The way Oracle deals with locks is complex and very rich. However, it's the application that accesses the database that really takes the decision of locking records or not depending on the its intention to modify data or not.

In Client-Server architectures, the locking strategy has most of the time been to use the "pessimistic" mode, which behavior can be described as: "as soon as I have the intention to modify, let me acquire a lock and reserve the record exclusively for my updates preventing any other users to interfere".

This mode sometimes is subject to perverse effects of egoist applications : Locking data but taking time to modify or never modifying the record preventing and making the other users to wait until the egoist application decide to release the record or fail.

With the advent of 3-tier architectures, new data representation is implemented at the midtier level, most of the time via Object Relational Mapping technics (examples are EJBs, ADF BCs) with the aim to map the database tables and views as the middle-tier level.
In this cases the pessimistic perverse effects can be amplified and that's why usually an optimistic mode is preferred.

In ADF you can choose to use one or the other at the database connection definition level on the Application Module depending on the type of application and your locking strategy.

In any case, locks issues always ends at the DBA's desk.

One of the issues is when a user tries to acquire a lock on an already locked record at the database level, the application can choose to either wait or fail.
In case the application waits for the lock without or with a long timed-out setting, the user will feel that the application has hanged . If the user kills the application and restart a session, same will happen and he will call IT department who will have to find out who is sleeping with pessimist locks on records and wake him up to release the lock.

Here is two simple queries to achieve that :

1/. Check who is locking who :

select s1.username || '@' || s1.machine
   || ' ( SID=' || s1.sid || ' )  is blocking '
   || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) '
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid   
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
 and l2.id2 = l2.id2;

2/. Check what table is locked to confirm your suspicion on the sleeping user :

select object_name,oracle_username from sys.dba_objects a,sys.v_$locked_object b
where a.object_id=b.object_id and oracle_username='The suspected user name'

Of course you must have the corresponding privileges to select from the v$xx, v_$xx & dba_xx database objects to do this.

Good lock hunt !

Tuesday, April 12, 2011

OTN Developer Day Virtual Conference - ADF and more...

In a previous post, we discussed about interesting places to network and learn ADF.

Virtual Conferences are now a very convenient place to virtually attend sessions, hands-on from your desk and in your preferred time zone.

Oracle Technology Network has scheduled a free online event on REA that all Oracle Fusion developers or Fusion  Applications developers will found interesting.
Sessions and hands-on are for all levels, from beginners to advanced developers.

You will learn how Oracle Rich Enterprise Applications can be built with Oracle ADF Faces and WebCenter.

You can check the Agenda and register on OTN at :

Book for your time zone and enjoy the event!

Tuesday, March 29, 2011

Essential places to learn ADF

For anyone embark someway in Oracle ADF, new or expert, there is training available at Oracle Education or at Oracle Partners around the world and to complement these training there are good blogs and books to help you in your ADF adventure.

This post is to point you to two other important ADF resources any ADF developer should have in their primary set of bookmarks :-

One is the ADF EMG (Enterprise Methodology Group), a google group for ADF developers made by ADF developers. Founded by Chris Muir, the group, at time of writing, has more than 500 members.
A few of them are ADF experts, some are Oracle Aces, some AceDs, and are continuously contributing to ADF via blogs, presentations at conferences etc..
The group is a place to share best practices and learn from real life cases, share opinions etc..
It is also a place to exchange ideas with Oracle ADF product team who are also members of the group.
If you are not a member yet, point your browser to ADF EMG to register.

The other resource on Oracle Technology Network (OTN) is Oracle ADF Insider.
It is a very good place to learn more on the framework after an academic training or books reading.
It contains recorded sessions for each topic and covers from basics to advanced features.
Recently its content has been extended with a section called ADF Insider Essentials which deals with common and recurring tasks that every developer has to face at one moment or the other.
Each ADF Essential topic is available in a recorded session presented by ADF experts and covering ADF BC, ADF Model, ADF Controller and ADF Faces.