[Maria-discuss] New Question: Locking/deadlocking on temp tables?

older
Re: [Maria-discuss] Backporting...

AskMonty KB

20 Aug 2016 20 Aug '16

1:50 a.m.

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/

Show replies by date

Justin Swanhart

20 Aug 20 Aug

5:52 a.m.

New subject: [Maria-discuss] New Question: Locking/deadlocking on temp tables?

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

Justin Swanhart

5:52 a.m.

New subject: [Maria-discuss] New Question: Locking/deadlocking on temp tables?

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/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

Justin Swanhart

22 Aug 22 Aug

7:30 p.m.

New subject: [Maria-discuss] New Question: Locking/deadlocking on temp tables?

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

Ian Gilfillan

9:22 p.m.

New subject: [Maria-discuss] New Question: Locking/deadlocking on temp tables?

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.

Reindl Harald

9:28 p.m.

New subject: [Maria-discuss] New Question: Locking/deadlocking on temp tables?

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

Colin Charles

9:52 p.m.

New subject: [Maria-discuss] New Question: Locking/deadlocking on temp tables?

...

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

Justin Swanhart

26 Aug 26 Aug

3:42 p.m.

New subject: [Maria-discuss] New Question: Locking/deadlocking on temp tables?

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 wrote:

...

...
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

2935

Age (days ago)

2942

Last active (days ago)

List overview

Download

7 comments

5 participants

participants (5)

AskMonty KB
Colin Charles
Ian Gilfillan
Justin Swanhart
Reindl Harald

[Maria-discuss] New Question: Locking/deadlocking on temp tables?

tags

participants (5)