Results 1 to 5 of 5

Thread: Select Issue

  1. #1
    Join Date
    May 2003
    Posts
    5

    Unanswered: Select Issue

    Hi,

    In my project ,I have a Java thread which runs at every one minute and picks up the record by using select from mysql table and send some messages to the mail id provided in that row.

    My problem is that initially I set a flag for the email id's in the table as false.

    So when my Java thread runs first , it will select the records whose flag is set to false.Then my thread starts sending mail one by one.

    Meanwhile my second thread starts after 1 min and again my thread selects the rows from the table where flag set to false.

    After sending the mail I am updating the table with flag set to true.

    Now How do I prevent both threads selecting same record and sending mail.

    As you know say first thread is selecting a particular record and proceesing it.Before updating the record there is every possibility that the same record may be selected by the second thread and it may also process it.Bcause of which there is a possibilty that two times mail will be sent to the same mail id.

    How do I address this issue in mysql?.

    But my thread has to run at every minute.

    PL.advise.

    Ram

  2. #2
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: Select Issue

    Originally posted by rampriyak
    Hi,

    In my project ,I have a Java thread which runs at every one minute and picks up the record by using select from mysql table and send some messages to the mail id provided in that row.

    My problem is that initially I set a flag for the email id's in the table as false.

    So when my Java thread runs first , it will select the records whose flag is set to false.Then my thread starts sending mail one by one.

    Meanwhile my second thread starts after 1 min and again my thread selects the rows from the table where flag set to false.

    After sending the mail I am updating the table with flag set to true.

    Now How do I prevent both threads selecting same record and sending mail.

    As you know say first thread is selecting a particular record and proceesing it.Before updating the record there is every possibility that the same record may be selected by the second thread and it may also process it.Bcause of which there is a possibilty that two times mail will be sent to the same mail id.

    How do I address this issue in mysql?.

    But my thread has to run at every minute.

    PL.advise.

    Ram
    1-Use a version of MySQL server that has InnoDb table handler.

    2-Choose the proper isolation level your need and turn autocommit to OFF and declare your transactions yourself.

    http://www.mysql.com/doc/en/InnoDB_t...ion_model.html

    3-Use SELECT...FOR UPDATE

    SELECT col1, col2, col3 FROM table1 WHERE col4=val4 FOR UPDATE

    Hope this helps!

  3. #3
    Join Date
    May 2003
    Posts
    5

    Re: Select Issue

    Thank you for your reply.

    I will implement this and will get back to you with my findings.

    Meanwhile do I need to do all or is it ok if I do any one of the process u mentioned?.

    Regards,

    Ram


    Originally posted by bstjean
    1-Use a version of MySQL server that has InnoDb table handler.

    2-Choose the proper isolation level your need and turn autocommit to OFF and declare your transactions yourself.

    http://www.mysql.com/doc/en/InnoDB_t...ion_model.html

    3-Use SELECT...FOR UPDATE

    SELECT col1, col2, col3 FROM table1 WHERE col4=val4 FOR UPDATE

    Hope this helps!

  4. #4
    Join Date
    May 2003
    Posts
    5

    Select issue

    Hi,

    Still I am unable to get into InnoDB as though I have installed higher version of mysql 3.23.56 which has support for InnoDB.

    In my windows my.ini file looks like this.

    [WinMySQLAdmin]
    Server=D:/mysql/bin/mysqld-max-nt.exe

    innodb_data_home_dir =
    innodb_data_file_path = ibdata1:100M:autoextend

    Is there any mistake in the ini file?.

    After starting the mysql server,when I put a query,

    show variables like 'have_%'

    I am getting InnDB as 'disabled' only.

    Pl.advise.

    Regards,

    Ram

  5. #5
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: Select issue

    Originally posted by rampriyak
    Hi,

    Still I am unable to get into InnoDB as though I have installed higher version of mysql 3.23.56 which has support for InnoDB.

    In my windows my.ini file looks like this.

    [WinMySQLAdmin]
    Server=D:/mysql/bin/mysqld-max-nt.exe

    innodb_data_home_dir =
    innodb_data_file_path = ibdata1:100M:autoextend

    Is there any mistake in the ini file?.

    After starting the mysql server,when I put a query,

    show variables like 'have_%'

    I am getting InnDB as 'disabled' only.

    Pl.advise.

    Regards,

    Ram
    Here's a sample of my file:


    innodb_data_file_path = tablespace_01:1024M:autoextend
    innodb_data_home_dir=C:\\Prog\\MySQL4\\innodb\\tab lespaces
    innodb_log_group_home_dir = C:\\Prog\\MySQL4\\innodb\\logs
    innodb_log_arch_dir = C:\\Prog\\MySQL4\\innodb\\logs
    innodb_flush_log_at_trx_commit=1
    innodb_log_archive=0

    As you can see, under Windows you need to double the "\"

    So my InnoDb data files are in: C:\Prog\MySQL4\innodb\tablespaces

    My log files are in :
    C:\Prog\MySQL4\innodb\logs

    And you can look at "what went wrong" by looking at the file mysql.err that is located in /data subdirectory in MySQL.

    Hope this helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •