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
8  13255 
>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_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.
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
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.
> >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.
>> 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
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.
>> 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
"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.
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($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"); This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Efim |
last post by:
Hi,
I have got some problem with sending of events in .NET.
I am using remouting.
The client has got 2 objects for receiving different types of events
(responses and events)
The server has got two objects for sending of these events.
The client opens tcp port 0 to receive events:
if (ChannelServices.GetChannel("tcp") == null)
{
|
by: Ivan |
last post by:
Hi
I have following problem: I'm creating two threads who are performing some
tasks. When one thread finished I would like to restart her again (e.g. new
job). Following example demonstrates that. Problem is that when program is
started many threads are created (see output section), when only two should
be running at any time. Can you please help me to identify me where the
problem is?
Best regards
|
by: Ivan |
last post by:
Hi there
My work on threads continues with more or less success. Here is what I'm
trying to do:
Class JobAgent is incharged for some tasks and when it's called it starts
thread which performs the job. Application contains one list of agents
that are idle at the moment and list of busy agents. In loop it checks if
there
are agents in idle list and if there are some, it starts them.
|
by: Cyrus |
last post by:
I have a question regarding synchronization across multiple threads for a
Hashtable. Currently I have a Threadpool that is creating worker threads
based on requests to read/write to a hashtable. One function of the
Hashtable is to iterate through its keys, which apparently is inherently not
thread-safe. Other functions of the Hashtable include
adding/modifying/deleting.
To solve the synchronization issues I am doing two things:
1. Lock...
|
by: scott |
last post by:
hi all,
Thx to any one that can offer me help, it will be much appreciated.
iv got a multithreaded program and need to use thread synchronization. The
synchronization does not have to work across multiple processes just the
one. I was wondering if any one new which one used the least overhead. Im
at current using mutexes but was wondering if there was something a bit
| | |
by: fei.liu |
last post by:
Hello, in the application I am developing, I am having trouble to
synchronize event triggered actions using 'lock(ob){...};' technique.
Here is a outline of my code:
class C{
int x = 0;
public void incre(){ if(x < 20) x++; }
}
class Form1: WinForm{
|
by: emma_middlebrook |
last post by:
Hi
Say you had N threads doing some jobs (not from a shared queue or
anything like that, they each know how to do their own set of jobs in a
self-contained way). How can you coordinate them so that they all wait
until they've all done one job before starting off on each of their
next jobs.
I have been thinking about this for a day and can't seem to find a
solution.
|
by: Tony Gravagno |
last post by:
I have a class that instantiates two Timer objects that fire at
different intervals. My class can be instantiated within a Windows
Form or from a Windows Service. Actions performed by one of the event
handlers may take longer than the interval for either of the timers,
so it's possible for multiple events to fire "simultaneously" and for
events to queue up. I'm attempting to get the timers to sync on some
reference type object, or use...
|
by: Chris Ashurst |
last post by:
Hi, I'm coming in from a despised Java background, and I'm having some
trouble wrapping my head around sharing an object between multiple
instances of a single class (in simpler terms, I would say imagine a
simple chat server that has to share a list of connected users to each
instance of a connected user).
Usually, I would have a synchronized list instantiated inside each
instance of a client class, which would do the trick, but since...
|
by: putrycydestengier |
last post by:
Hello everyone.
I've got question you may consider simple. Im rather new to C#, and I am not quiet sure about one thing. I've found somewhere, that one of ways of synchronizing access of few threads to one object is by using attribute and ContextBoundObject class derivance. I am not sure how does it work. I mean .. in C/C++, where object of some class may contain OBJECTS of another class, using such an attribute (if it would be possible in...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
