469,306 Members | 1,797 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

Synchronization between 2 PHP threads?

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
Jul 17 '05 #1
8 13034
>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.
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?


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
Jul 17 '05 #2
Eric B. wrote:
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.


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.
Jul 17 '05 #3
> >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.
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.


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.
Jul 17 '05 #4
>> 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).


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...
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.


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.

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


That's kind of old now.


Agreed. I've been telling the hosting company the same thing. :)
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.


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
Jul 17 '05 #5
Eric B. wrote:

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?


Timeouts - deadlocks are improbable but not impossible.

C.
Jul 17 '05 #6
>> 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.


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.


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.
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.


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
Jul 17 '05 #7
"Eric B." <eb****@hotmail.com> wrote in message
news:11***************@www.vif.com...
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.


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.
Jul 17 '05 #8
Eric B. wrote:
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.


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($client_id))
{
mysql_query("insert into $client_table values (...)");
$client_id = mysql_insert_id();
}

//insert a new file record into the files_table
mysql_query("insert 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("select count(*) from $files_table where
client_id=$client_id and PK_file_id <= $inserted_file_id");
list($file_count) = mysql_fetch_row($result);

//all that's left is to insert that incremental id into the files_table
mysql_query("update $files_table set client_file_count=$file_count
where PK_file_id = $inserted_file_id");

Jul 17 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Ivan | last post: by
reply views Thread by Ivan | last post: by
4 posts views Thread by scott | last post: by
12 posts views Thread by emma_middlebrook | last post: by
6 posts views Thread by Chris Ashurst | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.