[Maria-discuss] Are queries queued when locking a table?
Hello, Some backup tools make use of table locking to ensure data consistency during the operation. I'm wondering about what happens to the incoming queries for the table being locked. Obviously, the request will be pending until the table is unlocked but this way some timeout value may be reached. Another option could be to accept and queue all requests and replay them once the table is unlocked. So can you tell me how MariaDB is handling such situation. Is the second option (asynchronous request), can be activated within MariaDB? Best, Jimmy
Am 28.05.2014 15:43, schrieb Jimmy Thrasibule:
Some backup tools make use of table locking to ensure data consistency during the operation. I'm wondering about what happens to the incoming queries for the table being locked.
Obviously, the request will be pending until the table is unlocked but this way some timeout value may be reached. Another option could be to accept and queue all requests and replay them once the table is unlocked.
So can you tell me how MariaDB is handling such situation. Is the second option (asynchronous request), can be activated within MariaDB?
you don't want that because what answer would the application sending the query get? OK, done? what if the query fails? what if the next would not happen after the first error? if you can't live with locks for backups setup a replication slave and make your backups from the slave, we are doing that over years now by stop the slave, rsync the datadir over the WAN and start the slave again which pulls the last changes from the binlog
I think this is just handled by the setting of the variable 'lock_wait_timeout' : http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_l... But also you should know 'innodb_lock_wait_timeout' http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_... It is true that some backup tools will LOCK (complete) tables. This applies to some backup tools deployed on various hosting systems and integrated into various 'Control Panel' applications But IMO this just reflects that the still think MySQL is == MyISAM (because wiht MyISAM etc. there is no other way to ensure consistency). With InnoDB you may 'backup in a single transaction'. mysqldump ( http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_singl...) and also other tools support this (at least SQLyog/SJA does). On Wed, May 28, 2014 at 3:43 PM, Jimmy Thrasibule < thrasibule.jimmy@gmail.com> wrote:
Hello,
Some backup tools make use of table locking to ensure data consistency during the operation. I'm wondering about what happens to the incoming queries for the table being locked.
Obviously, the request will be pending until the table is unlocked but this way some timeout value may be reached. Another option could be to accept and queue all requests and replay them once the table is unlocked.
So can you tell me how MariaDB is handling such situation. Is the second option (asynchronous request), can be activated within MariaDB?
Best, Jimmy
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (3)
-
Jimmy Thrasibule -
Peter Laursen -
Reindl Harald