List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 19 2000 12:42pm
Subject:Re: Understanding Transactions...
View as plain text  
>>>>> "Tim" == Tim Bunce <Tim.Bunce@stripped> writes:

Tim> On Wed, Jan 19, 2000 at 04:26:28AM +0200, Michael Widenius wrote:
>> >>>>> "Tim" == Tim Bunce <Tim.Bunce@stripped> writes:
>> 
Tim> On Wed, Jan 19, 2000 at 01:22:39AM +0200, Michael Widenius wrote:
>> >> 
>> >> Never say never;  We have only said that we don't intend to
>> >> support transactions in the near future as we don't regard this is
>> >> very important.  However, things has changed as we got an offer about
>> >> transactions that we can't easily refuse and because of this
>> >> transactions is now on the top of our TODO.
>> 
Tim> Interesting.
>> 
Tim> Given the responses in this thread I guess most people will be
Tim> disapointed that that work will delay other "more important" work ;-)
>> 
>> >> To be able to support transactions in the update log, we have to add a
>> >> behaveour that resembles a lot the one that you have described. The
>> >> only addition is that ROLLBACK will be possible...
>> 
Tim> How will you deal with transaction isolation?
>> 
>> You will have the commands TRANSACTION START, COMMIT and ROLLBACK.
>> This should take care of the above problem.

Tim> That doesn't address isolation. For isolation you need locking (or
Tim> tables with data versioning like Oracle and, recently, PostgreSQL).
Tim> But MySQL locking (for isolation) currently requires all locks to be
Tim> taken up-front and that's, er, unnatural for transactional systems.

Not that this is not needed with the transaction safe tables;  This
will use internal page level locking and issue automatic rollbacks in
case of conflicts.

Tim> Yes there is a time when the update log and database is not perfectly
Tim> in sync.  In fact that can be quite a long time because delayed writing
Tim> of the data tables is a valid optimisation that I know Ingres uses
Tim> and I think Oracle and others do as well. (I did all this RDBMS
Tim> optimization theory back when I was using Ingres 6.4.)
>> 
>> This will help somewhat;  But you still have a small time slice
>> between the write and the flush when things can go wrong and you can't
>> know if you got the last transaction done or not.

Tim> You never trust the write. A commit should only return success when the
Tim> flush (or synchronous write) succeeds.

The problem is more what happens if the computer goes down between the
write to disk and flush or before the client gets the responce. The
only acceptable behaveour in this case is if the client when it gets a
'connection dropped' can do a automatic reconnect and 100 % reliable
ensure that the transaction was applied or not.  I haven't heard of
anyone doing this!


>> >> There
>> >> is also a small problem when the client don't get time to get the ok
>> >> from the server before it crashed.  You can theoretically only make it
>> >> recover 'almost sure', never perfect.
>> 
Tim> I don't see the big problem here. The only issue is that the client
Tim> may get a "false negative" from the "commit" because there's a tiny
Tim> chance it will return with a "connection dropped" error even though
Tim> the commit was successful. The recovery when the server is restarted
Tim> should be fine, in the sense that it will recover to the last commit.
>> 
>> But the client doesn't know if the command was successful or even if
>> he got an automatic rollback. He doesn't even have any way of checking
>> this.  This can be as bad as a lost transaction in the worst case.

Tim> The client knows a "network" error has occured on the commit
Tim> so knows there's a chance that commit succeeded, and can check that
Tim> the transaction completed when it reconnects to the database.

Note that a normal program can never do this reliable.  For example:

// Let increase our salaries
UPDATE salaries SET salaries=salaires*2

Now if a computer with transaction supports goes done during the
execution of the above query, the program can never know if the
transaction was completed or not.


>> (Note that we are of course discussing improbable situation, but if we
>> are talking about 100 % security, this issue must also be solved)

Tim> Sure, but there is _no_ solution (as far as I'm aware) that works at
Tim> the time of the commit. Even a three phase commit protocol can loose
Tim> the final commit packet. You could use a transaction sequence number
Tim> to simplify the check when the clients reconnects.

Yes, but I don't know of anyone that does it at the moment.
(Please correct me if I am wrong;  I am not always up to date with all
the features of the other SQL servers :)

Note that the replication server we are working on has this part
integrated in the protocol

Regards,
Monty
Thread
Understanding Transactions...rjb17 Jan
  • Re: Understanding Transactions...sinisa17 Jan
  • Re: Understanding Transactions...Doug Robinson17 Jan
    • Re: Understanding Transactions...sinisa17 Jan
    • Re: Understanding Transactions...Gregor Welters22 Feb
  • Re: Understanding Transactions...Sasha Pachev17 Jan
    • Re: Understanding Transactions...Tim Bunce17 Jan
      • Re: Understanding Transactions...sinisa18 Jan
        • Re: Understanding Transactions...Tim Bunce18 Jan
          • Re: Understanding Transactions...sinisa18 Jan
            • Re: Understanding Transactions...Tim Bunce18 Jan
              • Re: Understanding Transactions...sinisa18 Jan
                • Re: Understanding Transactions...Tim Bunce18 Jan
                  • Re: Understanding Transactions...sinisa19 Jan
                    • Re: Understanding Transactions...Tim Bunce20 Jan
          • Re: Understanding Transactions...Michael Widenius26 Jan
      • Re: Understanding Transactions...Christopher E. Brown18 Jan
        • Re: Understanding Transactions...James Rogers18 Jan
          • Re: Understanding Transactions...sinisa19 Jan
      • Re: Understanding Transactions...Michael Widenius26 Jan
        • Re: Understanding Transactions...Tim Bunce27 Jan
  • Re: Understanding Transactions...rjb17 Jan
  • Re: Understanding Transactions...Sasha Pachev17 Jan
  • Re: Understanding Transactions...Sven E. van 't Veer18 Jan
    • Re: Understanding Transactions...sinisa18 Jan
      • Re[2]: Understanding Transactions...Sven E. van 't Veer18 Jan
  • Re: Understanding Transactions...Jan Dvorak18 Jan
    • Re: Understanding Transactions...Michael Widenius18 Jan
      • Re: Understanding Transactions...Tim Bunce19 Jan
        • Re: Understanding Transactions...Patrick Greenwell19 Jan
          • Re: Understanding Transactions...sasha19 Jan
            • Re: Understanding Transactions...Patrick Greenwell19 Jan
              • Re: Understanding Transactions...Michael Widenius19 Jan
                • MySQL specific filesystemPatrick Greenwell19 Jan
          • Re: Understanding Transactions...Michael Widenius19 Jan
        • Re: Understanding Transactions...Michael Widenius19 Jan
          • Re: Understanding Transactions...Tim Bunce19 Jan
            • Re: Understanding Transactions...Michael Widenius19 Jan
              • Re: Understanding Transactions...Tim Bunce20 Jan
                • Re: Understanding Transactions...sinisa20 Jan