Hi,

I did not try this with other storage engines. And I need to do this inside of the ndbcluster. Locking tables explicitly commits - so says the manual - any open transactions. I cannot commit transactions in these triggers. This would break the application transactions. btw. the manual tells me also, that lock table works only for the sql node on which the lock statement is issued. So the second sql node will not see it.
I do not think, that locking will help. In the resultset, I posted earlier, the ID 14719 appears more than two minutes(!) after ID 14739. And the clocks of the machines are in sync today.

kind regards

Hendrik Woltersdorf
XCOM AG
Tel.: 0375/27008-580
Tel. mobil: 0178/5710078
Email: Hendrik.Woltersdorf@xcom.de

Inactive hide details for Martin Skold ---29.01.2010 14:06:13---Hi again!Martin Skold ---29.01.2010 14:06:13---Hi again!

          Martin Skold <Martin.Skold@Sun.COM>
          Gesendet von: Martin.Skold@Sun.COM

          29.01.2010 14:05


An

Hendrik Woltersdorf <hendrik.woltersdorf@xcom.de>

Kopie

Cluster <cluster@lists.mysql.com>

Thema

Re: WG: Re: Antwort: Re: howto implement a fifo queue in MySQL Cluster ?

Hi again!

Have you tried this on some other storage engine?
My guess is that the trigger execution interleaves between
different threads and the execution of now() and the insert
is not atomic.
You can try locking table in the beginning of trigger
and the unlock in the end this should avoid connection threads
interleaving. This will make it very inefficient, but then we will
know if that is the problem. This seems to be a standard synchronization
issue.

BR
-- Martin

Hendrik Woltersdorf wrote:
> Hi,
>
> the inserts are done in triggers.
>
> for example:
>
> CREATE DEFINER=CURRENT_USER trigger TR_STATE_HISTORY_AFTER_INSERT AFTER
> INSERT on STATE_HISTORY
> FOR EACH ROW
> BEGIN
> ...
>      insert into Q2X_HEAD
>         (Q2X_TABLENAME  ,
>          FLAG_Q2X_STATUS,
>          DATE_Q2X_INSERT,
>          DATE_Q2X_UPDATE,
>          FLAG_OPCODE    ,
>          ERROR_CODE     ,
>          TABLE_PK
>         )
>       values('STATE_HISTORY',
>              'A',
>              NOW(),
>              NULL,
>              'I',
>              null,
>              NEW.ID_STATE_HISTORY
>             );
>
>       set $ID_Q2X_HEAD = last_insert_id();
>
> ... < some more inserts into another table (Q2X_DETAIL)>
>
> And the number of clients is between 5 and 20 and may grow.
>
> kind regards
>
> Hendrik Woltersdorf
> XCOM AG
> Tel.: 0375/27008-580
> Tel. mobil: 0178/5710078
> Email: Hendrik.Woltersdorf@xcom.de
>
> Martin.Skold@Sun.COM schrieb am 29.01.2010 11:52:59:
>
>> Martin Skold <Martin.Skold@Sun.COM>
>> Gesendet von: Martin.Skold@Sun.COM
>>
>> 29.01.2010 11:52
>>
>> An
>>
>> Hendrik Woltersdorf <hendrik.woltersdorf@xcom.de>
>>
>> Kopie
>>
>> cluster@lists.mysql.com
>>
>> Thema
>>
>> Re: Antwort: Re: howto implement a fifo queue in MySQL Cluster ?
>>
>> Hi!
>>
>> How exactly are the INSERT statements done?
>> How many clients?
>>
>> BR
>> -- Martin
>>
>> Hendrik Woltersdorf wrote:
>>> Hi,
>>>
>>> I have already set ndb_autoincrement_prefetch_sz to 1. It does not
> force
>>> order.
>>> What I get is this:
>>>
>>> ID DATE_INSERT
>>> -------------------------------------
>>> 14757, '2010-01-29 10:15:51'
>>> 14756, '2010-01-29 10:15:50'
>>> 14755, '2010-01-29 10:15:50'
>>> 14721, '2010-01-29 10:15:50'
>>> 14720, '2010-01-29 10:15:50'
>>> 14719, '2010-01-29 10:15:50'
>>> 14739, '2010-01-29 10:13:37'
>>> 14738, '2010-01-29 10:13:37'
>>> 14737, '2010-01-29 10:13:37'
>>> 14736, '2010-01-29 10:13:37'
>>>
> ...
>
>
>
> *** XCOM AG Legal Disclaimer ***
>
> Diese E-Mail einschliesslich ihrer Anhaenge ist vertraulich und ist allein
> für den Gebrauch durch den vorgesehenen Empfaenger bestimmt. Dritten ist
> das Lesen, Verteilen oder Weiterleiten dieser E-Mail untersagt. Wir bitten,
> eine fehlgeleitete E-Mail unverzueglich vollstaendig zu loeschen und uns
> eine Nachricht zukommen zu lassen.
>
> This email may contain material that is confidential and for the sole use
> of the intended recipient. Any review, distribution by others or forwarding
> without express permission is strictly prohibited. If you are not the
> intended recipient, please contact the sender and delete all copies.
>
> Hauptsitz: Bahnstrasse 37, D-47877 Willich, USt-IdNr.: DE 812 885 664
> Kommunikation: Telefon +49 2154 9209-70, Telefax +49 2154 9209-900,
>
www.xcom.de
> Handelsregister: Amtsgericht Krefeld, HRB 10340
> Vorstand: Matthias Albrecht, Marco Marty, Dr. Rainer Fuchs, Dirk Werner
> Vorsitzender des Aufsichtsrates: Stefan H. Tarach

--
MySQL Cluster Mailing List
For list archives:
http://lists.mysql.com/cluster
To unsubscribe:    
http://lists.mysql.com/cluster?unsub=Hendrik.Woltersdorf@xcom.de






*** XCOM AG Legal Disclaimer ***

Diese E-Mail einschliesslich ihrer Anhaenge ist vertraulich und ist allein für den Gebrauch durch den vorgesehenen Empfaenger bestimmt. Dritten ist das Lesen, Verteilen oder Weiterleiten dieser E-Mail untersagt. Wir bitten, eine fehlgeleitete E-Mail unverzueglich vollstaendig zu loeschen und uns eine Nachricht zukommen zu lassen.

This email may contain material that is confidential and for the sole use of the intended recipient. Any review, distribution by others or forwarding without express permission is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies.

Hauptsitz: Bahnstrasse 37, D-47877 Willich, USt-IdNr.: DE 812 885 664
Kommunikation: Telefon +49 2154 9209-70, Telefax +49 2154 9209-900, www.xcom.de
Handelsregister: Amtsgericht Krefeld, HRB 10340
Vorstand: Matthias Albrecht, Marco Marty, Dr. Rainer Fuchs, Dirk Werner
Vorsitzender des Aufsichtsrates: Stefan H. Tarach