Other RDBMS Modules and holding the transaction between different components/submodules interaction

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

Other RDBMS Modules and holding the transaction between different components/submodules interaction

lcestari
Hi Guys!

I would like to discuss in this thread a draft concept that came to my mind so we could refine it. It is about the remaining RDBMS modules to integrate with the Lightblue, specially with some concerns with transaction.

I will put  the most important bits  so this first message doesnt become an extensive unclear text.

I think the next steps in RDBMS modules would be the Hystrix and CRUD submodules. We can do that in several different ways. The one that Naveen and I was talking during a meeting was to create very specific class in the Hystrix submodule that will handle the external interface (in this case the RDBMS), leaving to the CRUD submodule to make the necessary interpretation/interpolation of the saved RDBMS JSON and the entity some  of data. To make it clear, a simple scenario is the request coming to the CRUD and while it is processing it, for each statement that it reach, it will call a Hystrix command.

One thing that I was a bit worried it is the transaction. As we have to deal with scenarios where we can interrupt and rollback the processing, if the Hystrix commands aren't in the same transaction we can't fulfill that requirement. Naveen gave a good suggestion that we could just pass the connection between the classes from  CRUD submodule to Hystrix submodule. But the problem with the transaction might happen even in this scenario, for example if the JDBC implementation driver relies on the usage of ThreadLocal (a java class which hold a different value per Thread) and we change the Hystrix to use their own thread pool, we might get some problems.

I know that we aren't aiming to use Hystrix thread pool, but maybe in the future that can change.

One another possible scenario that comes to my mind is to move all the business logic of processing to another module, so each Hystrix command would just call this another class (from a different module just to make Hystrix commands as simple as possible) .

What do you think guys? Any suggestion/comment around the modules or the transaction problem?

Reply | Threaded
Open this post in threaded view
|

Re: Other RDBMS Modules and holding the transaction between different components/submodules interaction

bserdar


On Jul 25, 2014 8:15 PM, "lcestari [via lightblue-dev]" <[hidden email]> wrote:
>
> Hi Guys!
>
> I would like to discuss in this thread a draft concept that came to my mind so we could refine it. It is about the remaining RDBMS modules to integrate with the Lightblue, specially with some concerns with transaction.
>
> I will put  the most important bits  so this first message doesnt become an extensive unclear text.
>
> I think the next steps in RDBMS modules would be the Hystrix and CRUD submodules. We can do that in several different ways. The one that Naveen and I was talking during a meeting was to create very specific class in the Hystrix submodule that will handle the external interface (in this case the RDBMS), leaving to the CRUD submodule to make the necessary interpretation/interpolation of the saved RDBMS JSON and the entity some  of data. To make it clear, a simple scenario is the request coming to the CRUD and while it is processing it, for each statement that it reach, it will call a Hystrix command.
>
> One thing that I was a bit worried it is the transaction. As we have to deal with scenarios where we can interrupt and rollback the processing, if the Hystrix commands aren't in the same transaction we can't fulfill that requirement. Naveen gave a good suggestion that we could just pass the connection between the classes from  CRUD submodule to Hystrix submodule. But the problem with the transaction might happen even in this scenario, for example if the JDBC implementation driver relies on the usage of ThreadLocal (a java class which hold a different value per Thread) and we change the Hystrix to use their own thread pool, we might get some problems.

We are using hystrix thread pool for mongo. We will probably use it for rdbms as well.

One option is to write hystrix commands for one tx instead of every jdbc stmt. Adding tx management hooks to mediator is already in backlog so it can be implemented fairly easily.

>
> I know that we aren't aiming to use Hystrix thread pool, but maybe in the future that can change.
>
> One another possible scenario that comes to my mind is to move all the business logic of processing to another module, so each Hystrix command would just call this another class (from a different module just to make Hystrix commands as simple as possible) .
>
> What do you think guys? Any suggestion/comment around the modules or the transaction problem?
>
>
>
> ________________________________
> If you reply to this email, your message will be added to the discussion below:
> http://lightblue-dev.1011138.n3.nabble.com/Other-RDBMS-Modules-and-holding-the-transaction-between-different-components-submodules-interaction-tp6.html
> To start a new topic under lightblue-dev, email [hidden email]
> To unsubscribe from lightblue-dev, click here.
> NAML

