|
Home > Archive > MySQL Server Forum > August 2005 > MYsql & transactions
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
MYsql & transactions
|
|
| princevejita1@wp.pl 2005-08-22, 9:23 am |
| Hello
I have problem with my MySQL server and transactions. I installed MySQL
server 5.0.11 with MySQL administrator & MySql Query Browser on Win XP
Prof. I would like to use transactions, so I have read the manual ana I
thought I knew how to do it. I set the tables type to InnoDB (i can't
use BDB) and change the default mysqld.exe to mysql-max (or
mysql-max-nt). I even used : SET AUTOCOMMIT=0.
I'm doing more less this:
1. START TRANACTION (i've tried also BEGIN /WORK/ )
2. UPDATE some table for example :
UPDATE account SET balance=balance-100 WHERE id=2
---- 1 row affected by the last command, no resulset returned
//Shouldn't it waite until COMMIT ??
3. ROLLBACK
---- Query returned no resultset
I looks like Mysql doesn't support transactions.
Does anyone have any suggestions about possible cause of tis problem?
Maybe some link? Any advise would be apreciated. I don't know what to
do.
Please help me :/
Tom
| |
| Gordon Burditt 2005-08-22, 11:23 am |
| >I have problem with my MySQL server and transactions. I installed MySQL
>server 5.0.11 with MySQL administrator & MySql Query Browser on Win XP
>Prof. I would like to use transactions, so I have read the manual ana I
>thought I knew how to do it. I set the tables type to InnoDB (i can't
>use BDB) and change the default mysqld.exe to mysql-max (or
>mysql-max-nt). I even used : SET AUTOCOMMIT=0.
>I'm doing more less this:
>1. START TRANACTION (i've tried also BEGIN /WORK/ )
>2. UPDATE some table for example :
> UPDATE account SET balance=balance-100 WHERE id=2
>
>---- 1 row affected by the last command, no resulset returned
An update never returns a resultset.
> //Shouldn't it waite until COMMIT ??
The query can still tell you how many rows were affected. It doesn't
mean the change is committed yet. You wouldn't want to find out
you'd made a typo, leaving off the WHERE clause, and affected 100,000
records *AFTER* you typed "COMMIT", would you? Or perhaps you
should check what you just did if it comes back "0 rows affected".
>3. ROLLBACK
>---- Query returned no resultset
A rollback never returns a resultset.
>I looks like Mysql doesn't support transactions.
How do you conclude that? Did the balance change from step 2 take
after the rollback? If so, it doesn't look like there is transaction
support. If not, it looks like there IS.
>Does anyone have any suggestions about possible cause of tis problem?
What problem?
Gordon L. Burditt
| |
| princevejita1@wp.pl 2005-08-22, 11:23 am |
| Ok
You right and I didn't explain enough my situation.
I didn't write, but after the rollback the balance remained changed,
while according to manual it should changed back.
In other words I can't cancel changes made to a table and I wish I knew
how to do it.
| |
| Gordon Burditt 2005-08-22, 1:23 pm |
| >You right and I didn't explain enough my situation.
>I didn't write, but after the rollback the balance remained changed,
>while according to manual it should changed back.
>In other words I can't cancel changes made to a table and I wish I knew
>how to do it.
Are you sure your tables are InnoDB, not MyISAM? SHOW CREATE TABLE
or SHOW TABLE STATUS to find out.
Gordon L. Burditt
| |
| princevejita1@wp.pl 2005-08-22, 1:23 pm |
| Yes definitely. Engine: InnoDB :(.
| |
| princevejita1@wp.pl 2005-08-22, 1:23 pm |
| Ok I think I works now a bit better. I will test my server more and
I'll write how it works.
Thank's for you help and time.
|
|
|
|
|