Synchronization between 2 PHP threads?

[Eric B.]

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.

I have considered using semaphores around the code section that does this,
to enforce synchronization , but they are not available in a Windows
environment. So I've thought of using file locks as a semaphore-ish method,
but that is slow and clunky. And would rather avoid that.

I'm using MySQL 3.23 as my DB engine with no InnoDB support, meaning I'm
stuck with MyISAM tables. 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?

I'm open to any other ideas and suggestions as well. Perhaps there is a
much simpler solution that I'm missing?

Thanks!

Eric

[Gordon Burditt]

Re: Synchronization between 2 PHP threads?

>Here is my problem. I have 2 database tables Table A and Table B. Table A[color=blue]
>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.[/color]

make table A have a single column, int auto_increment primary key not null.

insert into tablea values(null);
insert into tableb values(last_ins ert_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.
[color=blue]
>I'm using MySQL 3.23 as my DB engine with no InnoDB support, meaning I'm
>stuck with MyISAM tables.[/color]

auto_increment is your friend.
[color=blue]
>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?[/color]

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

Gordon L. Burditt

[Colin McKinnon]

Re: Synchronization between 2 PHP threads?

Eric B. wrote:
[color=blue]
> Hi,
>[/color]

Hi Eric,
[color=blue]
> 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.
>[/color]

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).
[color=blue]
> 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.
>[/color]

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.
[color=blue]
> I'm using MySQL 3.23 as my DB engine with no InnoDB support,[/color]

That's kind of old now.
[color=blue]
> 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.[/color]

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.


HTH

C.

[Eric B.]

Re: Synchronization between 2 PHP threads?

> >Here is my problem. I have 2 database tables Table A and Table B. Table[color=blue][color=green]
> >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.[/color]
>
> make table A have a single column, int auto_increment primary key not
> null.[/color]

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.
[color=blue]
>[color=green]
>>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?[/color]
>
> user-level locks are a possibility here, assuming all processes dealing
> with the table cooperate. But auto_increment is generally much nicer.[/color]

The only processes accessing the DB are my PHP processes, so as long as I
code it correctly, I can be assured that they will all cooperate. But that
brings me back to the question of what kind of user locks are the best to
use.

[Eric B.]

Re: Synchronization between 2 PHP threads?

>> I'm running into a slight programming issue here that I am hoping someone[color=blue][color=green]
>> 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.
>>[/color]
>
> 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).[/color]

Technically, they aren't PHP threads as such, but rather Apache threads
which each execute individual PHP scripts. Perhaps it would have been
better to specify simultaneous execution of the same PHP code...
[color=blue][color=green]
>> 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.
>>[/color]
>
> 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.[/color]

Am not sure how I would normalize the database. I am willing to change my
DB structure if necessary, but can't come up with an appropriate solution.
My application is the following. Table A is a client table. Table B is a
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.
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. Any select call to
retrieve the last number'ed file would run into the same trouble. Any
suggestions how to better structure the DB? Additionally, I can't be sure
how the files would sort as the file number acutally contains a specific
identifier between the initials and the file number - ie: BR-<client
identifier>-1, etc.

[color=blue][color=green]
>> I'm using MySQL 3.23 as my DB engine with no InnoDB support,[/color]
>
> That's kind of old now.[/color]

Agreed. I've been telling the hosting company the same thing. :)
[color=blue][color=green]
>> 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.[/color]
>
> 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.[/color]

Thanks - must have missed that in the docs. Was kinda tired when I read
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?

Thanks!

Eric

[Colin McKinnon]

Re: Synchronization between 2 PHP threads?

Eric B. wrote:

[color=blue]
> 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.[/color]

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.
[color=blue]
> 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.[/color]

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.
[color=blue]
> 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?
>[/color]

Timeouts - deadlocks are improbable but not impossible.

C.

[Eric B.]

Re: Synchronization between 2 PHP threads?

>> Am not sure how I would normalize the database.[color=blue][color=green]
>> 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.[/color]
>
> 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.[/color]

Yeah, I know that is a killer. That's where the whole issue comes in.
[color=blue]
>[color=green]
>> 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.[/color]
>
> 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.[/color]

