Database Session AutoCommit in PUTSQL (Nifi 1.9.1) not working

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Database Session AutoCommit in PUTSQL (Nifi 1.9.1) not working

A_Nad
Hi,

I am using Nifi 1.9.1 and in this version, PUTSQL has been introduced with
Database Session AutoCommit property which can be set to false so that we
can control the commit on the database. But even after setting Database
Session AutoCommit = 'false', data is getting auto committed on the oracle
database. Not sure what is wrong in my processor. I am preparing an insert
statement before the flowfile is fed to PUTSQL

Below are the properties of putsql -  
JDBC Connection Pool - DBCPConnectionPool(set to oracle database)
SQL Statement - No value set
Support Fragmented Transactions - false
Database Session AutoCommit - false
Transaction Timeout - No value set
Batch Size - 1
Obtain Generated Keys - false
Rollback On Failure - false

Please help as this is an important feature for us.




--
Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Database Session AutoCommit in PUTSQL (Nifi 1.9.1) not working

Denes Arvay
Hi,

Setting the Database Session AutoCommit property to false turns off the
JDBC Connection's autocommit feature by calling
connection.setAutoCommit(false). But regardless of this value PutSQL will
commit the transaction after batchsize number of FlowFiles were processed.
This means that setting the batch size to 1 results in a similar behavior
as the autocommit = true setting.

You can verify whether the autocommit property works in your environment by
increasing the batch size and checking the query logs to see how frequently
COMMIT is issued.

Regards,
Denes

On Tue, Feb 26, 2019 at 4:37 PM A_Nad <[hidden email]> wrote:

> Hi,
>
> I am using Nifi 1.9.1 and in this version, PUTSQL has been introduced with
> Database Session AutoCommit property which can be set to false so that we
> can control the commit on the database. But even after setting Database
> Session AutoCommit = 'false', data is getting auto committed on the oracle
> database. Not sure what is wrong in my processor. I am preparing an insert
> statement before the flowfile is fed to PUTSQL
>
> Below are the properties of putsql -
> JDBC Connection Pool - DBCPConnectionPool(set to oracle database)
> SQL Statement - No value set
> Support Fragmented Transactions - false
> Database Session AutoCommit - false
> Transaction Timeout - No value set
> Batch Size - 1
> Obtain Generated Keys - false
> Rollback On Failure - false
>
> Please help as this is an important feature for us.
>
>
>
>
> --
> Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/
>
Reply | Threaded
Open this post in threaded view
|

Re: Database Session AutoCommit in PUTSQL (Nifi 1.9.1) not working

A_Nad
Hi Denes,

Thanks for your inputs. Yes, I observed what you are mentioning. Flowfiles
get committed to oracle database if Batch size is set to 1 irrespective of
the Autocommit property being false.

*Use case scenario* - I have a parent table(incremental parent_id) and a
child table(parent_id referencing the parent table) on oracle database. My
use case is that I need to load data into both parent and child table
together so that when i run the below validation query, I always get the
ora_rowscn value to match for both parent and child tables.

I verified this by loading 2 test tables using PUTSQL processor. I created
two source test tables(id,name) and inserted same 10 records into both the
source tables and made them load into another set of 2 target test
tables(id,name). To load the 2 target tables together in one PUTSQL, i
introduced the funnel just before PUTSQL so that I can use one PUTSQL with
batchsize of 2 and Database Autocommit property set as 'false'.
My flow layout is
*Pipeline1*:-GenerateTableFetch(table t1,partition size 10)
->ReplaceText(creates insert statement)->UpdateAttribute(adds 'priority'
attribute as 1 to the flowfile)
                                                                                                           
v
                                                                                                           
v
                                                                       
*Funnel*(PriorityAttributePrioritizer) -->PutSQL
                                                                                                           
^
                                                                                                           
^
*Pipeline2*:-GenerateTableFetch(table t2,partition size 10)
->ReplaceText(creates insert statement) ->UpdateAttribute(Added 'priority'
attribute as 2 to the flowfile)

I tested this flow multiple times.
*Result *- Data gets loaded into both target1 an target2 tables every time.
However, when I run the below query with ora_rowscn to check the commit
boundary on the 2 tables

/select a.idd,b.idd,a.ora_rowscn,b.ora_rowscn,case when a.ora_rowscn =
b.ora_rowscn then 'matches' else 'doomed' end as flg
from test_table_tgt a, test_table_tgt1 b
where a.idd = b.idd(+)/

I find that out 10 times that i run the dataflow, 6 times the ora_rowscn in
both the target table matches, but rest it does not match.

Ideal result should have been that all 10 times the ora_rowscn values in
both the tables should have matched. But this is not the case. Does this
mean that there is a bug in PUTSQL and that even after setting the database
autocommit property to false, data does not commit as expected? Please let
me know if you see some thing odd in my test and how can i achieve my use
case scenario

Thanks,
A_Nad




--
Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/