SpirirHost Support Centre: MySQL Reference Manual p.355

<< previous page -- table of contents -- next page >>

Chapter_1

Chapter_2

Chapter_3

Chapter_4

Chapter_5

Return to Table of Contents

Table of Contents
1. General Information
2. MySQL Installation
3. Tutorial Introduction
4. Database Administration
5. MySQL Optimisation
6. MySQL Language Reference
7. MySQL Table Types
8. MySQL APIs
9. Extending MySQL

Chapter 5: MySQL Optimisation 355 Using SQL_BUFFER_RESULT can also help making table locks shorter. See Section 6.4.1 [SELECT], page 447. You could also change the locking code in `mysys/thr_lock.c' to use a single queue. In this case, write locks and read locks would have the same priority, which might help some applications. 5.3.2 Table Locking Issues The table locking code in MySQL is deadlock free. MySQL uses table locking (instead of row locking or column locking) on all table types, except InnoDB and BDB tables, to achieve a very high lock speed. For large tables, table locking is much better than row locking for most applications, but there are, of course, some pitfalls. For InnoDB and BDB tables, MySQL only uses table locking if you explicitly lock the table with LOCK TABLES. For these table types we recommend you to not use LOCK TABLES at all, because InnoDB uses automatic row level locking and BDB uses page level locking to ensure transaction isolation. In MySQL Version 3.23.7 and above, you can insert rows into MyISAM tables at the same time other threads are reading from the table. Note that currently this only works if there are no holes after deleted rows in the table at the time the insert is made. When all holes has been lled with new data, concurrent inserts will automatically be enabled again. Table locking enables many threads to read from a table at the same time, but if a thread wants to write to a table, it must rst get exclusive access. During the update, all other threads that want to access this particular table will wait until the update is ready. As updates on tables normally are considered to be more important than SELECT, all state- ments that update a table have higher priority than statements that retrieve information from a table. This should ensure that updates are not 'starved' because one issues a lot of heavy queries against a specic table. (You can change this by using LOW PRIORITY with the statement that does the update or HIGH_PRIORITY with the SELECT statement.) Starting from MySQL Version 3.23.7 one can use the max_write_lock_count variable to force MySQL to temporary give all SELECT statements, that wait for a table, a higher priority after a specic number of inserts on a table. Table locking is, however, not very good under the following senario: A client issues a SELECT that takes a long time to run. Another client then issues an UPDATE on a used table. This client will wait until the SELECT is nished. Another client issues another SELECT statement on the same table. As UPDATE has higher priority than SELECT, this SELECT will wait for the UPDATE to nish. It will also wait for the rst SELECT to nish! A thread is waiting for something like full disk, in which case all threads that wants to access the problem table will also be put in a waiting state until more disk space is made available. Some possible solutions to this problem are:

MySQL Reference Manual

Home
Hosting Plans \| Domain Registration \| About Us \| Contact Us \| Site Map Terms of Use \| Privacy Policy \| Guarantees Merchant Accounts

SpiritHost - web hosting for spiritual and education sites SpiritHit.com - Religious and Spiritual Portal Copyright © 2002 Dyntex Group, Inc. All Rights Reserved

Return to Table of Contents	Back to top

Web Hosting: Manuals & FAQ's

1. Unix-Based Web Hosting
2. Unix Dedicated Servers
3. Windows Dedicated Servers
4. CuteFTP User’s Guide
5. CuteHTML User’s Guide
6. WS_FTP Pro User's Guide
7. Miva Order User's Guide
8. Miva Merchant User's Guide