Synchronization between 2 PHP threads?

contains a counter field that I need to increment every time I save a record
in Table B. Furthermore, I need to save the value of this counter in Table
B whenever I create a new record. Finally, I must ensure that I have unique
values of this counter in Table B everytime I save.

Under single threaded conditions, this would be simple.
Read TableA counter
Increment TableA counter
Save TableA record
Save TableB record with counter value read in Step #1.
make table A have a single column, int auto_increment primary key not null.

insert into tablea values(null);
insert into tableb values(last_insert_id(), bunch of other stuff, ...)
you can optionally delete the record you inserted into tablea here.

Note that since last_insert_id() operates PER CONNECTION, you
don't have to worry about someone getting a query in between the
insert into tablea and tableb.

You might be able to use auto_increment in table B to dispense with
table A entirely.
I'm using MySQL 3.23 as my DB engine with no InnoDB support, meaning I'm
stuck with MyISAM tables.
auto_increment is your friend.
The only other thing I can think of is using
MySQL to perform the locking, but am not quite sure what my syntax would be.
From my understanding a MySQL Read lock would not prevent another thread
from reading the table - just from inserting data. So that wouldn't be
appropriate. Would a MySQL Write lock on Table A prevent another thread
from reading Table A until the lock is released?

Hi,

Hi Eric,
I'm running into a slight programming issue here that I am hoping someone
can help me with. I am developping a web-based PHP application, which
means I have to consider multiple threads of the same PHP script running
at the same time.

You need to be a bit more careful with your language here. The term
'threads' does not mean what it used to - particularly in the context you
are applying it. PHP doesn't do threads (in the 'new' sense). (Yes, it
*should* be thread aware - but that's a different issue). (Yes, you can
fork a PHP - but that creates a new process, not a new thread).
Here is my problem. I have 2 database tables Table A and Table B. Table
A contains a counter field that I need to increment every time I save a
record
in Table B. Furthermore, I need to save the value of this counter in
Table
B whenever I create a new record. Finally, I must ensure that I have
unique values of this counter in Table B everytime I save.

Ahh, the my-database-is-not-normalized-how-do-I-make-my-application-work
-without-fixing-the-cause problem.

Controlling access to finite resources is one of the classic computing
problems. There are any number of ways of solving it, however the most
apposite, if you really can't normalize your database, is to use database
locking - avoid persistent DB connections, and, if you're using MySQL, I'd
recomend discretionary locks (GET_LOCK/RELEASE_LOCK) rather than table
locks.
I'm using MySQL 3.23 as my DB engine with no InnoDB support,
That's kind of old now.
The only other thing I can think of is using
MySQL to perform the locking, but am not quite sure what my syntax would
be. From my understanding a MySQL Read lock would not prevent another
thread
from reading the table - just from inserting data.

A
contains a counter field that I need to increment every time I save a
record
in Table B. Furthermore, I need to save the value of this counter in
Table
B whenever I create a new record. Finally, I must ensure that I have
unique
values of this counter in Table B everytime I save.

Under single threaded conditions, this would be simple.
Read TableA counter
Increment TableA counter
Save TableA record
Save TableB record with counter value read in Step #1.
make table A have a single column, int auto_increment primary key not
null.

Whoops - should perhaps have been slightly more specific in stating that I
can't use auto_increment for the counter column since each row in Table A
has a different counter value. And the auto_increment field is already
being used for the table's PK, which is completely independent from this
counter value. Similarly, Table B already has an auto-increment column as
well for its PK, but its value is independent from Table A's counter value
as well. Essentially Table A is a client table, and each client has it's
own counter value that gets incremented independently from the other
clients. Table B uses this counter value to specify a unique file number
(must be incremental) on a per-client basis. A count() function runs into
the same race-condition as just a simple counter as well.

The only other thing I can think of is using
MySQL to perform the locking, but am not quite sure what my syntax would
be.
From my understanding a MySQL Read lock would not prevent another thread
from reading the table - just from inserting data. So that wouldn't be
appropriate. Would a MySQL Write lock on Table A prevent another thread
from reading Table A until the lock is released?

user-level locks are a possibility here, assuming all processes dealing
with the table cooperate. But auto_increment is generally much nicer.

can help me with. I am developping a web-based PHP application, which
means I have to consider multiple threads of the same PHP script running
at the same time.

You need to be a bit more careful with your language here. The term
'threads' does not mean what it used to - particularly in the context you
are applying it. PHP doesn't do threads (in the 'new' sense). (Yes, it
*should* be thread aware - but that's a different issue). (Yes, you can
fork a PHP - but that creates a new process, not a new thread).

Here is my problem. I have 2 database tables Table A and Table B. Table
A contains a counter field that I need to increment every time I save a
record
in Table B. Furthermore, I need to save the value of this counter in
Table
B whenever I create a new record. Finally, I must ensure that I have
unique values of this counter in Table B everytime I save.

Ahh, the my-database-is-not-normalized-how-do-I-make-my-application-work
-without-fixing-the-cause problem.

