Shankar, MySQL now provides (3.23.34a Unix source distribution) transactions both through BDB and Innobase tables. I will try to answer your questions for Innobase. >After the vitriolic debates I've seen between the PostgreSQL fanatics and >the MySQL defenders, I thought I'd ask for clarification on the support for >transactions that's supposedly provided by the use of BDB tables: >Having used Oracle for a while (and given my understanding of how >transactions work there), does using BDB guarantee that: >* DML that's not explicitly COMMITted will be automatically ROLLedBACK if >the connection is terminated prematurely? Yes it works for Innobase. >* Other connections will continue to see a perfectly consistent snapshot of >the DB from before the start of the transaction, as long as the transaction >is not committed? Innobase is multiversioned like Oracle: the above holds for Innobase. For non-multiversioned databases like MS SQL Server the above does not hold, because other users are not able to read rows another transaction has modified and locked. >* If the DB server process crashes in the middle of a transaction, then on >restart, it will present a view of the DB without any of the effects of >uncommitted transactions? And *with* all the effects of all committed >transactions (needless to say)? Works for Innobase. >* How about locking? While a transaction is not committed, how much of the >tables that it modifies is automatically locked against updates by other >connections? The whole table? A page of the table? A row (dare I hope?)? Innobase has row level locking while BDB has page level locking. Also, because of multiversioning, Innobase users are able to do SELECTS to a consistent snapshot of the database, regardless of which rows other users have locked. >* (To labor a point) If a connection dies in the middle of a transaction >holding locks, will it automatically roll them changes and free the locks >and unblock any other operations waiting on that lock? Works for Innobase. >Just checking.. Thanks! >-- >Shankar Unni [EMAIL PROTECTED] (408) 434-8311 Regards, Heikki Tuuri Innobase Oy --------------------------------------------------------------------- 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