Reply | Threaded
Open this post in threaded view
|

Re: Other RDBMS Modules and holding the transaction between different components/submodules interaction

jewzaam
Administrator

I think use of thread pools refers to the async command execution. Not isolation configuration. If we hanged to this for rdbms results become non-deterministic. We can't have that, especially because of the chaining of sql statements to make it all work. So I don't see the async command execution being something to worry about.

On Jul 25, 2014 1:47 PM, "bserdar [via lightblue-dev]" <[hidden email]> wrote:


On Jul 25, 2014 8:15 PM, "lcestari [via lightblue-dev]" <[hidden email]> wrote:
>
> Hi Guys!
>
> I would like to discuss in this thread a draft concept that came to my mind so we could refine it. It is about the remaining RDBMS modules to integrate with the Lightblue, specially with some concerns with transaction.
>
> I will put  the most important bits  so this first message doesnt become an extensive unclear text.
>
> I think the next steps in RDBMS modules would be the Hystrix and CRUD submodules. We can do that in several different ways. The one that Naveen and I was talking during a meeting was to create very specific class in the Hystrix submodule that will handle the external interface (in this case the RDBMS), leaving to the CRUD submodule to make the necessary interpretation/interpolation of the saved RDBMS JSON and the entity some  of data. To make it clear, a simple scenario is the request coming to the CRUD and while it is processing it, for each statement that it reach, it will call a Hystrix command.
>
> One thing that I was a bit worried it is the transaction. As we have to deal with scenarios where we can interrupt and rollback the processing, if the Hystrix commands aren't in the same transaction we can't fulfill that requirement. Naveen gave a good suggestion that we could just pass the connection between the classes from  CRUD submodule to Hystrix submodule. But the problem with the transaction might happen even in this scenario, for example if the JDBC implementation driver relies on the usage of ThreadLocal (a java class which hold a different value per Thread) and we change the Hystrix to use their own thread pool, we might get some problems.

We are using hystrix thread pool for mongo. We will probably use it for rdbms as well.

One option is to write hystrix commands for one tx instead of every jdbc stmt. Adding tx management hooks to mediator is already in backlog so it can be implemented fairly easily.

>
> I know that we aren't aiming to use Hystrix thread pool, but maybe in the future that can change.
>
> One another possible scenario that comes to my mind is to move all the business logic of processing to another module, so each Hystrix command would just call this another class (from a different module just to make Hystrix commands as simple as possible) .
>
> What do you think guys? Any suggestion/comment around the modules or the transaction problem?
>
>
>
> ________________________________
> If you reply to this email, your message will be added to the discussion below:
> http://lightblue-dev.1011138.n3.nabble.com/Other-RDBMS-Modules-and-holding-the-transaction-between-different-components-submodules-interaction-tp6.html
> To start a new topic under lightblue-dev, email [hidden email]
> To unsubscribe from lightblue-dev, click here.
> NAML




To start a new topic under lightblue-dev, email [hidden email]
To unsubscribe from lightblue-dev, click here.
NAML
Reply | Threaded
Open this post in threaded view
|

Re: Other RDBMS Modules and holding the transaction between different components/submodules interaction

lcestari
Sorry Burak, I didn't get how exactly would look like that suggestion to write hystrix commands for one tx, would that be just a call to an interface that would hold a reference to the whole set of business rules around the RDBMS JSON (conditionals, variables and statements) to be processed during the runtime (as some conditionals are very dynamic)?

Naveen, I was thinking more about if we changed any configuration around hystrix, but that is a good scenario too. So the async command would be called in above layer (before reaching the RDBMS stuff), so for RDBMS it would be the same thread all the time (which was started in the async call, not from the request), right?
Reply | Threaded
Open this post in threaded view
|

Re: Other RDBMS Modules and holding the transaction between different components/submodules interaction

jewzaam
Administrator

Each command can be executed asynchronously. So yes it could be at higher layer, rest as an example, but it could also be for each rdbms command. I assume there will potentially be many such commands called on one request.

On Jul 28, 2014 8:23 AM, "lcestari [via lightblue-dev]" <[hidden email]> wrote:
Sorry Burak, I didn't get how exactly would look like that suggestion to write hystrix commands for one tx, would that be just a call to an interface that would hold a reference to the whole set of business rules around the RDBMS JSON (conditionals, variables and statements) to be processed during the runtime (as some conditionals are very dynamic)?

