SpirirHost Support Centre: MySQL Reference Manual p.485

<< 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 6: MySQL Language Reference 485 If you are using a table handler in MySQL that doesn't support transactions, you must use LOCK TABLES if you want to ensure that no other thread comes between a SELECT and an UPDATE. The example shown here requires LOCK TABLES in order to execute safely: mysql> LOCK TABLES trans READ, customer WRITE; mysql> SELECT SUM(value) FROM trans WHERE customer_id=some_id; mysql> UPDATE customer SET total_value=sum_from_previous_statement -> WHERE customer_id=some_id; mysql> UNLOCK TABLES; Without LOCK TABLES, there is a chance that another thread might insert a new row in the trans table between execution of the SELECT and UPDATE statements. By using incremental updates (UPDATE customer SET value=value+new_value) or the LAST_INSERT_ID() function, you can avoid using LOCK TABLES in many cases. You can also solve some cases by using the user-level lock functions GET_LOCK() and RELEASE_LOCK(). These locks are saved in a hash table in the server and implemented with pthread_mutex_lock() and pthread_mutex_unlock() for high speed. See Section 6.3.6.2 [Miscellaneous functions], page 439. See Section 5.3.1 [Internal locking], page 354, for more information on locking policy. You can lock all tables in all databases with read locks with the FLUSH TABLES WITH READ LOCK command. See Section 4.5.3 [FLUSH], page 248. This is very convenient way to get backups if you have a lesystem, like Veritas, that can take snapshots in time. NOTE: LOCK TABLES is not transaction-safe and will automatically commit any active trans- actions before attempting to lock the tables. 6.7.3 SET TRANSACTION Syntax SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } Sets the transaction isolation level for the global, whole session or the next transaction. The default behaviour is to set the isolation level for the next (not started) transaction. If you use the GLOBAL keyword, the statement sets the default transaction level globally for all new connections created from that point on. You will need the SUPER privilege to do do this. Using the SESSION keyword sets the default transaction level for all future transactions performed on the current connection. You can set the default global isolation level for mysqld with --transaction-isolation=.... See Section 4.1.1 [Command-line options], page 181. 6.8 MySQL Full-text Search As of Version 3.23.23, MySQL has support for full-text indexing and searching. Full-text indexes in MySQL are an index of type FULLTEXT. FULLTEXT indexes can be created from VARCHAR and TEXT columns at CREATE TABLE time or added later with ALTER TABLE or

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