Re: [Dev] [DEV] Problem regarding Transaction Handling in MySQL
Hi all,
The problem was solved by changing the database engine type to innoDB.
Previously it was set to MyISAM which does not provide transactional
support.
Thank you
On Wed, May 27, 2015 at 10:20 AM, Sasikala Kottegoda
wrote:
> Hi all,
>
> MB stores message metadata and content in two separate tables.
>
> Currently, message metadata is being deleted once the messages are
> delivered, but the content is not (deleted later by a scheduled task).
>
> What I'm trying to do is to delete (transactional delete) both message
> metadata and content once they get delivered. For this, I have created two
> prepared statements. I add messages to these two statements and execute the
> batches as shown below.
>
> for (AndesRemovableMetadata md : messagesToRemove) {
> //add parameters to delete metadata
> metadataRemovalPreparedStatement.setInt(1, queueID);
> metadataRemovalPreparedStatement.setLong(2, md.getMessageID());
> metadataRemovalPreparedStatement.addBatch();
>
> //add parameters to delete content
> contentRemovalPreparedStatment.setLong(1, md.getMessageID());
> contentRemovalPreparedStatment.addBatch();
> }
> metadataRemovalPreparedStatement.executeBatch();
> contentRemovalPreparedStatment.executeBatch();
> connection.commit();
>
>
> This works fine in a normal scenario. But when I kill the MB node (which
> acts as the SQL client) suddenly, it seems like the execution of the second
> prepared statement is not completed. Meaning that, even though the metadata
> is removed from the respective table, the content is not. When I
> interchange the statement executions, metadata gets left in the metadata
> table and content gets deleted.
>
> Could I please know what I'm doing wrong?
>
> Also, another solution to this would be to add a foreign key constraint to
> the content table to enable cascade delete. Would that be a better approach?
>
> Thank you
> --
> Sasikala Kottegoda
> *Software Engineer*
> WSO2 Inc., http://wso2.com/
> lean. enterprise. middleware
> Mobile: +94 774835928/712792401
>
--
Sasikala Kottegoda
*Software Engineer*
WSO2 Inc., http://wso2.com/
lean. enterprise. middleware
Mobile: +94 774835928/712792401
___
Dev mailing list
Dev@wso2.org
http://wso2.org/cgi-bin/mailman/listinfo/dev
Re: [Dev] [DEV] Problem regarding Transaction Handling in MySQL
Yes I had set the connection autoCommit value to false.
Below is the complete code snippet:
Connection connection = null;
PreparedStatement preparedStatement = null;
PreparedStatement contentRemovalPreparedStatment = null;
Context context =
DataAccessMatrixManager.addAndGetTimer(MatrixConstants.DELETE_MESSAGE_META_DATA_FROM_QUEUE,
this).
start();
try {
int queueID = getCachedQueueID(storageQueueName);
connection = getConnection();
connection.setAutoCommit(false);
preparedStatement = connection
.prepareStatement(RDBMSConstants.PS_DELETE_METADATA_FROM_QUEUE);
contentRemovalPreparedStatment =
connection.prepareStatement(RDBMSConstants.PS_DELETE_MESSAGE_PARTS);
for (AndesRemovableMetadata md : messagesToRemove) {
//add parameter to delete metadata
preparedStatement.setInt(1, queueID);
preparedStatement.setLong(2, md.getMessageID());
preparedStatement.addBatch();
//add parameters to delete content
contentRemovalPreparedStatment.setLong(1, md.getMessageID());
contentRemovalPreparedStatment.addBatch();
}
preparedStatement.executeBatch();
contentRemovalPreparedStatment.executeBatch();
connection.commit();
//TODO find a better phrase
if (log.isDebugEnabled()) {
log.debug("Metadata and content removed. " + messagesToRemove.size() +
" metadata from destination " + storageQueueName);
}
} catch (SQLNonTransientConnectionException sqlConEx) {
rollback(connection,
RDBMSConstants.TASK_DELETING_METADATA_FROM_QUEUE + storageQueueName
+ " and " + RDBMSConstants.TASK_DELETING_MESSAGE_PARTS);
throw new AndesStoreUnavailableException("error occurred while
deleting message metadata " +
"and content for queue " + storageQueueName,
sqlConEx.getSQLState(), sqlConEx);
} catch (SQLException e) {
rollback(connection,
RDBMSConstants.TASK_DELETING_METADATA_FROM_QUEUE + storageQueueName
+ " and " + RDBMSConstants.TASK_DELETING_MESSAGE_PARTS);
throw new AndesException("error occurred while deleting message
metadata and content for queue ",
e);
} finally {
context.stop();
String task = RDBMSConstants.TASK_DELETING_METADATA_FROM_QUEUE +
storageQueueName;
String messageContentRemovalTask =
RDBMSConstants.TASK_DELETING_MESSAGE_PARTS;
close(preparedStatement, task);
close(contentRemovalPreparedStatment, messageContentRemovalTask);
close(connection, task + " and " + messageContentRemovalTask);
}
Thanks
On Wed, May 27, 2015 at 1:12 PM, Harsha Kumara wrote:
> Hi Sasikala,
>
> Can you share your full code snippet. I hope you have initially set
> connection autoCommit value to false. As Lahiru mentioned, it would be best
> to have foreign key constraint with cascade delete.
>
> Thanks,
> Harsha
>
>
>
> On Wed, May 27, 2015 at 10:20 AM, Sasikala Kottegoda
> wrote:
>
>> Hi all,
>>
>> MB stores message metadata and content in two separate tables.
>>
>> Currently, message metadata is being deleted once the messages are
>> delivered, but the content is not (deleted later by a scheduled task).
>>
>> What I'm trying to do is to delete (transactional delete) both message
>> metadata and content once they get delivered. For this, I have created two
>> prepared statements. I add messages to these two statements and execute the
>> batches as shown below.
>>
>> for (AndesRemovableMetadata md : messagesToRemove) {
>> //add parameters to delete metadata
>> metadataRemovalPreparedStatement.setInt(1, queueID);
>> metadataRemovalPreparedStatement.setLong(2, md.getMessageID());
>> metadataRemovalPreparedStatement.addBatch();
>>
>> //add parameters to delete content
>> contentRemovalPreparedStatment.setLong(1, md.getMessageID());
>> contentRemovalPreparedStatment.addBatch();
>> }
>> metadataRemovalPreparedStatement.executeBatch();
>> contentRemovalPreparedStatment.executeBatch();
>> connection.commit();
>>
>>
>> This works fine in a normal scenario. But when I kill the MB node (which
>> acts as the SQL client) suddenly, it seems like the execution of the second
>> prepared statement is not completed. Meaning that, even though the metadata
>> is removed from the respective table, the content is not. When I
>> interchange the statement executions, metadata gets left in the metadata
>> table and content gets deleted.
>>
>> Could I please know what I'm doing wrong?
>>
>> Also, another solution to this would be to add a foreign key constraint
>> to the content table to enable cascade delete. Would that be a better
>> approach?
>>
>> Thank you
>> --
>> Sasikala Kottegoda
>> *Software Engineer*
>> WSO2 Inc., http://wso2.com/
>> lean. enterprise. middleware
>> Mobile: +94 774835928/712792401
>>
>> ___
>> Dev mailing list
>> Dev@wso2.org
>> http://wso2.org/cgi-bin/mailman/listinfo/dev
>>
>>
>
>
> --
> Harsha Kumara
> Software Engineer, WSO2 I
Re: [Dev] [DEV] Problem regarding Transaction Handling in MySQL
Hi Sasikala,
Can you share your full code snippet. I hope you have initially set
connection autoCommit value to false. As Lahiru mentioned, it would be best
to have foreign key constraint with cascade delete.
Thanks,
Harsha
On Wed, May 27, 2015 at 10:20 AM, Sasikala Kottegoda
wrote:
> Hi all,
>
> MB stores message metadata and content in two separate tables.
>
> Currently, message metadata is being deleted once the messages are
> delivered, but the content is not (deleted later by a scheduled task).
>
> What I'm trying to do is to delete (transactional delete) both message
> metadata and content once they get delivered. For this, I have created two
> prepared statements. I add messages to these two statements and execute the
> batches as shown below.
>
> for (AndesRemovableMetadata md : messagesToRemove) {
> //add parameters to delete metadata
> metadataRemovalPreparedStatement.setInt(1, queueID);
> metadataRemovalPreparedStatement.setLong(2, md.getMessageID());
> metadataRemovalPreparedStatement.addBatch();
>
> //add parameters to delete content
> contentRemovalPreparedStatment.setLong(1, md.getMessageID());
> contentRemovalPreparedStatment.addBatch();
> }
> metadataRemovalPreparedStatement.executeBatch();
> contentRemovalPreparedStatment.executeBatch();
> connection.commit();
>
>
> This works fine in a normal scenario. But when I kill the MB node (which
> acts as the SQL client) suddenly, it seems like the execution of the second
> prepared statement is not completed. Meaning that, even though the metadata
> is removed from the respective table, the content is not. When I
> interchange the statement executions, metadata gets left in the metadata
> table and content gets deleted.
>
> Could I please know what I'm doing wrong?
>
> Also, another solution to this would be to add a foreign key constraint to
> the content table to enable cascade delete. Would that be a better approach?
>
> Thank you
> --
> Sasikala Kottegoda
> *Software Engineer*
> WSO2 Inc., http://wso2.com/
> lean. enterprise. middleware
> Mobile: +94 774835928/712792401
>
> ___
> Dev mailing list
> Dev@wso2.org
> http://wso2.org/cgi-bin/mailman/listinfo/dev
>
>
--
Harsha Kumara
Software Engineer, WSO2 Inc.
Mobile: +94775505618
Blog:harshcreationz.blogspot.com
___
Dev mailing list
Dev@wso2.org
http://wso2.org/cgi-bin/mailman/listinfo/dev
Re: [Dev] [DEV] Problem regarding Transaction Handling in MySQL
I believe the better approach is to enforce referential integrity (with
cascade on delete) and delete the master record so it will automatically
delete the child records.
On Wed, May 27, 2015 at 10:20 AM, Sasikala Kottegoda
wrote:
> Hi all,
>
> MB stores message metadata and content in two separate tables.
>
> Currently, message metadata is being deleted once the messages are
> delivered, but the content is not (deleted later by a scheduled task).
>
> What I'm trying to do is to delete (transactional delete) both message
> metadata and content once they get delivered. For this, I have created two
> prepared statements. I add messages to these two statements and execute the
> batches as shown below.
>
> for (AndesRemovableMetadata md : messagesToRemove) {
> //add parameters to delete metadata
> metadataRemovalPreparedStatement.setInt(1, queueID);
> metadataRemovalPreparedStatement.setLong(2, md.getMessageID());
> metadataRemovalPreparedStatement.addBatch();
>
> //add parameters to delete content
> contentRemovalPreparedStatment.setLong(1, md.getMessageID());
> contentRemovalPreparedStatment.addBatch();
> }
> metadataRemovalPreparedStatement.executeBatch();
> contentRemovalPreparedStatment.executeBatch();
> connection.commit();
>
>
> This works fine in a normal scenario. But when I kill the MB node (which
> acts as the SQL client) suddenly, it seems like the execution of the second
> prepared statement is not completed. Meaning that, even though the metadata
> is removed from the respective table, the content is not. When I
> interchange the statement executions, metadata gets left in the metadata
> table and content gets deleted.
>
> Could I please know what I'm doing wrong?
>
> Also, another solution to this would be to add a foreign key constraint to
> the content table to enable cascade delete. Would that be a better approach?
>
> Thank you
> --
> Sasikala Kottegoda
> *Software Engineer*
> WSO2 Inc., http://wso2.com/
> lean. enterprise. middleware
> Mobile: +94 774835928/712792401
>
> ___
> Dev mailing list
> Dev@wso2.org
> http://wso2.org/cgi-bin/mailman/listinfo/dev
>
>
--
*Lahiru Cooray*
Software Engineer
WSO2, Inc.;http://wso2.com/
lean.enterprise.middleware
Mobile: +94 715 654154
___
Dev mailing list
Dev@wso2.org
http://wso2.org/cgi-bin/mailman/listinfo/dev
