List:Cluster« Previous MessageNext Message »
From:Tom Hanlon Date:February 2 2010 6:37pm
Subject:Re: howto implement a fifo queue in MySQL Cluster ?
View as plain text  
Hendrik Woltersdorf wrote:
> Hi Tom,
> 
> thanks for the interesting details about "set timestamp" and "now()", but
> the results of now() or last_insert_id() are ok.

Well MySQL is all about "interesting" solutions, so those "features" 
might come in handy at a later date.

I imagine that the auto_increment is being reserved at transaction 
start, and not at use, or at transaction commit.

That might cause this issue.

I can not think of any way to get the functionality you seek without 
adding a table to maintain the sequences, and therefore a lock and a delay.

--
Tom



> The problem is, that the primary key values, generated by auto_increment,
> are not insertet into the table in order.
> See the following resultset (filtered with Q2X_TABLENAME and TABLE_PK):
> 
>
> "ID_Q2X_HEAD","Q2X_TABLENAME","FLAG_Q2X_STATUS","DATE_Q2X_INSERT","DATE_Q2X_UPDATE","FLAG_OPCODE","ERROR_CODE","TABLE_PK"
> 16681,"ORDERS","F","2010-02-01 15:06:20","2010-02-01
> 15:06:25","U",8002,25023173
> 16711,"ORDERS","E","2010-02-01 15:06:18","2010-02-01
> 15:06:25","I",0,25023173
> 16714,"ORDERS","E","2010-02-01 15:06:20","2010-02-01
> 15:06:25","U",0,25023173
> 16716,"ORDERS","F","2010-02-01 15:06:21","2010-02-01
> 15:06:25","U",8002,25023173
> 16719,"ORDERS","E","2010-02-01 15:06:21","2010-02-01
> 15:06:25","U",0,25023173
> 
> If I sort them by ID_Q2X_HEAD I get the update event (ID_Q2X_HEAD = 16681,
> FLAG_OPCODE="U") before I get the insert event (ID_Q2X_HEAD=16711,
> FLAG_OPCODE="I").
> Sorting by DATE_Q2X_INSERT is no solution because there will be many table
> entries per second. For example the entries with the IDs 16716 and 16719
> are inserted in the same second and there order IS important.
> 
> kind regards
> 
> Hendrik Woltersdorf
> XCOM AG
> Tel.: 0375/27008-580
> Tel. mobil: 0178/5710078
> Email: Hendrik.Woltersdorf@stripped
> 
> 
>                                                                            
>              Tom Hanlon                                                    
>              <Tom.Hanlon@stripped                                             
>              OM>                                                        An 
>              Gesendet von:              Cluster <cluster@stripped>  
>              Tom.Hanlon@stripped                                       Kopie 
>              M                                                             
>                                                                      Thema 
>                                         Re: WG: Re: Antwort: Re: howto     
>              29.01.2010 20:26           implement a fifo queue in MySQL    
>                                         Cluster ?                          
>                                                                            
>                                                                            
>                                                                            
>                                                                            
>                                                                            
>                                                                            
> 
> 
> 
> 
> Martin Skold wrote:
>> 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.
>>
> 
> 
> Have you tried getting the now() value associated with a timestamp on
> the state_history table ?
> 
> Something like
> 
> Create table (blah blah blah, mt timestamp);
> 
> And then the trigger on that table extracts the value of the timestamped
> column..
> 
> Create trigger after insert in on table insert into new table values
> NEW.mt ??
> 
> Then you would not be dependent on the now when the trigger execute, but
> the now() when the table was updated ?
> 
> Or you could perhaps leverage this somewhat interesting behavior for
> your purposes..
> mysql> select now();
> +---------------------+
> | now()               |
> +---------------------+
> | 2010-01-29 14:17:38 |
> +---------------------+
> 1 row in set (0.10 sec)
> 
> mysql> select now();
> +---------------------+
> | now()               |
> +---------------------+
> | 2010-01-29 14:17:40 |
> +---------------------+
> 1 row in set (0.00 sec)
> 
> mysql> SET TIMESTAMP=1263450854;
> Query OK, 0 rows affected (0.02 sec)
> 
> mysql> select now();
> +---------------------+
> | now()               |
> +---------------------+
> | 2010-01-14 01:34:14 |
> +---------------------+
> 1 row in set (0.00 sec)
> mysql> select now();
> +---------------------+
> | now()               |
> +---------------------+
> | 2010-01-14 01:34:14 |
> +---------------------+
> 1 row in set (0.00 sec)
> 
> *note*
> the set TIMESTAMP freezes now(); Until you set it to something else, or
> reset with 0.
> 
> 
> The SET timestamp, in the trigger.. based upon the value in a timestamp
> in the table.
> 
> Or something. Just putting some options to deal with the value of now();
> 
> --
> Tom
> 
> 
>> 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
> 
> 
> --
> 
> Tom Hanlon
> Certified MySQL DBA
> Certified Cloudera Certified Hadoop Professional (CCHP)
> 
> 
> --
> MySQL Cluster Mailing List
> For list archives: http://lists.mysql.com/cluster
> To unsubscribe:
> http://lists.mysql.com/cluster?unsub=1
> 
> 
> 
> 
> 
> 
> *** 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


-- 

Tom Hanlon
Certified MySQL DBA
Certified Cloudera Certified Hadoop Professional (CCHP)

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