Re: execute multiple queries with mysql c++ connector
Date: Tue, 07 Mar 2017 12:41:01 -0500
Message-ID: <o9mr65$tgr$1_at_dont-email.me>
[Quoted] [Quoted] On Tuesday March 7 2017 12:19, in comp.databases.mysql, "The Natural [Quoted] Philosopher" <tnp_at_invalid.invalid> wrote:
> On 07/03/17 16:46, Axel Schwenke wrote:
>> On 07.03.2017 11:56, kushal bhattacharya wrote:
>>> Cant i use semicolon in c++ mysql connector too ?
>>
>> Obviously not. Haven't you just got an error message for that?
>>
>>> i Have to run the above queries simultaneously not as a separate query
>>> statement
>>
>> 1. what you have shown before was a single statement ending with a
>> semicolon, not multiple statements. You're blabbing incoherently.
>>
>> 2. no, you cannot run two (or more) statements simultaneously in one
>> connection. It's impossible. You might be able to *send* multiple
>> statements at once [1] but it's highly discouraged. But even in that case
>> the statements will be executed one after the other.
>>
>> 3. whenever you think you have to run two (or more) statements
>> simultaneously to achieve a certain result, you're doing it wrong.
>>
> > How can you therefore create an atomic unit of two or more queries? > [Quoted] > I.e. lest suppose you want to read a number, like a bank balance, and > subtract something from it and write it back, but you dont want some > other asynchronous process to be doing the same? > > No alternatives to locks?
Such things are called "transactions", and are delimited by the SQL statements
START TRANSACTION;
or
BEGIN;
to initiate the transaction, and
COMMIT;
or
ROLLBACK;
to terminate it.
The
START TRANSACTION;
or
BEGIN;
statement tells MySQL where the transaction begins. MySQL will "remember" the
state of the database at this point.
The
COMMIT;
statement tells MySQL to make permanent all changes made by the SQL statements
executed between the START TRANSACTION (or BEGIN) and this statement.
The
ROLLBACK;
statement tells MySQL to undo all changes made by the SQL statements executed
between the START TRANSACTION (or BEGIN) and this statement.
Any sql executed between these delimiters will be treated as a single unit of work, with databases and database records locked and data guaranteed accordingly.
For example:
START TRANSACTION;
SELECT _at_A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=_at_A WHERE type=1;
COMMIT;
Reference: https://dev.mysql.com/doc/refman/5.7/en/commit.html
[snip]
-- Lew Pitcher "In Skills, We Trust" PGP public key available upon requestReceived on Tue Mar 07 2017 - 18:41:01 CET