Commonmark migration

edited Jun 15, 2020 at 9:05

1

The direct answer to your question's title is No.

SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three DBA Stack Exchanges questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that SELECT queries can lock data.

You also have special cases of SELECT where you can lock specific rows on demand.

UPDATE 2011-08-08 16:49 EDT

You asked the variation question : "Are InnoDB deadlock exceptions possibly going to be thrown by SELECT" The answer to that can be Yes under a certain condition. What is that condition ? If just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.

Based on that statement, the sequences of event to cause this could theoretically be as follows:

Your SQL UPDATEs a single row but generates an error
The UPDATE causes a rollback of the one row
The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)

#UPDATE 2015-09-22 18:40 EST

UPDATE 2015-09-22 18:40 EST

Earlier in the year, I learned that Percona has a cool Nagios check to find these pesky locks hiding behind sleeping connections. All you have to do now is run the code from that link:

SELECT COALESCE(MAX(IF(p.command = 'Sleep', p.time, 0)), 0) AS idle_in_trx FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id;

This will only works for MySQL 5.5+. If you have MySQL 5.1 or prior, you must kill all the sleeping connections to release the locks.

The direct answer to your question's title is No.

SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three DBA Stack Exchanges questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that SELECT queries can lock data.

You also have special cases of SELECT where you can lock specific rows on demand.

UPDATE 2011-08-08 16:49 EDT

You asked the variation question : "Are InnoDB deadlock exceptions possibly going to be thrown by SELECT" The answer to that can be Yes under a certain condition. What is that condition ? If just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.

Based on that statement, the sequences of event to cause this could theoretically be as follows:

Your SQL UPDATEs a single row but generates an error
The UPDATE causes a rollback of the one row
The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)

#UPDATE 2015-09-22 18:40 EST

Earlier in the year, I learned that Percona has a cool Nagios check to find these pesky locks hiding behind sleeping connections. All you have to do now is run the code from that link:

SELECT COALESCE(MAX(IF(p.command = 'Sleep', p.time, 0)), 0) AS idle_in_trx FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id;

This will only works for MySQL 5.5+. If you have MySQL 5.1 or prior, you must kill all the sleeping connections to release the locks.

The direct answer to your question's title is No.

SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three DBA Stack Exchanges questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that SELECT queries can lock data.

You also have special cases of SELECT where you can lock specific rows on demand.

UPDATE 2011-08-08 16:49 EDT

You asked the variation question : "Are InnoDB deadlock exceptions possibly going to be thrown by SELECT" The answer to that can be Yes under a certain condition. What is that condition ? If just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.

Based on that statement, the sequences of event to cause this could theoretically be as follows:

Your SQL UPDATEs a single row but generates an error
The UPDATE causes a rollback of the one row
The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)

UPDATE 2015-09-22 18:40 EST

Earlier in the year, I learned that Percona has a cool Nagios check to find these pesky locks hiding behind sleeping connections. All you have to do now is run the code from that link:

SELECT COALESCE(MAX(IF(p.command = 'Sleep', p.time, 0)), 0) AS idle_in_trx FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id;

This will only works for MySQL 5.5+. If you have MySQL 5.1 or prior, you must kill all the sleeping connections to release the locks.

replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/

Source Link

edited Apr 13, 2017 at 12:42

Community Bot

1

The direct answer to your question's title is No.

SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three DBA Stack Exchanges questions I agressively looked over with @RedBlueThing @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that SELECT queries can lock data.

You also have special cases of SELECT where you can lock specific rows on demand.

UPDATE 2011-08-08 16:49 EDT

You asked the variation question : "Are InnoDB deadlock exceptions possibly going to be thrown by SELECT" The answer to that can be Yes under a certain condition. What is that condition ? If just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.

Based on that statement, the sequences of event to cause this could theoretically be as follows:

Your SQL UPDATEs a single row but generates an error
The UPDATE causes a rollback of the one row
The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)

#UPDATE 2015-09-22 18:40 EST

Earlier in the year, I learned that Percona has a cool Nagios check to find these pesky locks hiding behind sleeping connections. All you have to do now is run the code from that link:

SELECT COALESCE(MAX(IF(p.command = 'Sleep', p.time, 0)), 0) AS idle_in_trx FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id;

This will only works for MySQL 5.5+. If you have MySQL 5.1 or prior, you must kill all the sleeping connections to release the locks.

The direct answer to your question's title is No.

SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three DBA Stack Exchanges questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that SELECT queries can lock data.

You also have special cases of SELECT where you can lock specific rows on demand.

UPDATE 2011-08-08 16:49 EDT

You asked the variation question : "Are InnoDB deadlock exceptions possibly going to be thrown by SELECT" The answer to that can be Yes under a certain condition. What is that condition ? If just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.

Based on that statement, the sequences of event to cause this could theoretically be as follows:

