Database commit not quick enough for Coldfusion process

Question

I have the following Coldfusion process:

1. My code makes a database call to the proc CommentInsert (this inserts a comment, and then calls an event insert proc about the comment being added called EventInsert)

2. I then call Event.GetEventByCommentId(commentId)

The result is no records returned, as the EventInsert hasn't finished adding the event record triggered by CommentInsert in Step 1.

I know this is the case, because if I create a delay between steps 1 and 2, then a recordset IS returned in step 2.

This leads me to believe that the read in step 2 is happening too quickly, before the event insert has committed in step 1.

My question is, how do tell the Coldfusion process to wait till Step 1 has completed before doing the read in Step 2??

Step one and step two are tow totally separate methods.

Code:

<cfset MessageHandlerManager = AddComment(argumentCollection=arguments) />

<cfset qEvents = application.API.EventManager.GetEventFeed(commentId=MessageHandlerManager.GetReturnItems()) />

Also, just let me add that the commentId being passed is valid. I have checked.

Another way to look at it:

Given this code:

<!--- Calls CommentInsert proc, which inserts a comment AND inserts an
event record by calling EventInsert within the proc --->
<cfset var newCommentId = AddComment(argumentCollection=arguments) />

<cfloop from="1" to="1000000" index="i">
</cfloop>

<!--- Gets the event record inserted in the code above --->
<cfset qEvent =
application.API.EventManager.GetEventFeed(commentId=newCommentId ) />

When I run the above code, qEvent comes back with a valid record. However, when I comment out the loop, the record is coming back empty.

What I think is happening is that the CommentInsert returns the new comment Id, but when the GetEventFeed function is called, the EventInsert proc hasn't completed in time and no record is found.

Thus, by adding the loop and delaying a bit, the event insert has time to finish and then a valid record is returned when GetEventFeed is called.

So my question is, how do I prevent this without using the loop.

UPDATE: Here are the two stored procs used:

DELIMITER $$

DROP PROCEDURE IF EXISTS `CommentInsert` $$
CREATE DEFINER=`root`@`%` PROCEDURE `CommentInsert`(
        IN _commentParentId bigint,
        IN _commentObjectType int,
        IN _commentObjectId bigint,
        IN _commentText text,
        IN _commentAuthorName varchar(100),
        IN _commentAuthorEmail varchar(255),
        IN _commentAuthorWebsite varchar(512),
        IN _commentSubscribe tinyint(1),
        IN _commentIsDisabled tinyint(1),
        IN _commentIsActive tinyint(1),
        IN _commentCSI int,
        IN _commentCSD datetime,
        IN _commentUSI int,
        IN _commentUSD datetime,
        OUT _commentIdOut bigint
    )
BEGIN


    DECLARE _commentId bigint default 0;

    INSERT INTO comment
            (
            commentParentId,
            commentObjectType,
            commentObjectId,
            commentText,
            commentAuthorName,
            commentAuthorEmail,
            commentAuthorWebsite,
            commentSubscribe,
            commentIsDisabled,
            commentIsActive,
            commentCSI,
            commentCSD,
            commentUSI,
            commentUSD
            )
        VALUES
            (
            _commentParentId,
            _commentObjectType,
            _commentObjectId,
            _commentText,
            _commentAuthorName,
            _commentAuthorEmail,
            _commentAuthorWebsite,
            _commentSubscribe,
            _commentIsDisabled,
            _commentIsActive,
            _commentCSI,
            _commentCSD,
            _commentUSI,
            _commentUSD
            );



    SET _commentId = LAST_INSERT_ID();


    CALL EventInsert(6, Now(), _commentId, _commentObjectType, _commentObjectId, null, null, 'Comment Added', 1, _commentCSI, Now(), _commentUSI, Now());



    SELECT _commentId INTO _commentIdOut ;

    END $$

DELIMITER ;


DELIMITER $$

DROP PROCEDURE IF EXISTS `EventInsert` $$
CREATE DEFINER=`root`@`%` PROCEDURE `EventInsert`(
        IN _eventTypeId int,
        IN _eventCreateDate datetime,
        IN _eventObjectId bigint,
        IN _eventAffectedObjectType1 int,
        IN _eventAffectedObjectId1 bigint,
        IN _eventAffectedObjectType2 int,
        IN _eventAffectedObjectId2 bigint,
    IN _eventText varchar(1024),
        IN _eventIsActive tinyint,
        IN _eventCSI int,
        IN _eventCSD datetime,
        IN _eventUSI int,
        IN _eventUSD datetime

    )
BEGIN

        INSERT INTO event
            (
            eventTypeId,
            eventCreateDate,
            eventObjectId,
            eventAffectedObjectType1,
            eventAffectedObjectId1,
            eventAffectedObjectType2,
            eventAffectedObjectId2,
      eventText,
            eventIsActive,
            eventCSI,
            eventCSD,
            eventUSI,
            eventUSD
            )
        VALUES
            (
            _eventTypeId,
            _eventCreateDate,
            _eventObjectId,
            _eventAffectedObjectType1,
            _eventAffectedObjectId1,
            _eventAffectedObjectType2,
            _eventAffectedObjectId2,
      _eventText,
            _eventIsActive,
            _eventCSI,
            _eventCSD,
            _eventUSI,
            _eventUSD
            );


    END $$

DELIMITER ;

Answer 1

Found it. Boiled down to this line in the EventManager.GetEventFeed query:

AND eventCreateDate <= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#Now()#" />

What was happening was the MySql Now() function called in the EventInsert proc was a fraction later than the Coldfusion #Now()# being used in the query. Therefore the line of code excluded that record.Also, why it was only happening when comments were added quickly.

What a biatch. Thanks for the input everyone.

Answer 2

Let me get this straight : You call a MySQL SP which does an insert which then calls another SP to do another insert. There's no return to ColdFusion between those two? Is that right?

If that's the case then the chances are there's a problem with your SP not returning values correctly or you're looking in the wrong place for the result.

I'm more inclined towards there being a problem with the MySQL SPs. They aren't exactly great and don't really give you a great deal of performance benefit. Views are useful, but the SPs are, frankly, a bit rubbish. I suspect that when you call the second SP from within the first SP and it returns a value its not being correctly passed back out of the original SP to ColdFusion, hence the lack of result.

To be honest, my suggestion would be to write two ORM functions or simple cfqueries in a suitable DAO or service to record the result of the insert of comment first and return a value. Having returned that value, make the other call to the function to get your event based on the returned comment id. (ColdFusion 8 will give you Generated_Key, ColdFusion 9 is generatedkey, I'm not sure what it'll be in Railo, but it'll be there in the "result" attribute structure).

Thinking about it, I'm not even sure why you're getting the event based on the commentid just entered. You've just added that comment against an event, so you should already have some data on that event, even if its just the ID which you can then get the full event record/object from without having to go around the house via the comment.

So over all I would suggest taking a step back and looking at the data flow you're working with and perhaps refactor it.