List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:November 9 2001 1:36am
Subject:Re: MySQL Transaction Problem
View as plain text  
>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 <mysql-thread90630@stripped>
>To unsubscribe, e-mail <mysql-unsubscribe-paul=snake.net@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Thread
MySQL Transaction ProblemDavid Woods8 Nov
  • Re: MySQL Transaction ProblemPaul DuBois9 Nov
    • RE: MySQL Transaction ProblemDavid Woods9 Nov
      • RE: MySQL Transaction ProblemDavid Woods9 Nov
  • Simple JDBC QuestionMichael Bitzko9 Nov
    • RE: Simple JDBC QuestionVenu9 Nov