>I am writing a system that requires transactions. I understand that I need
>to use Transaction-safe tables, such as BDB or InnoDB, but even when I do, I
>get an error when I try to roll back my transaction.
>
>Here is some SQL that illustrates my problem:
>
>I create a table of type BDB (failure is the same if I use InnoDB):
>
>CREATE TABLE Episodes(
> EpisodeID varchar(100) NOT NULL,
> RecordLock varchar(25),
> Primary Key (EpisodeID))
> TYPE=BDB ;
If you issue a SHOW CREATE TABLE Episodes statement, does the output
indicate that the table is indeed of type BDB? If your server wasn't
compiled with support for transactional tables, the table may default
to MyISAM (and you won't get a warning, alas).
>I put a couple of records in it to have something to work with:
>
>INSERT INTO Episodes
> (EpisodeID, RecordLock)
> VALUES
> ('Boychoir', '');
>
>INSERT INTO Episodes
> (EpisodeID, RecordLock)
> VALUES
> ('Demo', '');
>
>I start a transaction and look at the contents of my table:
>
>BEGIN;
>
>SELECT EpisodeID, RecordLock FROM Episodes;
>
>I change something in the table, still within the transaction and check that
>it is changed:
>
>UPDATE Episodes
> SET RecordLock = 'DavidW'
> WHERE EpisodeID = 'Boychoir';
>
>SELECT EpisodeID, RecordLock FROM Episodes;
>
>I discover (programmatically) that I need to roll the transaction back, so I
>do:
>
>ROLLBACK;
>
>I get the following message: ERROR 1196: Warning: Some non-transactional
>changed tables couldn't be rolled back
>
>SELECT EpisodeID, RecordLock FROM Episodes;
>
>I check again, and indeed the data is changed and the rollback failed.
>
>What am I doing wrong? I am using version 3.23.43-nt under Windows 2000.
>
>Thanks for your help,
>David Woods, Ph.D.
>Wisconsin Center for Education Research
>
>
>---------------------------------------------------------------------
>Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail <[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
