Skip to Main Content
MySQL Stored Procedure Programming
book

MySQL Stored Procedure Programming

by Guy Harrison, Steven Feuerstein
March 2006
Intermediate to advanced content levelIntermediate to advanced
640 pages
17h 8m
English
O'Reilly Media, Inc.
Content preview from MySQL Stored Procedure Programming

Transaction Design Guidelines

A well-designed transaction should have the following properties:

  • The integrity of the database will be maintained at all times.

  • The duration and coverage of locks will be minimized. Locks should be applied to as few rows as possible and maintained for the shortest possible duration.

  • Rollbacks will be minimal—transactions that eventually issue a rollback have needlessly consumed resources.

  • User expectations about the persistence of data will be met. For instance, a user who clicks a Save or Apply button has a reasonable expectation that the data will not disappear if he subsequently clicks Cancel on another page.

To achieve these goals, we recommend the following general guidelines for transaction design:

Keep transactions small

A transaction should generally include as small a logical unit of work as possible to reduce the duration of locks.

Avoid a transaction design that encourages rollbacks

For instance, rather than trying an insert and rolling back if there is a "duplicate key" error, check for the existence of the key value before issuing the DML.

Avoid savepoints whenever possible

The existence of a savepoint may indicate that you have failed to check for success criteria before issuing a DML statement and may indicate a transaction design that encourages rollbacks.

By default, rely on a pessimistic locking strategy

Lock rows that you SELECT if the results of the SELECT statement affect DML executed later in the transaction. Pessimistic locking is easy ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Concurrency: Locking and Transactions for MySQL Developers and DBAs

MySQL Concurrency: Locking and Transactions for MySQL Developers and DBAs

Jesper Wisborg Krogh
MySQL 8 Administrator???s Guide

MySQL 8 Administrator???s Guide

Chintan Mehta, Hetal Oza, Subhash Shah, Ravi Shah
MySQL Cookbook, 4th Edition

MySQL Cookbook, 4th Edition

Sveta Smirnova, Alkin Tezuysal
Learning MySQL, 2nd Edition

Learning MySQL, 2nd Edition

Vinicius M. Grippa, Sergey Kuzmichev

Publisher Resources

ISBN: 0596100892Supplemental ContentErrata Page