Dyntex.net Support Centre: MySQL Reference Manual p.523

<< 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 7: MySQL Table Types 523 v SELECT * FROM t; empty set COMMIT; SELECT * FROM t; empty set; COMMIT; SELECT * FROM t; --------------------- | 1 | 2 | --------------------- Thus user A sees the row inserted by B only when B has committed the insert, and A has committed his own transaction so that the timepoint is advanced past the commit of B. If you want to see the \freshest" state of the database, you should use a locking read: SELECT * FROM t LOCK IN SHARE MODE; 7.5.8.7 How to cope with deadlocks? Deadlocks are a classic problem in transactional databases, but they are not dangerous unless they are so frequent that you cannot run certain transactions at all. Normally you have to write your applications so that they are always prepared to re-issue a transaction if it gets rolled back because of a deadlock. InnoDB uses automatic row level locking. You can get deadlocks even in the case of trans- actions which just insert or delete a single row. That is because these operations are not really 'atomic': they automatically set locks on the (possibly several) index records of the row inserted/deleted. You can cope with deadlocks and reduce the number of them with the following tricks: Always be prepared to re-issue a transaction if it fails in a deadlock. Deadlocks are not dangerous. Just try again. Commit your transactions often. Small transactions are less prone to collide. Access your tables and rows in a xed order. Then transactions will form nice queues, and do not deadlock. Use less locking: if you can aord a SELECT to return data from an old snapshot, do not add the clause FOR UPDATE or LOCK IN SHARE MODE to it. If nothing helps, serialize your transactions with table level locks: LOCK TABLES t1 WRITE, t2 READ, ... ; [do something with tables t1 and t2 here]; UNLOCK TABLES Table level locks make you transactions to queue nicely, and deadlocks are avoided. Note that LOCK TABLES implicitly starts a transaction, just like the command BEGIN, and UNLOCK TABLES implicitly ends the transaction in a COMMIT. 7.5.9 Performance Tuning Tips 1. If the Unix `top' or the Windows `Task Manager' shows that the CPU usage percentage with your workload is less than 70%, your workload is probably disk-bound. Maybe you are

MySQL Reference Manual

Our Partners

SpiritHost - web hosting for spiritual and education sites
SpiritHit.com - Religious and Spiritual Portal

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