List:Cluster« Previous MessageNext Message »
From:Hendrik Woltersdorf Date:January 29 2010 1:26pm
Subject:Antwort: Re: WG: Re: Antwort: Re: howto implement a fifo queue in MySQL
Cluster ?
View as plain text  
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@stripped


                                                                           
             Martin Skold                                                  
             <Martin.Skold@Sun                                             
             .COM>                                                      An 
             Gesendet von:              Hendrik Woltersdorf                
             Martin.Skold@Sun.          <hendrik.woltersdorf@stripped>      
             COM                                                     Kopie 
                                        Cluster <cluster@strippedsql.com>  
                                                                     Thema 
             29.01.2010 14:05           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@stripped
>
> Martin.Skold@stripped schrieb am 29.01.2010 11:52:59:
>
>> Martin Skold <Martin.Skold@stripped>
>> Gesendet von: Martin.Skold@stripped
>>
>> 29.01.2010 11:52
>>
>> An
>>
>> Hendrik Woltersdorf <hendrik.woltersdorf@stripped>
>>
>> Kopie
>>
>> cluster@stripped
>>
>> 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@stripped






*** 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. TarachAttachment: [text/html]
Attachment: [text/html]
Attachment: [text/html]
Thread
WG: Re: Antwort: Re: howto implement a fifo queue in MySQL Cluster ?Hendrik Woltersdorf29 Jan
  • Re: WG: Re: Antwort: Re: howto implement a fifo queue in MySQL Cluster?Martin Skold29 Jan
    • Antwort: Re: WG: Re: Antwort: Re: howto implement a fifo queue in MySQLCluster ?Hendrik Woltersdorf29 Jan
      • Re: Antwort: Re: WG: Re: Antwort: Re: howto implement a fifo queue inMySQL Cluster ?Martin Skold29 Jan
        • Re: howto implement a fifo queue in MySQL Cluster ?Hendrik Woltersdorf29 Jan
          • Re: howto implement a fifo queue in MySQL Cluster ?Martin Skold29 Jan
            • Re: howto implement a fifo queue in MySQL Cluster ?Hendrik Woltersdorf29 Jan
    • Re: WG: Re: Antwort: Re: howto implement a fifo queue in MySQL Cluster?Tom Hanlon29 Jan
    • Re: WG: Re: Antwort: Re: howto implement a fifo queue in MySQL Cluster?Tom Hanlon29 Jan
Re: howto implement a fifo queue in MySQL Cluster ?Tom Hanlon2 Feb