Victor, Thanks for the prompt attention to my posting.
I am test running the queries from a different machine on a small network under windows xp. I setup a few machines running small queries like select one record, update the record, wait a few seconds, then select another record at random etc. When from another computer I run a query that selects and sorts say 200,000 records. All the mysql threads for all the workstations go to locked state except the one running the big query. No computer has locked any tables. In a minute or two when the big query is complete the computers all unlock and continue. Note that all the users except the main query stay locked while it builds the temp table and are still locked while the data is sent back through the network. It is obviously unacceptable for a select without a lock to lock all users on the system. Especially since I am only testing with a few users. Any assistance would be greatly appreciated. -----Original Message----- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, 15 July 2004 1:35 PM To: 'John Nairn '; '[EMAIL PROTECTED] ' Subject: RE: Any select with a large result set locks all other threads until it completes. Any suggestions? What type of select is this? Is the application and the database on the same machine? Is this query build a large temp table? -----Original Message----- From: John Nairn To: [EMAIL PROTECTED] Sent: 7/14/04 7:11 PM Subject: Any select with a large result set locks all other threads until it completes. Any suggestions? Hi, I am testing mysql on a large database (10GB). When I post a large select request via myodbc all other process threads lock until that thread completes. I am not locking any tables. How do I stop a read only select statement with a large result set from locking all the other threads until it completes? I am using version mysql 3.23.56 with odbc 2.5 and mysql tables. All the best. John Nairn Readysell Pty Ltd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