Luckily, when I'm talking about files, I'm not actually talking about a
directory-based file. I meant file as in a paper-file number (the kind of
thing you store in a filing cabinet). Although technically, I guess it
could be considered the same thing. The system, among other things, will be
used to help someone number the files more consistently, whilst retaining
their current numbering scheme. Agreed, if the system gets out of sync,
she'll be toast, but as long as she always generates the file number through
the system, it should be okay.
[color=blue][color=green]
>> 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?
>>[/color]
>
> Timeouts - deadlocks are improbable but not impossible.[/color]

Hmmm - interesting. Would I not run into the issue of deadlocks with
discretionary locks as well? Does the Table locking mechanism not have a
timeout as well?

Thanks again!

Eric

[Chung Leong]

Re: Synchronization between 2 PHP threads?

"Eric B." <ebenze@hotmail .com> wrote in message
news:1111645112 .800756@www.vif .com...[color=blue]
> 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[/color]
means[color=blue]
> 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[/color]
A[color=blue]
> contains a counter field that I need to increment every time I save a[/color]
record[color=blue]
> in Table B. Furthermore, I need to save the value of this counter in[/color]
Table[color=blue]
> B whenever I create a new record. Finally, I must ensure that I have[/color]
unique[color=blue]
> 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.[/color]

One of those situations where you need to do the read, the increment, and
the save simultaneously. AFAIK it's no doable in old versions of MySQL. I
think you will have to resort to using a one column table with auto
increment to maintain the counter.

[Ramius]

Re: Synchronization between 2 PHP threads?

Eric B. wrote:[color=blue][color=green][color=darkred]
> > >Here is my problem. I have 2 database tables Table A and Table B.[/color][/color][/color]
Table[color=blue][color=green][color=darkred]
> > >A
> >>contains a counter field that I need to increment every time I save[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> >>record
> >>in Table B. Furthermore, I need to save the value of this counter[/color][/color][/color]
in[color=blue][color=green][color=darkred]
> >>Table
> >>B whenever I create a new record. Finally, I must ensure that I[/color][/color][/color]
have[color=blue][color=green][color=darkred]
> >>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.[/color]
> >
> > make table A have a single column, int auto_increment primary key[/color][/color]
not[color=blue][color=green]
> > null.[/color]
>
> Whoops - should perhaps have been slightly more specific in stating[/color]
that I[color=blue]
> can't use auto_increment for the counter column since each row in[/color]
Table A[color=blue]
> has a different counter value. And the auto_increment field is[/color]
already[color=blue]
> being used for the table's PK, which is completely independent from[/color]
this[color=blue]
> counter value. Similarly, Table B already has an auto-increment[/color]
column as[color=blue]
> well for its PK, but its value is independent from Table A's counter[/color]
value[color=blue]
> as well. Essentially Table A is a client table, and each client has[/color]
it's[color=blue]
> own counter value that gets incremented independently from the other
> clients. Table B uses this counter value to specify a unique file[/color]
number[color=blue]
> (must be incremental) on a per-client basis. A count() function runs[/color]
into[color=blue]
> the same race-condition as just a simple counter as well.
>[/color]

Well, I think I have an idea how it could be accomplished. I didn't
think about it too hard, though, so I might be forgetting something.
Here's the pseudo-code:

//I'm assuming that you will know the client_id if this is an existing
client, so if you don't know it, create a record in the clients table.
if((!isset($cli ent_id))
{
mysql_query("in sert into $client_table values (...)");
$client_id = mysql_insert_id ();
}

//insert a new file record into the files_table
mysql_query("in sert into $files_table (client_id, ...) values
($client_id, ...)");
$inserted_file_ id = mysql_insert_id ();

//now figure out the incremental value for that file
$result = mysql_query("se lect count(*) from $files_table where
client_id=$clie nt_id and PK_file_id <= $inserted_file_ id");
list($file_coun t) = mysql_fetch_row ($result);

//all that's left is to insert that incremental id into the files_table
mysql_query("up date $files_table set client_file_cou nt=$file_count
where PK_file_id = $inserted_file_ id");