Controlling access to finite resources is one of the classic computing
problems. There are any number of ways of solving it, however the most
apposite, if you really can't normalize your database, is to use database
locking - avoid persistent DB connections, and, if you're using MySQL, I'd
recomend discretionary locks (GET_LOCK/RELEASE_LOCK) rather than table
locks.

I'm using MySQL 3.23 as my DB engine with no InnoDB support,

That's kind of old now.

The only other thing I can think of is using
MySQL to perform the locking, but am not quite sure what my syntax would
be. From my understanding a MySQL Read lock would not prevent another
thread
from reading the table - just from inserting data.

So use a write lock - from the mysql manual:
If a thread obtains a WRITE lock on a table, then only the thread holding
the lock can read from or write to the table. Other threads are blocked.

Am not sure how I would normalize the database.
file table. Each client can have multiple files, but a file can only be
assocaited to a single client. 1->many relationship. Each file, however,
must be sequentially numbered on a per client basis; that is, each file
would have the client's initials, for example, followed by the file
number.
OK, that constraint is a killer - it means that your data doesn't fit the
relational model. There are tricks on other DBMS to solve this (sequences)
but not on MySQL.
ie: client Bob Ross would have files BR-1, BR-2, BR-3.... client John
Smith
would have files JS-1, JS-2, JS-3, etc... I can't figure out how on
earth
to use an auto-increment field for this type of case.
You could fudge it be setting the next value for MySQL, but again not a good
idea.

You're looking for is the last number allocated for that Client and adding
1. Simple with locking. But you're going to be fscked if the database and
directory get out of sync though....and there are a lot of other nasties
which could occur if you don't code it carefully.

Best advice would be to loose the file-naming requirement. It's the cause of
all your problems, requires a lot of code to work around and undermines the
maintainability of your app.
them last night. Given that a write lock would prevent all read accesses
to
the table, why use discretionary locks over table locks? What advantages
would I get with discretionary locks?

file table. Each client can have multiple files, but a file can only be
assocaited to a single client. 1->many relationship. Each file,
however,
must be sequentially numbered on a per client basis; that is, each file
would have the client's initials, for example, followed by the file
number.
OK, that constraint is a killer - it means that your data doesn't fit the
relational model. There are tricks on other DBMS to solve this (sequences)
but not on MySQL.

Yeah, I know that is a killer. That's where the whole issue comes in.

ie: client Bob Ross would have files BR-1, BR-2, BR-3.... client John
Smith
would have files JS-1, JS-2, JS-3, etc... I can't figure out how on
earth
to use an auto-increment field for this type of case.

You could fudge it be setting the next value for MySQL, but again not a
good
idea.

You're looking for is the last number allocated for that Client and adding
1. Simple with locking. But you're going to be fscked if the database and
directory get out of sync though....and there are a lot of other nasties
which could occur if you don't code it carefully.

them last night. Given that a write lock would prevent all read accesses
to
the table, why use discretionary locks over table locks? What advantages
would I get with discretionary locks?

Timeouts - deadlocks are improbable but not impossible.

Hi,

I'm running into a slight programming issue here that I am hoping someone
can help me with. I am developping a web-based PHP application, which means I have to consider multiple threads of the same PHP script running at the
same time.

Here is my problem. I have 2 database tables Table A and Table B. Table A contains a counter field that I need to increment every time I save a record in Table B. Furthermore, I need to save the value of this counter in Table B whenever I create a new record. Finally, I must ensure that I have unique values of this counter in Table B everytime I save.

Under single threaded conditions, this would be simple.
Read TableA counter
Increment TableA counter
Save TableA record
Save TableB record with counter value read in Step #1.

Simple. However, when I look at this from a multi-threaded point of view,
this concept obviously doesn't work out anymore. If two users
simultaneously read the TableA counter they will both write a record to
Table B with the same counter value. So I would end up with 2 records in
Table B with the same value. This cannot be allowed.

Here is my problem. I have 2 database tables Table A and Table B. TableA
contains a counter field that I need to increment every time I save arecord
in Table B. Furthermore, I need to save the value of this counter inTable
B whenever I create a new record. Finally, I must ensure that I haveunique
values of this counter in Table B everytime I save.

Under single threaded conditions, this would be simple.
Read TableA counter
Increment TableA counter
Save TableA record
Save TableB record with counter value read in Step #1.
make table A have a single column, int auto_increment primary key not null.

Whoops - should perhaps have been slightly more specific in stating

that I can't use auto_increment for the counter column since each row in Table A has a different counter value. And the auto_increment field is already being used for the table's PK, which is completely independent from this counter value. Similarly, Table B already has an auto-increment column as well for its PK, but its value is independent from Table A's counter value as well. Essentially Table A is a client table, and each client has it's own counter value that gets incremented independently from the other
clients. Table B uses this counter value to specify a unique file number (must be incremental) on a per-client basis. A count() function runs into the same race-condition as just a simple counter as well.