According to everything I've read, using LOCK TABLES...WRITE for updates,
inserts and deletes should improve performance of mysql server, but I think
I've been seeing the opposite effect.
I've been doing quite a bit of testing on a 64bit install of CentOS 5.5
installed as a guest OS on a VMWare ESXi 4.0 hypervisor on a Dell R610.
There are no other VMs on this box, and there are no other users or threads
running on the OS. Just me. I'm using this box strictly for testing of
large database migration scripts.
It seems like when I execute some of these long running statements without
locking the tables, the code runs quite a bit faster than when I do lock the
tables. And before testing each run, I do restart the server so there is no
query caching and I also use FLUSH TABLES between each test run.
All I'm asking is this: Can anything think of a scenario on a single
user-box and mysql instance, that locking tables would cause these DML
statements to slow down compared to not locking the tables?