Parameterized trigger - concurrency concerns

kaliantag

Hi,

The current (test) trigger is :

<span class="kwd" style="color: #00008b;">CREATE</span><span class="pln" style="color: #000000;"> </span><span class="kwd" style="color: #00008b;">OR</span><span class="pln" style="color: #000000;"> REPLACE </span><span class="kwd" style="color: #00008b;">TRIGGER</span><span class="pln" style="color: #000000;"> CHASSIS_DT_EVNT_AIUR_TRG_OLD AFTER </span><span class="kwd" style="color: #00008b;">DELETE</span><span class="pln" style="color: #000000;"> </span><span class="kwd" style="color: #00008b;">OR</span><span class="pln" style="color: #000000;"> </span><span class="kwd" style="color: #00008b;">INSERT</span><span class="pln" style="color: #000000;"> </span><span class="kwd" style="color: #00008b;">OR</span><span class="pln" style="color: #000000;"> </span><span class="kwd" style="color: #00008b;">UPDATE</span><span class="pln" style="color: #000000;"><br/></span><span class="kwd" style="color: #00008b;">OF</span><span class="pln" style="color: #000000;"> ETA<br/></span><span class="kwd" style="color: #00008b;">ON</span><span class="pln" style="color: #000000;"> CHASSITRANSPORTS<br/>REFERENCING NEW </span><span class="kwd" style="color: #00008b;">AS</span><span class="pln" style="color: #000000;"> New OLD </span><span class="kwd" style="color: #00008b;">AS</span><span class="pln" style="color: #000000;"> Old<br/></span><span class="kwd" style="color: #00008b;">FOR</span><span class="pln" style="color: #000000;"> EACH </span><span class="kwd" style="color: #00008b;">ROW</span><span class="pln" style="color: #000000;"><br/></span><span class="kwd" style="color: #00008b;">DECLARE</span><span class="pln" style="color: #000000;"><br/></span><span class="kwd" style="color: #00008b;">BEGIN</span><span class="pln" style="color: #000000;"><br/>   </span><span class="kwd" style="color: #00008b;">INSERT</span><span class="pln" style="color: #000000;"> </span><span class="kwd" style="color: #00008b;">INTO</span><span class="pln" style="color: #000000;"> TS_CHASSIS_DATE_EVENTS </span><span class="pun" style="color: #000000;">(</span><span class="pln" style="color: #000000;">CHASSISNUMBER</span><span class="pun" style="color: #000000;">,</span><span class="pln" style="color: #000000;">DATETYPE</span><span class="pun" style="color: #000000;">,</span><span class="pln" style="color: #000000;">TRANSPORTLEGSORTORDER</span><span class="pun" style="color: #000000;">,</span><span class="pln" style="color: #000000;">OLDDATE</span><span class="pun" style="color: #000000;">,</span><span class="pln" style="color: #000000;">CREATEDBY</span><span class="pun" style="color: #000000;">,</span><span class="pln" style="color: #000000;">CREATEDDATE</span><span class="pun" style="color: #000000;">,</span><span class="pln" style="color: #000000;">UPDATEDBY</span><span class="pun" style="color: #000000;">,</span><span class="pln" style="color: #000000;">UPDATEDDATE</span><span class="pun" style="color: #000000;">)</span><span class="pln" style="color: #000000;"> </span><span class="kwd" style="color: #00008b;">VALUES</span><span class="pln" style="color: #000000;"> </span><span class="pun" style="color: #000000;">(:</span><span class="pln" style="color: #000000;">old</span><span class="pun" style="color: #000000;">.</span><span class="pln" style="color: #000000;">chassino</span><span class="pun" style="color: #000000;">,</span><span class="str" style="color: #800000;">'ETA'</span><span class="pun" style="color: #000000;">,:</span><span class="pln" style="color: #000000;">old</span><span class="pun" style="color: #000000;">.</span><span class="pln" style="color: #000000;">sortorder</span><span class="pun" style="color: #000000;">,:</span><span class="pln" style="color: #000000;">old</span><span class="pun" style="color: #000000;">.</span><span class="pln" style="color: #000000;">eta</span><span class="pun" style="color: #000000;">,</span><span class="str" style="color: #800000;">'xyz'</span><span class="pun" style="color: #000000;">,</span><span class="pln" style="color: #000000;">sysdate</span><span class="pun" style="color: #000000;">,</span><span class="str" style="color: #800000;">'xyz'</span><span class="pun" style="color: #000000;">,</span><span class="pln" style="color: #000000;">sysdate</span><span class="pun" style="color: #000000;">);</span><span class="pln" style="color: #000000;"><br/><br/>  EXCEPTION<br/>   </span><span class="kwd" style="color: #00008b;">WHEN</span><span class="pln" style="color: #000000;"> OTHERS </span><span class="kwd" style="color: #00008b;">THEN</span><span class="pln" style="color: #000000;"><br/>   </span><span class="kwd" style="color: #00008b;">NULL</span><span class="pun" style="color: #000000;">;</span><span class="pln" style="color: #000000;"><br/></span><span class="kwd" style="color: #00008b;">END</span><span class="pln" style="color: #000000;"> TS_CHASSIS_DT_EVNT_AIUR_TRG</span><span class="pun" style="color: #000000;">;</span>

Now the 'CREATEDBY', 'UPDATEDBY' will be the web application users who have logged in and made the changes which caused the trigger execution, hence, these values need to be passed from the application.

The web application :

1. Is deployed in Websphere Application Server where the datasources are configured
2. As expected, is using db connection pooling

