I am using MySQL and I would like to know if I make multiple select statements simultaneously in order to get information from the information schema, how are these queries handled? Could this cause some potential database malfunction?
Asked
Active
Viewed 79 times
1
-
How would you present them to the database simultaneously? – Andrew Mortimer Aug 15 '15 at 15:59
-
Possible duplicate of http://stackoverflow.com/questions/4629979/nested-select-statement-in-sql-server – nha Aug 15 '15 at 16:08
-
@mort: we may have multiple clients using the information schema. By simultaneously I mean, one client can run a select query and before this ends another one runs select. will this result in a mess? thank you for your time – Alex Aug 15 '15 at 16:14
-
@nha: I checked, and we are not asking the same thing, unfortunately – Alex Aug 15 '15 at 16:16
-
@Alex you mean different clients on different machines ? – nha Aug 15 '15 at 16:25
-
@nha: I mean different programs running on the same server. Each program plays with his own database, but may ask the information schema (I guess you call this master database?) about tables existing in his database. My question is: if two programs ask the information schema concurrently, if there can be a potential problem. – Alex Aug 15 '15 at 16:40
-
sure there is a potential problem. forbid it from happening if it really matters – Drew Aug 15 '15 at 16:45
-
@Alex the database will handle concurrent transactions, depending on the query/engine. For instance have a look at http://dba.stackexchange.com/questions/22321/mysql-transaction-vs-lock – nha Aug 15 '15 at 16:48
-
thank you guys. The query for the information schema is just a select query, and the engine is MyISAM (as far as I can tell). Is this bad or good news? – Alex Aug 15 '15 at 17:03
-
Well I'm no database expert but InnoDB could be a good pick : http://stackoverflow.com/questions/20148/myisam-versus-innodb, otherwise for your SELECT statements, have a look at : http://stackoverflow.com/questions/6415195/myisam-place-table-lock-on-table-even-when-dealing-with-select-query – nha Aug 15 '15 at 17:22
-
perhaps you are not a database expert, but you were really helpful. thank you very much – Alex Aug 15 '15 at 17:28
-
@Alex I am glad it was helpful. If that answered your question, then please accept the answer I just wrote. – nha Aug 15 '15 at 18:08
1 Answers
0
Since your are using the myISAM storage engine and are worrying about concurrent SELECT statements:
READ (SELECT) can happen concurrently as long as there is no WRITE (INSERT, UPDATE, DELETE or ALTER TABLE). Ie. you can have either one writer or several readers.
Otherwise the operations are queued and executed as soon as possible. There is a special case : concurrent inserts.
Note : if you are wondering about the choice between the two main mySQL storage engines myISAM and InnoDB, InnoDB is usually a good choice, please read this SO question.
