Hello,
I created website using PHP5+MySQL. It contains 3 pages:
1. enter_announcement.php - enter/change real estate announcement
2. submit_announcement.php - add/modify announcement in database
3. summary.php - show summary information.
To be precise: the page enter_announcement.php submits to
submit_announcement.php which redirects (at the end) to summary.php.
Announcement number consist of 3 parts: year, month, sequential number
in month.
Here is draft of submit_announcement.php:

START TRANSACTION
LOCK TABLES announcements WRITE
if (empty($_POST['full_announcement_no'])) { // new announcement
$announcement = new Announcement;
$announcement->initializeWithPOST();
INSERT INTO announcements with new number MAX+1
} else { // change announcement
$announcement = new Announcement;
$announcement->initializeWithPOST();
$announcement->initializeWithNumber($_POST['full_announcement_no']));
UPDATE announcements
}
UNLOCK TABLES
COMMIT
header('Location: summary.php');

The problem is that I am not sure if inside transaction the command
LOCK TABLES is necessary. I lock announcements table because of
concurrency (many users) of announcement number generation (SELECT
MAX(no)+1 FROM announcements WHERE year=current AND month=current).
Please help. Thanks in advance.

On 12/20/2010 3:34 PM, Jivanmukta wrote:
Just use an auto_increment column and don't worry about MAX+1. Then you
don't need to lock tables.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

I cannot use auto increment because announcement number consist of 3
parts: year, month, sequential number within month. After month
changes I need to start new sequence.

On Tue, 21 Dec 2010 01:25:48 -0800 (PST), Jivanmukta wrote:
Why?

I mean, it's your page/system. Why hang onto a design choice that's
inconveniencing you? If it's some kind of archiving/indexing by month
that you want to do, it's pretty simple to layer that on top of an
autoincrement.

El 20/12/2010 21:34, Jivanmukta escribió/wrote:
Transactions and table locking solve different problems. Transactions
are a mechanism to "undo" a series of modifications if one of them
fails. Table locking are a mechanism to prevent other sessions from
reading or altering a table.

If you are running a single query you don't actually need transactions.
However, if you need to use both, be aware that the syntax MySQL expects
is very tricky. You cannot use "START TRANSACTION", you need "SET
autocommit=0" instead.


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--