I read about Oracle Context but I'm not sure it can help in case of a web application.

My question is which approach mentioned in the doc. should I take to avoid the 'concurrency' issues i.e the updates by the app. users in multiple sessions at the application level as well the db level should not interfere with each other.

Thanks and regards.

John Stegeman

First of all, your exception handler STINKS. When anything bad happens, just ignore it.

Secondly, you can, upon checking out a connection from the pool, ensure that you call dbms_session.set_identifier to set a client identifier to the application user name, then use that in your trigger.

There would be no concurrency issues with this approach.

Jarkko Turpeinen

Hi

If you need to pass only username then DBMS_SESSION.SET_IDENTIFIER could be your alternative. See http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sessio.htm#i996935

identifier can then be retrieved inside trigger by sys_context('userenv', 'client_identifier')

See http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm

kaliantag

Hi John,

Thanks for the reply.

'upon checking out a connection from the pool, ensure that you call dbms_session.set_identifier to set a client identifier to the application user name'

I assume I need to do this only in that piece of code where the table is updated, please validate.

Yeah, the exception handling is awful but the trigger is just a test one to highlight the challenge that I'm facing.

Jarkko Turpeinen

I read about Oracle Context but I'm not sure it can help in case of a web application.
My question is which approach mentioned in the doc. should I take to avoid the 'concurrency' issues i.e the updates by the app. users in multiple sessions at the application level as well the db level should not interfere with each other.

Please see that documentation:

Setting a Global Application Context for Non database Users

"

When a nondatabase user, that is, a user who is not known to the database (such as a Web application user), starts a client session, the application server generates a client session ID. Once this ID is set on the application server, it must be passed to the database server side. You do this by using the DBMS_SESSION.SET_IDENTIFIER procedure to set the client session ID. To set the context, you set the client_id parameter in the DBMS_SESSION.SET_CONTEXT procedure, in a PL/SQL procedure on the server side. This enables you to manage the application context globally, yet each client sees only his or her assigned application context.

The client_id value is the key here to getting and setting the correct attributes for the global application context. Remember that the client identifier is controlled by the middle-tier application, and once set, it remains open until it is cleared.

A typical way to manage this type of application context is to place the session_id value (client_identifier) in a cookie, and send it to the end user's HTML page so that is returned on the next request. A lookup table in the application should also keep client identifiers so that they are prevented from being reused for other users and to implement an end-user session time out.

"

Tutorial http://docs.oracle.com/cd/B28359_01/network.111/b28531/app_context.htm#DBSEG79745

John Stegeman

You tell me.

Normally, you probably have one code path for grabbing connections - it would be pretty simple to modify this one code path to set the client identifier - then it would be set anytime and anywhere you needed it.

Unknown

'upon checking out a connection from the pool, ensure that you call dbms_session.set_identifier to set a client identifier to the application user name'

I assume I need to do this only in that piece of code where the table is updated, please validate.

No - the connection IS THE SESSION.

Set the identifier when the pool first creates the connections. Don't set it in an isolated piece of code.

kaliantag

The web application uses Hibernate which looks up the datasource configured in the Websphere Application Server.

Below is one of the codes that updates the CHASSITRANSPORTS table :

protected static Session _hib_session =  = HibernateSessionUtil.getSession();

.

.

.

oCha = (Chassis) _hib_session.get("Chassis", _chassino);

oCT.setETA(v_ETA);

.

.

.

_hib_session.update("Transport", oCT);

  _hib_session.flush();

  _hib_session.clear();

  HibernateSessionUtil.commitTransaction();

Now several web app users will have their own application sessions (which will use the connections via Hibernate) and modify the table from different places. Now how do I pass their user ids from the application sessions to the db context i.e where do I write 'dbms_session.set_identifier' or other similar DB related commands ?

kaliantag

The web application uses Hibernate which looks up the datasource configured in the Websphere Application Server.

Below is one of the codes that updates the CHASSITRANSPORTS table :

protected static Session _hib_session =  = HibernateSessionUtil.getSession();

.

.

.

oCha = (Chassis) _hib_session.get("Chassis", _chassino);

oCT.setETA(v_ETA);

.

.

.

_hib_session.update("Transport", oCT);

  _hib_session.flush();

  _hib_session.clear();

  HibernateSessionUtil.commitTransaction();

Now several web app users will have their own application sessions (which will use the connections via Hibernate) and modify the table from different places. Now how do I pass their user ids from the application sessions to the db context i.e where do I write 'dbms_session.set_identifier' or other similar DB related commands ?

kaliantag

The web application uses Hibernate which looks up the datasource configured in the Websphere Application Server.

Below is one of the codes that updates the CHASSITRANSPORTS table :

protected static Session _hib_session =  = HibernateSessionUtil.getSession();

.

.

.

oCha = (Chassis) _hib_session.get("Chassis", _chassino);

oCT.setETA(v_ETA);

.

.

.

_hib_session.update("Transport", oCT);

  _hib_session.flush();

  _hib_session.clear();

  HibernateSessionUtil.commitTransaction();

Now several web app users will have their own application sessions (which will use the connections via Hibernate) and modify the table from different places. Now how do I pass their user ids from the application sessions to the db context i.e where do I write 'dbms_session.set_identifier' or other similar DB related commands ?

John Stegeman

You probably need to ask that on the Hibernate forums...

Wait a minute, hang on....

Why use a trigger here at all? Just set the values in your Java code and off you go... no need for a trigger.