SQL unpredictable failure
<p>I wrote an SQL stored procedure, that's called trough an <code>SqlDataSource</code> element (in a ASP.NET / C# web application).
To be more precise the procedure is called from within a trigger of <code>tableA</code> bound on the <code>AFTER INSERT</code> event:</p>

<pre><code>CREATE PROCEDURE `create_checklist`(IN _id_A INT(10))
BEGIN
    DECLARE _mode CHAR(4);
    DECLARE _id_default INT(10);

    SELECT 
        mode
    FROM
        tableA
    WHERE
        id = _id_A INTO _mode;

   SELECT
        id
    FROM
        tab_doc_type
    WHERE
        def_value = 1 INTO _id_default;

   # LOCK TABLES TableA WRITE;
   # START TRANSACTION;

    INSERT INTO checklist 
         (id_type, id_A, priority)
         SELECT id_doc_type, _id_A, priority
         FROM tab_checklist 
         WHERE A_mode = _mode;

    UPDATE
        checklist
    SET
        not_needed = 1
    WHERE
        (id_A = _id_A ) AND (id_type = _id_default);

    # COMMIT;
    # UNLOCK TABLES;
END

CREATE TRIGGER `tableA_AINS` AFTER INSERT ON `tableA` FOR EACH ROW
BEGIN
    CALL create_checklist(NEW.id);

    IF NEW.mode = 'X' OR NEW.mode = 'Y' THEN
        CALL create_booking(NEW.id);
    END IF;
END
</code></pre>

<p>that should create the <code>checklist</code> entries for an element of the <code>tableA</code> and then update one of the checlist entries with a flag (<code>not_needed</code> ) with a predefined value.</p>

<p>My SQL backend is a MySQL server.</p>

<p>This works in most of the cases but users reports that 'sometime' the procedure fails without errors being shown, the <code>checklist</code> is not filled up so I'm assuming that INSERT command is not executed.</p>

<p>I'm not able to reproduce this behaviour.</p>

<p>I've been notified from a user the value of an <code>_id_A</code> on which the procedure failed and triggering it 'manually' from the <strong>MySQL SQL Editor</strong> worked withtout problems.</p>

<p>I tried to enclose lines of codes 'writing' data in table with <code>LOCK TABLES .. UNLOCK TABLES</code> and/or <code>START TRANSACTION ... COMMIT</code> without success. In both cases it seems that stored procedure cannot use such commands.</p>

<p>Anyone here is able to locate a possible failure reason?
There's a way to fix this?</p>

<p>In short there's a way to call the stored procedure from within the trigger and avoid race condition so the <code>checklist</code> table is correctly populated every time?</p>