[teiid-issues] [JBoss JIRA] (TEIID-3272) JDBC Translator | Segregation of batch execution and commit mode

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-3272?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13029897#comment-13029897 ] 

Steven Hawkins edited comment on TEIID-3272 at 1/5/15 9:53 AM:
---------------------------------------------------------------

> 1. If I have millions of rows being updated, how could I send such a huge update request in one one batch command, expecting executeTranslatedCommand to break it down for me. Since there would be a limit on what we can hold in memory, we would be restricted on this front.

If the update were that large, then the only current mechanism to break it up would be a global transaction.  The current paradigm for an execution makes it quite difficult for this to be done with just a source local transaction.

> 2. Secondly, since we are setting the autocommit property for the connection to false, wouldnt it be better, if the control for Commit , is exposed to the caller. In this case, if we have the control over the "autocommit" property for the connection, we could send the batches as we desire and yet control the commit as per requirement.

>From the caller's connection perspective they are in one of:

a global txn (auto commit is implied to be false)
auto commit = false
auto commit = true 

In the first two cases Teiid will coordinate the client work under a global transaction - in which case the translator level auto commit behavior does not apply.
Only in auto commit = true does Teiid have more room to determine the transaction handling.  At a high level we'll first consult the autoCommitTxn property if it is true we'll start a global transaction.  If it is auto, then we'll inspect the plan to see if a transaction appears to be needed to coordinate the work.  If no transaction is started, then we'll get to the translator layer where we can choose how to handle the source connection auto commit.  The logic assumes then the logical operation should be atomic and will start/commit a local transaction as needed.  


was (Author: shawkins):
> 1. If I have millions of rows being updated, how could I send such a huge update request in one one batch command, expecting executeTranslatedCommand to break it down for me. Since there would be a limit on what we can hold in memory, we would be restricted on this front.

If the update were that large, then the only current mechanism to break it up would be a global transaction.  The current paradigm for an execution makes it quite difficult for this to be done with just a local transaction.

> 2. Secondly, since we are setting the autocommit property for the connection to false, wouldnt it be better, if the control for Commit , is exposed to the caller. In this case, if we have the control over the "autocommit" property for the connection, we could send the batches as we desire and yet control the commit as per requirement.

>From the caller's connection perspective they are in one of:

a global txn (auto commit is implied to be false)
auto commit = false
auto commit = true 

In the first two cases Teiid will coordinate the client work under a global transaction - in which case the translator level auto commit behavior does not apply.
Only in auto commit = true does Teiid have more room to determine the transaction handling.  At a high level we'll first consult the autoCommitTxn property if it is true we'll start a global transaction.  If it is auto, then we'll inspect the plan to see if a transaction appears to be needed to coordinate the work.  If no transaction is started, then we'll get to the translator layer where we can choose how to handle the source connection auto commit.  The logic assumes then the logical operation should be atomic and will start/commit a local transaction as needed.  

> JDBC Translator | Segregation of batch execution and commit mode 
> -----------------------------------------------------------------
>
>                 Key: TEIID-3272
>                 URL: https://issues.jboss.org/browse/TEIID-3272
>             Project: Teiid
>          Issue Type: Enhancement
>          Components: JDBC Connector
>            Reporter: Shiveeta Mattoo
>            Assignee: Steven Hawkins
>              Labels: Batch-Commit, JDBC-Transator
>         Attachments: batch1.png
>
>
> In JDBC Translator, the batch execution and commit are coupled together. Hence, we after every batch execution, a commit is executed. 
> This enhancement is for providing an ability to support the commit after a configurable 'x' no. of batches has completed, instead of an automatic commit after every batch.
> The no of batches after which a commit is desired should be a configurable option.
>  



--
This message was sent by Atlassian JIRA
(v6.3.11#6341)