Oracle – Does Trigger See All Changes Done in the Transaction It Was Fired From?

Actually, if you place an if then block in every trigger, you could effectively shutdown all triggers. Here is such a code block

IF @TRIGGER_DISABLED = 0 THEN
    ...trigger body
END IF;

In the mysql environment, you could

• run SET @TRIGGER_DISABLED = 1;
• do your data maintenance
• run SET @TRIGGER_DISABLED = 0;

So your trigger for table A should look like this:

BEGIN 
    IF @TRIGGER_DISABLED = 0 THEN
    IF (OLD.status != 1 AND NEW.status = 2) THEN 
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL) THEN 
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        END IF; 
    ELSEIF (OLD.Status = 1 AND NEW.Status != 2) THEN 
        IF (NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN 
            INSERT INTO geo (datatype, foreignid, long, lat, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status); 
        END IF; 
    ELSEIF (NEW.status != 3) THEN   
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL AND (NEW.geo_lat IS NULL OR NEW.geo_long IS NULL)) THEN 
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        ElSEIF ((OLD.geo_lat IS NULL OR OLD.geo_long IS NULL) AND NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN 
            INSERT INTO geo (datatype, foreignid, longitude, latitude, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status); 
        ELSEIF (OLD.geo_lat!=NEW.geo_lat OR OLD.geo_long != NEW.geo_long OR OLD.status != NEW.status) THEN 
            UPDATE geo SET lat = NEW.geo_lat, long = NEW.geo_long, status = NEW.status WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        END IF; 
    END IF; 
    END IF; 
END 

So your trigger for table B should look like this:

CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`     
    FOR EACH ROW     
    BEGIN     
        IF @TRIGGER_DISABLED = 0 THEN
            CALL sp-set-comment_count(NEW.`gid`);     
        END IF;
    END;     

If you want the triggers for table A to launch but not to table B, then add the code block only to table B's trigger.

If you are totally sure about the separation status of your data, you can set the global isolation level to READ COMMITTED or even to READ UNCOMMITTED. The first blocks less from the index, which should be enough in your case. The second one arranges for all SELECT statements to be non blocking. This is as low as you can go.

Whatever you choose, you should set the global isolation level back to REPEATABLE READ as soon as you finished inserting your data.

global means that this applies to all connections, which are established. So this change also affects connections beside your jarfiles. If you have the possibility to change the jarfiles, you should set the connection based isolation level with the SESSION keyword instead in both jarfiles.