Just a thought, if I were to JOIN or reference a SUBQUERY instead, inside the UPDATE statement, would that lessen the length of time for a SELECT lock? From what I understand, subqueries get temporarily cached as a temp table, so theoretically it would be a way to avoid locks, no? (or, reduce the total time used for a SELECT lock)
-----Original Message----- From: Jonathan Langevin [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 03, 2007 4:49 PM To: Jerry Schwartz; mysql@lists.mysql.com Subject: RE: Question regarding Update ... LEFT JOIN That would be fine with me, but then conversely it means multiple tables would be locked by one query, which is bad in our environment (high transaction) -----Original Message----- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 03, 2007 4:24 PM To: Jonathan Langevin; mysql@lists.mysql.com Subject: RE: Question regarding Update ... LEFT JOIN I hope it is locking both tables. Even if you aren't changing any fields in the right-hand column, you don't want anyone changing it under you. I hope that, if you aren't actually modifying the right-hand table, that MySQL uses a read (SELECT) lock. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -----Original Message----- > From: Jonathan Langevin [mailto:[EMAIL PROTECTED] > Sent: Wednesday, January 03, 2007 4:13 PM > To: mysql@lists.mysql.com > Subject: Question regarding Update ... LEFT JOIN > > I've used the syntax, UPDATE ... LEFT JOIN a few times in the > past, and > today I used it again for a new employer that I work for. > > Several of my associates were unaware that the UPDATE ... LEFT JOIN > syntax is valid in MySQL. After I demonstrated that the query does > indeed work fine on our MySQL 5.x server, one associate proposed the > possibility that by performing an UPDATE ... LEFT JOIN, you could > possibly be locking more than just the table being written to. > > > > I.E - > > UPDATE > > Table1 t1 > > LEFT JOIN Table2 t2 USING (field1) > > SET > > t1.field2 = '1' > > WHERE > > t1.field2 = '0' > > > > > > Now, my actual query was more useful than the example I wrote > above, but > my question is, would Table2 be locked, even though it is only being > read from, not written to? > > > > Any assistance on this matter would be of great assistance, and would > determine whether I'll continue to use this update structure. > > > > Many thanks! > > > > RED VENTURES > > Jonathan Langevin > PHP Developer (Home Services Corp) > 14120 Ballantyne Corporate Place > Suite 200 > Charlotte, NC 28277 > Tel: 704-971-4373 > Fax: 704-971-2303 > [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > > > > -- 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]
