From: Perrin Harkins
Date: January 21 2010 12:50pm
Subject: Re: Selecting, Inserting and Deleting data
List-Archive: http://lists.mysql.com/mysql/220333
Message-Id: <66887a3d1001210450m69c8bdc0kc108a9efb514fa3c@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1

On Thu, Jan 21, 2010 at 7:15 AM, Krishna Chandra Prajapati
<prajapatikc@stripped> wrote:
> 1. Inserted data into a table A by user.
> 2. Selecting data from table A inserting data to table B after applying some
> rules(update).
> 3. Deleting data from table A.
> 4. Selecting data from table B using some conditions (SELECT sql_id, momt,
> sender, receiver, udhdata, msgdata, time, smsc_id, service, account, id,
> sms_type, mclass, mwi, coding, compress FROM alt_send_sms WHERE smsc_id =
> 'ite' ORDER BY msg_priority, sql_id LIMIT 0,20) moving to third party for
> sending sms.
> 5. Deleting the selected data from table B.
>
> With the above scenario, i am not able to user concurrent connections. Other
> wise it will send duplicate sms.

I see, you want to lock on steps 4 and 5 to prevent concurrent access
to the same records.  You can use SELECT FOR UPDATE (if you're using
InnoDB) or just lock the whole table.

- Perrin