[Maria-discuss] New Question: Locking/deadlocking on temp tables?
![](https://secure.gravatar.com/avatar/b08670b8f8b6a0eea81857347b9ce12e.jpg?s=120&d=mm&r=g)
Hello, A new question has been asked in "MariaDB Knowledge Base" by WebtrendsData. Please answer it at http://mariadb.com/kb/en/lockingdeadlocking-on-temp-tables/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hello - I am coming up to speed on MariaDB, I have a MS Sql Server and Hadoop background but we are starting to use MariaDB heavily. We have some queries that hit tables with mediumtext datatype columns. My understanding is these types of queries will always generate temp tables directly to disk (because of the text/blob types). I've confirmed our temp table engine is Aria.
From what I've read, in the mysql world, this situation would end up with a MyIsam table on disk, which does not have row locking, only table locking. I've tried looking for info on how Aria tables would handle this as I suspect we are getting deadlocks on these tables. Our application is reporting many database deadlocks, our entire database is all ExtraDB tables, yet SHOW ENGINE INNODB STATUS does not report any deadlocks, which leads me to think it is the temp tables.
So I guess my question is, does the Aria engine do row locking? Is it possible to get a status similar to SHOW ENGINE INNODB STATUS? Please forgive me if this is painfully obvious or unclear (I'm still learning!), and thank you for your time. -Aaron -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/lockingdeadlocking-on-temp-tables/
![](https://secure.gravatar.com/avatar/392856b43200ef35060b655747853c7c.jpg?s=120&d=mm&r=g)
If you are using Galera, then aborted transactions due to lock conflict are
reported as deadlocks.
Temporary tables are not shared between connections and can not cause
deadlocks.
--Justin
On Fri, Aug 19, 2016 at 7:50 PM, AskMonty KB
Hello,
A new question has been asked in "MariaDB Knowledge Base" by WebtrendsData. Please answer it at http://mariadb.com/kb/en/ lockingdeadlocking-on-temp-tables/ as the person asking the question may not be subscribed to the mailing list.
-------------------------------- Hello - I am coming up to speed on MariaDB, I have a MS Sql Server and Hadoop background but we are starting to use MariaDB heavily. We have some queries that hit tables with mediumtext datatype columns. My understanding is these types of queries will always generate temp tables directly to disk (because of the text/blob types). I've confirmed our temp table engine is Aria.
From what I've read, in the mysql world, this situation would end up with a MyIsam table on disk, which does not have row locking, only table locking. I've tried looking for info on how Aria tables would handle this as I suspect we are getting deadlocks on these tables. Our application is reporting many database deadlocks, our entire database is all ExtraDB tables, yet SHOW ENGINE INNODB STATUS does not report any deadlocks, which leads me to think it is the temp tables.
So I guess my question is, does the Aria engine do row locking? Is it possible to get a status similar to SHOW ENGINE INNODB STATUS? Please forgive me if this is painfully obvious or unclear (I'm still learning!), and thank you for your time. -Aaron
--------------------------------
To view or answer this question please visit: http://mariadb.com/kb/en/ lockingdeadlocking-on-temp-tables/
_______________________________________________ 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
![](https://secure.gravatar.com/avatar/392856b43200ef35060b655747853c7c.jpg?s=120&d=mm&r=g)
Oh yeah, this stupid system where I have to log into the web. So much
stupid around here.
On Fri, Aug 19, 2016 at 11:52 PM, Justin Swanhart
If you are using Galera, then aborted transactions due to lock conflict are reported as deadlocks.
Temporary tables are not shared between connections and can not cause deadlocks.
--Justin
On Fri, Aug 19, 2016 at 7:50 PM, AskMonty KB
wrote: Hello,
A new question has been asked in "MariaDB Knowledge Base" by WebtrendsData. Please answer it at http://mariadb.com/kb/en/locki ngdeadlocking-on-temp-tables/ as the person asking the question may not be subscribed to the mailing list.
-------------------------------- Hello - I am coming up to speed on MariaDB, I have a MS Sql Server and Hadoop background but we are starting to use MariaDB heavily. We have some queries that hit tables with mediumtext datatype columns. My understanding is these types of queries will always generate temp tables directly to disk (because of the text/blob types). I've confirmed our temp table engine is Aria.
From what I've read, in the mysql world, this situation would end up with a MyIsam table on disk, which does not have row locking, only table locking. I've tried looking for info on how Aria tables would handle this as I suspect we are getting deadlocks on these tables. Our application is reporting many database deadlocks, our entire database is all ExtraDB tables, yet SHOW ENGINE INNODB STATUS does not report any deadlocks, which leads me to think it is the temp tables.
So I guess my question is, does the Aria engine do row locking? Is it possible to get a status similar to SHOW ENGINE INNODB STATUS? Please forgive me if this is painfully obvious or unclear (I'm still learning!), and thank you for your time. -Aaron
--------------------------------
To view or answer this question please visit: http://mariadb.com/kb/en/lockingdeadlocking-on-temp-tables/
_______________________________________________ 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
![](https://secure.gravatar.com/avatar/392856b43200ef35060b655747853c7c.jpg?s=120&d=mm&r=g)
When is this system getting fixed? Having to log into web to answer email is idiotic... Sent from my iPhone
On Aug 19, 2016, at 8:52 PM, Justin Swanhart
wrote: Oh yeah, this stupid system where I have to log into the web. So much stupid around here.
On Fri, Aug 19, 2016 at 11:52 PM, Justin Swanhart
wrote: If you are using Galera, then aborted transactions due to lock conflict are reported as deadlocks. Temporary tables are not shared between connections and can not cause deadlocks.
--Justin
On Fri, Aug 19, 2016 at 7:50 PM, AskMonty KB
wrote: Hello, A new question has been asked in "MariaDB Knowledge Base" by WebtrendsData. Please answer it at http://mariadb.com/kb/en/lockingdeadlocking-on-temp-tables/ as the person asking the question may not be subscribed to the mailing list.
-------------------------------- Hello - I am coming up to speed on MariaDB, I have a MS Sql Server and Hadoop background but we are starting to use MariaDB heavily. We have some queries that hit tables with mediumtext datatype columns. My understanding is these types of queries will always generate temp tables directly to disk (because of the text/blob types). I've confirmed our temp table engine is Aria.
From what I've read, in the mysql world, this situation would end up with a MyIsam table on disk, which does not have row locking, only table locking. I've tried looking for info on how Aria tables would handle this as I suspect we are getting deadlocks on these tables. Our application is reporting many database deadlocks, our entire database is all ExtraDB tables, yet SHOW ENGINE INNODB STATUS does not report any deadlocks, which leads me to think it is the temp tables.
So I guess my question is, does the Aria engine do row locking? Is it possible to get a status similar to SHOW ENGINE INNODB STATUS? Please forgive me if this is painfully obvious or unclear (I'm still learning!), and thank you for your time. -Aaron
--------------------------------
To view or answer this question please visit: http://mariadb.com/kb/en/lockingdeadlocking-on-temp-tables/
_______________________________________________ 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
![](https://secure.gravatar.com/avatar/a2be5e3cc035a1320b5870360a7338f7.jpg?s=120&d=mm&r=g)
It's not, and ranting about things being "idiotic" and "stupid" is not going to help. As pointed out many times in the past, it works like this by design, as not many people view the questions on the website, so sending them to the email list gets their questions more visibility. Most of the people asking the questions are not on the mailing list. If clicking a link (and logging in once) is too much effort and upsetting for you, rather than raving at the list, just hit delete :) On 22/08/2016 19:30, Justin Swanhart wrote:
When is this system getting fixed? Having to log into web to answer email is idiotic...
Sent from my iPhone
On Aug 19, 2016, at 8:52 PM, Justin Swanhart
mailto:greenlion@gmail.com> wrote: Oh yeah, this stupid system where I have to log into the web. So much stupid around here.
![](https://secure.gravatar.com/avatar/359652f5d5394fdc76fea714c0e40c97.jpg?s=120&d=mm&r=g)
Am 22.08.2016 um 21:22 schrieb Ian Gilfillan:
It's not, and ranting about things being "idiotic" and "stupid" is not going to help. As pointed out many times in the past, it works like this by design, as not many people view the questions on the website, so sending them to the email list gets their questions more visibility. Most of the people asking the questions are not on the mailing list.
then just point them to the mailing list when i get a question in my mail-client i expect to be able to just press reply - and guess what - reply! if that don't work then fix your process to connect mail-responses to the webpage or just don't bother list subscribers with the web stuff it#s really easy - make it proper or don't do it at all
If clicking a link (and logging in once) is too much effort and upsetting for you, rather than raving at the list, just hit delete :)
On 22/08/2016 19:30, Justin Swanhart wrote:
When is this system getting fixed? Having to log into web to answer email is idiotic...
Sent from my iPhone
On Aug 19, 2016, at 8:52 PM, Justin Swanhart
mailto:greenlion@gmail.com> wrote: Oh yeah, this stupid system where I have to log into the web. So much stupid around here
![](https://secure.gravatar.com/avatar/9f3e26ecb3362dad1217bb6a858147a5.jpg?s=120&d=mm&r=g)
On 23 Aug 2016, at 03:28, Reindl Harald
wrote: It's not, and ranting about things being "idiotic" and "stupid" is not going to help. As pointed out many times in the past, it works like this by design, as not many people view the questions on the website, so sending them to the email list gets their questions more visibility. Most of the people asking the questions are not on the mailing list.
then just point them to the mailing list
when i get a question in my mail-client i expect to be able to just press reply - and guess what - reply!
if that don't work then fix your process to connect mail-responses to the webpage or just don't bother list subscribers with the web stuff
it#s really easy - make it proper or don't do it at all
Agree that complaining isn’t going to help. But agree that fixing the process is what needs to happen - this is after all a mailing list. If the resources are an issue, maybe the Knowledgebase code can be open sourced and others can add such a feature? cheers, -colin -- Colin Charles, http://bytebot.net/blog/ twitter: @bytebot | skype: colincharles "First they ignore you, then they laugh at you, then they fight you, then you win." -- Mohandas Gandhi
![](https://secure.gravatar.com/avatar/392856b43200ef35060b655747853c7c.jpg?s=120&d=mm&r=g)
Hi,
I don't have the email on hand, but it was acknowledged by MariaDB that
this system is inefficient. I'm not the first one to complain about it. I
figure I should keep complaining about it, since there was a promise to fix
it. I'll refrain from characterizing the current situation with an
negative statements.
--Justin
On Mon, Aug 22, 2016 at 3:52 PM, Colin Charles
On 23 Aug 2016, at 03:28, Reindl Harald
wrote: It's not, and ranting about things being "idiotic" and "stupid" is not going to help. As pointed out many times in the past, it works like this by design, as not many people view the questions on the website, so sending them to the email list gets their questions more visibility. Most of the people asking the questions are not on the mailing list.
then just point them to the mailing list
when i get a question in my mail-client i expect to be able to just press reply - and guess what - reply!
if that don't work then fix your process to connect mail-responses to the webpage or just don't bother list subscribers with the web stuff
it#s really easy - make it proper or don't do it at all
Agree that complaining isn’t going to help. But agree that fixing the process is what needs to happen - this is after all a mailing list.
If the resources are an issue, maybe the Knowledgebase code can be open sourced and others can add such a feature?
cheers, -colin
-- Colin Charles, http://bytebot.net/blog/ twitter: @bytebot | skype: colincharles "First they ignore you, then they laugh at you, then they fight you, then you win." -- Mohandas Gandhi
_______________________________________________ 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 (5)
-
AskMonty KB
-
Colin Charles
-
Ian Gilfillan
-
Justin Swanhart
-
Reindl Harald