Your SQL UPDATEs a single row but generates an error
The UPDATE causes a rollback of the one row
The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)

#UPDATE 2015-09-22 18:40 EST

Earlier in the year, I learned that Percona has a cool Nagios check to find these pesky locks hiding behind sleeping connections. All you have to do now is run the code from that link:

SELECT COALESCE(MAX(IF(p.command = 'Sleep', p.time, 0)), 0) AS idle_in_trx FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id;

This will only works for MySQL 5.5+. If you have MySQL 5.1 or prior, you must kill all the sleeping connections to release the locks.

The direct answer to your question's title is No.

SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three DBA Stack Exchanges questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that SELECT queries can lock data.

You also have special cases of SELECT where you can lock specific rows on demand.

UPDATE 2011-08-08 16:49 EDT

You asked the variation question : "Are InnoDB deadlock exceptions possibly going to be thrown by SELECT" The answer to that can be Yes under a certain condition. What is that condition ? If just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.

Based on that statement, the sequences of event to cause this could theoretically be as follows:

Your SQL UPDATEs a single row but generates an error
The UPDATE causes a rollback of the one row
The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)

#UPDATE 2015-09-22 18:40 EST

Earlier in the year, I learned that Percona has a cool Nagios check to find these pesky locks hiding behind sleeping connections. All you have to do now is run the code from that link:

SELECT COALESCE(MAX(IF(p.command = 'Sleep', p.time, 0)), 0) AS idle_in_trx FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id;

This will only works for MySQL 5.5+. If you have MySQL 5.1 or prior, you must kill all the sleeping connections to release the locks.

replaced http://serverfault.com/ with https://serverfault.com/

Source Link

edited Apr 13, 2017 at 12:13

Community Bot

1

The direct answer to your question's title is No.

SELECT queries can perform locks on the gen_clust_index gen_clust_index, aka the Clustered Index.

Here are three DBA Stack Exchanges questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that SELECT queries can lock data.

You also have special cases of SELECT where you can lock specific rows on demand.

UPDATE 2011-08-08 16:49 EDT

You asked the variation question : "Are InnoDB deadlock exceptions possibly going to be thrown by SELECT" The answer to that can be Yes under a certain condition. What is that condition ? If just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.

Based on that statement, the sequences of event to cause this could theoretically be as follows:

Your SQL UPDATEs a single row but generates an error
The UPDATE causes a rollback of the one row
The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)

#UPDATE 2015-09-22 18:40 EST

Earlier in the year, I learned that Percona has a cool Nagios check to find these pesky locks hiding behind sleeping connections. All you have to do now is run the code from that link:

SELECT COALESCE(MAX(IF(p.command = 'Sleep', p.time, 0)), 0) AS idle_in_trx FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id;

This will only works for MySQL 5.5+. If you have MySQL 5.1 or prior, you must kill all the sleeping connections to release the locks.

The direct answer to your question's title is No.

SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three DBA Stack Exchanges questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that SELECT queries can lock data.

You also have special cases of SELECT where you can lock specific rows on demand.

UPDATE 2011-08-08 16:49 EDT

You asked the variation question : "Are InnoDB deadlock exceptions possibly going to be thrown by SELECT" The answer to that can be Yes under a certain condition. What is that condition ? If just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.

Based on that statement, the sequences of event to cause this could theoretically be as follows:

Your SQL UPDATEs a single row but generates an error
The UPDATE causes a rollback of the one row
The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)

#UPDATE 2015-09-22 18:40 EST

Earlier in the year, I learned that Percona has a cool Nagios check to find these pesky locks hiding behind sleeping connections. All you have to do now is run the code from that link:

SELECT COALESCE(MAX(IF(p.command = 'Sleep', p.time, 0)), 0) AS idle_in_trx FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id;

This will only works for MySQL 5.5+. If you have MySQL 5.1 or prior, you must kill all the sleeping connections to release the locks.

The direct answer to your question's title is No.

SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three DBA Stack Exchanges questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that SELECT queries can lock data.

You also have special cases of SELECT where you can lock specific rows on demand.

UPDATE 2011-08-08 16:49 EDT

You asked the variation question : "Are InnoDB deadlock exceptions possibly going to be thrown by SELECT" The answer to that can be Yes under a certain condition. What is that condition ? If just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.

Based on that statement, the sequences of event to cause this could theoretically be as follows:

Your SQL UPDATEs a single row but generates an error
The UPDATE causes a rollback of the one row
The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)

#UPDATE 2015-09-22 18:40 EST

Earlier in the year, I learned that Percona has a cool Nagios check to find these pesky locks hiding behind sleeping connections. All you have to do now is run the code from that link:

SELECT COALESCE(MAX(IF(p.command = 'Sleep', p.time, 0)), 0) AS idle_in_trx FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id;

This will only works for MySQL 5.5+. If you have MySQL 5.1 or prior, you must kill all the sleeping connections to release the locks.