Oracle 5.0, Справочное руководство

Страница: 45 / 3232

MySQL Differences from Standard SQL
25
it releases the lock. With
INSERT DELAYED
, you can write inserts that go into a local queue until
the locks are released, without having the client wait for the insert to complete. See Section 8.7.3,
“Concurrent Inserts” , and Section 13.2.5.2, “
INSERT DELAYED
Syntax” .
“Atomic,” in the sense that we mean it, is nothing magical. It only means that you can be sure that while
each specific update is running, no other user can interfere with it, and there can never be an automatic
rollback (which can happen with transactional tables if you are not very careful). MySQL Server also
guarantees that there are no dirty reads.
Following are some techniques for working with nontransactional tables:
• Loops that need transactions normally can be coded with the help of
LOCK TABLES
, and you don't
need cursors to update records on the fly.
• To avoid using
ROLLBACK
, you can employ the following strategy:
1. Use
LOCK TABLES
to lock all the tables you want to access.
2. Test the conditions that must be true before performing the update.
3. Update if the conditions are satisfied.
4. Use
UNLOCK TABLES
to release your locks.
This is usually a much faster method than using transactions with possible rollbacks, although not
always. The only situation this solution doesn't handle is when someone kills the threads in the
middle of an update. In that case, all locks are released but some of the updates may not have been
executed.
• You can also use functions to update records in a single operation. You can get a very efficient
application by using the following techniques:
• Modify columns relative to their current value.
• Update only those columns that actually have changed.
For example, when we are updating customer information, we update only the customer data that
has changed and test only that none of the changed data, or data that depends on the changed data,
has changed compared to the original row. The test for changed data is done with the
WHERE
clause
in the
UPDATE
statement. If the record wasn't updated, we give the client a message: “Some of the
data you have changed has been changed by another user.” Then we show the old row versus the
new row in a window so that the user can decide which version of the customer record to use.
This gives us something that is similar to column locking but is actually even better because we only
update some of the columns, using values that are relative to their current values. This means that
typical
UPDATE
statements look something like these:
UPDATE tablename SET pay_back=p125;
UPDATE customer
SET
customer_date='current_date',
address='new address',
phone='new phone',
money_owed_to_us=money_owed_to_us-125
WHERE
customer_id=id AND address='old address' AND phone='old phone';
This is very efficient and works even if another client has changed the values in the
pay_back
or
money_owed_to_us
columns.
• In many cases, users have wanted
LOCK TABLES
or
ROLLBACK
for the purpose of managing
unique identifiers. This can be handled much more efficiently without locking or rolling back by