Write-behind and foreign keys in DB

[vetko <At...@wolterskluwer.com>]

Hi All,

I am experiencing an issue while trying to use write-behind on caches
connected to tables which have foreign key constraints between them.
Seemingly the write-behind mechanism is not executing the updates/inserts in
a deterministic order, but rather is trying to push all the collected
changes per each cache consecutively in some unknown order. But as we have
foreign keys in the tables, the order of the operation matters, so parent
objects should be inserted/updated first, and children only after that
(otherwise foreign key violations are thrown from the DB).

It seems that the current implementation is trying to workaround this
problem on a trial-and-error basis
(org.apache.ignite.cache.store.jdbc.CacheAbstractJdbcStore:888), which means
that it will periodically retry to flush the changes again and again for the
caches in case of which a constraint violation occured. So the "child" cache
will periodically retry to flush, until the "parent" cache gets flushed
first. This ultimately will result in getting the data into the DB, but it
also means a lot of unsuccessful tries in case of complex hierarchical
tables, until the correct order is "found". This results in poor performance
and unnecessary shelling of the DB.

Do you have any suggestions how could I circumvent this issue?

(Initially I were trying with write-through, but it resulted in VERY poor
performance, because the CacheAbstractJdbcStore is seemingly opening a new
prepared statement for each insert/update operation.)

(Using Ignite 1.4)



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Write-behind-and-foreign-keys-in-DB-tp4415.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Write-behind and foreign keys in DB

[Alexei Scherbakov <al...@gmail.com>]

Hi,

You should not use write-behind mode in such scenario.
Concerning performance issues: are you using connection pooling library?
If no, I recommend you to use a library like c3p0 for connection pooling
and prepared statement caching.



2016-04-21 18:02 GMT+03:00 vetko <At...@wolterskluwer.com>:

> Hi All,
>
> I am experiencing an issue while trying to use write-behind on caches
> connected to tables which have foreign key constraints between them.
> Seemingly the write-behind mechanism is not executing the updates/inserts
> in
> a deterministic order, but rather is trying to push all the collected
> changes per each cache consecutively in some unknown order. But as we have
> foreign keys in the tables, the order of the operation matters, so parent
> objects should be inserted/updated first, and children only after that
> (otherwise foreign key violations are thrown from the DB).
>
> It seems that the current implementation is trying to workaround this
> problem on a trial-and-error basis
> (org.apache.ignite.cache.store.jdbc.CacheAbstractJdbcStore:888), which
> means
> that it will periodically retry to flush the changes again and again for
> the
> caches in case of which a constraint violation occured. So the "child"
> cache
> will periodically retry to flush, until the "parent" cache gets flushed
> first. This ultimately will result in getting the data into the DB, but it
> also means a lot of unsuccessful tries in case of complex hierarchical
> tables, until the correct order is "found". This results in poor
> performance
> and unnecessary shelling of the DB.
>
> Do you have any suggestions how could I circumvent this issue?
>
> (Initially I were trying with write-through, but it resulted in VERY poor
> performance, because the CacheAbstractJdbcStore is seemingly opening a new
> prepared statement for each insert/update operation.)
>
> (Using Ignite 1.4)
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/Write-behind-and-foreign-keys-in-DB-tp4415.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>



-- 

Best regards,
Alexei Scherbakov