Re: Update: Status of MySQL 4.0/Innobase/transactions & row level locks
>> [...]. Currently, in MySQL >> you must call UNLOCK TABLES before issuing another LOCK TABLES, because >> there is no deadlock detection in MySQL itself. >I believe a LOCK TABLES does an automatic atomic UNLOCK TABLES of any >that were locked before if it's able to get the requested locks. >Tim. Right, sorry, I was thinking the internal workings of the database. By the way, has anyone felt a need for 'gradual' table locking: i.e., if you do not know in advance which tables you should lock, and only find out during the transaction? Then you might want to set more table locks during the transaction. I guess such a need for gradual table locking is not very common? Regards, Heikki - 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
Re: Update: Status of MySQL 4.0/Innobase/transactions & row level locks
On Mon, Feb 12, 2001 at 03:02:49PM +0200, Heikki Tuuri wrote: > [...]. Currently, in MySQL > you must call UNLOCK TABLES before issuing another LOCK TABLES, because > there is no deadlock detection in MySQL itself. I believe a LOCK TABLES does an automatic atomic UNLOCK TABLES of any that were locked before if it's able to get the requested locks. Tim. - 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
Re: Update: Status of MySQL 4.0/Innobase/transactions & row level locks
Hi! The timeout in Innobase deadlock resolution is configurable in my.cnf file: you will be able to set it in units of one second (or would someone need an even greater precision?). In the future we might modify the MySQL code so that Innobase gets the information of MySQL LOCK TABLES commands before MySQL tries to set the locks. Then Innobase will be able to detect these deadlocks immediately and roll back the transaction. In this case we could also allow a user to issue several consecutive LOCK TABLES commands without the need to call UNLOCK TABLES between these. Currently, in MySQL you must call UNLOCK TABLES before issuing another LOCK TABLES, because there is no deadlock detection in MySQL itself. The MySQL mailing list is currently the only place to discuss the Innobase table handler. You can also send me email directly, I will be happy to reply! Regards, Heikki Tuuri Innobase Oy At 04:45 AM 2/11/01 +0100, you wrote: >Hi. > >On Fri, Feb 09, 2001 at 05:34:38PM +0200, you wrote >[...] >> 5. Add a timeout to lock waiting: Innobase can detect and resolve deadlocks >> within its own lock table, but if a user uses also MySQL LOCK TABLES... or >> BDB locks, a deadlock can occur where Innobase does not know of all the >> locks: this is resolved by adding a timeout for a lock wait, say 100 s., >> after which the transaction is rolled back. > >Maybe I misunderstand this point: But a 100 secs timeout (in worst >case of a deadlock) would be absolutely inacceptable within my >applications. :-/ > >I mean, okay, if I am going to block a table for 100 secs, that's my >problem. But it sounds as if the handler waits 100 secs before it >"solves" a dead-lock condition by failing. > >Is there a better mechanism planned for later? At least one should be >able to change the timeout (I would set it below 5 seconds...). > >Bye, > >Benjamin. > > >PS: If there is a better place to discuss the Innobase table handler >for MySQL, let me know. > > - 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
Re: Update: Status of MySQL 4.0/Innobase/transactions & row level locks
Hi. On Fri, Feb 09, 2001 at 05:34:38PM +0200, you wrote [...] > 5. Add a timeout to lock waiting: Innobase can detect and resolve deadlocks > within its own lock table, but if a user uses also MySQL LOCK TABLES... or > BDB locks, a deadlock can occur where Innobase does not know of all the > locks: this is resolved by adding a timeout for a lock wait, say 100 s., > after which the transaction is rolled back. Maybe I misunderstand this point: But a 100 secs timeout (in worst case of a deadlock) would be absolutely inacceptable within my applications. :-/ I mean, okay, if I am going to block a table for 100 secs, that's my problem. But it sounds as if the handler waits 100 secs before it "solves" a dead-lock condition by failing. Is there a better mechanism planned for later? At least one should be able to change the timeout (I would set it below 5 seconds...). Bye, Benjamin. PS: If there is a better place to discuss the Innobase table handler for MySQL, let me know. - 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
Update: Status of MySQL 4.0/Innobase/transactions & row level locks
Hi, readers of the mailing list! I promised to report to the mailing list every week how the interfacing of MySQL and the Innobase engine progresses. Innobase will provide transactions, rollback, row level locking, and Oracle-style non-locking consistent read to MySQL. It currently runs on Intel Linux and NT. The following items from the TODO list from the last week have been done: - 3. Give appropriate information to the MySQL query optimizer about table sizes, and also to users about the file space occupied by each table, index, and other data structures. 9. Prevent MySQL from using 'generate_table' in DELETE FROM ... . That is not good if we want a rollback. - I am currently working on: - 5. Add a timeout to lock waiting: Innobase can detect and resolve deadlocks within its own lock table, but if a user uses also MySQL LOCK TABLES... or BDB locks, a deadlock can occur where Innobase does not know of all the locks: this is resolved by adding a timeout for a lock wait, say 100 s., after which the transaction is rolled back. - The following items remain: - 2. Test correct handling of all MySQL data types and the SQL NULL value. 10. Port to Solaris, HP-UX and all different Unix flavors. >From the progress we can give an estimate that the interfacing will be done 2 weeks from now. I have available Linux-Compaq Alpha 64-bit and Solaris 32-bit platforms, for which the porting will be done. Then the source code hopefully compiles and works on most other Unix flavors also. Regards, Heikki Tuuri Innobase Oy Helsinki, Finland - 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