Naveen, I was thinking more about if we changd any configuration around hystrix, but that is a good scenario too. So the async command would be called in above layer (before reaching the RDBMS stuff), so for RDBMS it would be the same thread all the time (which was started in the async call, not from the request), right?


To start a new topic under lightblue-dev, email [hidden email]
To unsubscribe from lightblue-dev, click here.
NAML
Reply | Threaded
Open this post in threaded view
|

Re: Other RDBMS Modules and holding the transaction between different components/submodules interaction

lcestari
OK, no problem about I think. I´d like to get back to the implementation details, so you guys don't see any problem sharing the connection between the command and the calling class and I can implement each command to run a simple statement (like query/update/call procedure/ etc) , right?
Reply | Threaded
Open this post in threaded view
|

Re: Other RDBMS Modules and holding the transaction between different components/submodules interaction

lcestari
I would just mention some details that I'm facing during the implementation.

I focused in the point which I thought would be more complex which is the call of a procedure. I saw the CallableStatement would have to deal with almost all the same issues as PreparedStatement and other ones. Both will need some try/catch verification for wrong input (like when the SQL statement has parameters but none was informed by the user) but this is just one thing that I found.

In the CallableStatement case, there will be fewer information available that it can need, I mean, in any other SQL statement we can clearly see the input and output data, but in during the call of a procedure with JDBC we will just have the procedure name and its parameters, which can be IN OR OUT or INOUT (but INOUTcan just assume it will be mapped in both IN and OUT fields are informed). The main issue here is that each parameter will have a specific type and some of the fields can be Cursor( in the Cursor in procedure,  in Oracle database it is pretty common to map that as a parameter as well but the JDBC standards only guarantee using getResultSet and gerMoreResult.

One way that I thought to solve the cursor problem is adding more optional fields for the binding (like parameterNumber, sqlType, isForProcedureStmt (this would warn to try to extract this field during an procedure processing, repeated matches would resold to get only the last value), isCursor(this last one would guarantee to remove from the statement the variable as required by JDBC) ) so we could better handle the procedure processing (we could make it even more strict and enhance the logic over isForProcedureStmt to something different, so it could just match to a single resultset from a specific Procedure).

Another possibility is the bruteforce, which could have undesired consequence like the comment I did for isForProcedureStmt (will have only the last value defined, even by mistake) and worst (some conflict between a binding field and the name returned of a a column from a Query statement in the cursor of a Procedure, but that field was expecting to be used else where (maybe in the next SQL statement.

Anyway, I think we  could try to put all the details about this complex mechanism in the documentation (and also special notes if some undesired effect can happen).

Any other suggestion?

Some relevant links (I saw some other, specially from the database vendors, but these give a better summary):
http://docs.oracle.com/javase/7/docs/api/java/sql/CallableStatement.html
http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#
http://sourceforge.net/p/jtds/discussion/104389/thread/bf59390e/
http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-cursor-example/
http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-out-parameter-example/
http://www.mkyong.com/oracle/oracle-stored-procedures-hello-world-examples/
http://www.onjava.com/pub/a/onjava/excerpt/javaentnut_2/index2.html
http://www.xyzws.com/Javafaq/how-to-retrieve-multiple-result-sets-from-a-stored-procedure-in-jdbc/172
https://stackoverflow.com/questions/19210871/replacing-oracles-oracletypes-cursor-to-an-equivalent-in-mysql-when-using-reg/19214988#19214988
http://javahash.com/invoking-strored-procedures-using-jdbc-callabale-statement/
http://www.tutorialspoint.com/jdbc/jdbc-stored-procedure.htm
http://www.herongyang.com/JDBC/MySQL-CallableStatement-Multiple-ResulSet.html
http://www.enterprisedt.com/publications/oracle/result_set.html
http://msdn.microsoft.com/en-us/library/ms188655.aspx
Reply | Threaded
Open this post in threaded view
|

Re: Other RDBMS Modules and holding the transaction between different components/submodules interaction

lcestari
I forgot to mention that I tried to see some similar solutions around the RDBMS and this problem with procedure and I saw that Netflix Staash (more info https://github.com/Netflix/staash/wiki  https://github.com/Netflix/staash  https://github.com/Netflix/staash/blob/master/staash-svc/src/main/java/com/netflix/staash/connection/MySqlConnection.java ) doesnt have procedure in their implementation
Reply | Threaded
Open this post in threaded view
|

Re: Other RDBMS Modules and holding the transaction between different components/submodules interaction

jewzaam
Administrator
In reply to this post by lcestari

I would recommend not implementing this immediately, focus on other commands first. From our use case point of view and planned usage of rdbms controller the statement support is not required. Not that we won't use it later, but for now there are no plans.

On Jul 30, 2014 2:12 PM, "lcestari [via lightblue-dev]" <[hidden email]> wrote:
I would just mention some details that I'm facing during the implementation.

I focused in the point which I thought would be more complex which is the call of a procedure. I saw the CallableStatement would have to deal with almost all the same issues as PreparedStatement and other ones. Both will need some try/catch verification for wrong input (like when the SQL statement has parameters but none was informed by the user) but this is just one thing that I found.

In the CallableStatement case, there will be fewer information available that it can need, I mean, in any other SQL statement we can clearly see the input and output data, but in during the call of a procedure with JDBC we will just have the procedure name and its parameters, which can be IN OR OUT or INOUT (but INOUTcan just assume it will be mapped in both IN and OUT fields are informed). The main issue here is that each parameter will have a specific type and some of the fields can be Cursor( in the Cursor in procedure,  in Oracle database it is pretty common to map that as a parameter as well but the JDBC standards only guarantee using getResultSet and gerMoreResult.

One way that I thought to solve the cursor problem is adding more optional fields for the binding (like parameterNumber, sqlType, isForProcedureStmt (this would warn to try to extract this field during an procedure processing, repeated matches would resold to get only the last value), isCursor(this last one would guarantee to remove from the statement the variable as required by JDBC) ) so we could better handle the procedure processing (we could make it even more strict and enhance the logic over isForProcedureStmt to something different, so it could just match to a single resultset from a specific Procedure).

Another possibility is the bruteforce, which could have undesired consequence like the comment I did for isForProcedureStmt (will have only the last value defined, even by mistake) and worst (some conflict between a binding field and the name returned of a a column from a Query statement in the cursor of a Procedure, but that field was expecting to be used else where (maybe in the next SQL statement.

Anyway, I think we  could try to put all the details about this complex mechanism in the documentation (and also special notes if some undesired effect can happen).

Any other suggestion?

Some relevant links (I saw some other, specially from the database vendors, but these give a better summary):
http://docs.oracle.com/javase/7/docs/api/java/sql/CallableStatement.html
http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#
http://sourceforge.net/p/jtds/discussion/104389/thread/bf59390e/
http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-cursor-example/
http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-out-parameter-example/
http://www.mkyong.com/oracle/oracle-stored-procedures-hello-world-examples/
http://www.onjava.com/pub/a/onjava/excerpt/javaentnut_2/index2.html
http://www.xyzws.com/Javafaq/how-to-retrieve-multiple-result-sets-from-a-stored-procedure-in-jdbc/172
https://stackoverflow.com/questions/19210871/replacing-oracles-oracletypes-cursor-to-an-equivalent-in-mysql-when-using-reg/19214988#19214988
http://javahash.com/invoking-strored-procedures-using-jdbc-callabale-statement/
http://www.tutorialspoint.com/jdbc/jdbc-stored-procedure.htm
http://www.herongyang.com/JDBC/MySQL-CallableStatement-Multiple-ResulSet.html
http://www.enterprisedt.com/publications/oracle/result_set.html
http://msdn.microsoft.com/en-us/library/ms188655.aspx


To start a new topic under lightblue-dev, email [hidden email]
To unsubscribe from lightblue-dev, click here.
NAML
Reply | Threaded
Open this post in threaded view
|

Re: Other RDBMS Modules and holding the transaction between different components/submodules interaction

lcestari
I would like just to mention that the development is in progress, you can see the milestone https://github.com/lightblue-platform/lightblue/milestones/RDBMS%20Controller  ( or using  https://waffle.io/lightblue-platform/lightblue ) , specially this issue https://github.com/lightblue-platform/lightblue/issues/91