By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,746 Members | 1,924 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,746 IT Pros & Developers. It's quick & easy.

Concurrency Question

P: n/a
Hello everyone,

I have read many threads about concurrency issues, and I think I
understand some of the pieces, but not the whole picture.

I believe I am like many people using php: developing a personal
website on a windows machine, that will be hosted on a shared linux
server. Financial reality makes it impossible to think of a dedicated
server, and I'm not ready to jump into developing on a linux box.

I am looking at how to efficiently code some database operations,
while using a MyISAM mySQL database. The options seem to be:

1. Use flock() in the code to lock a semaphore file. This sounded
fine, until I read that different systems respond to flock()
differently, and we get into making temporary directories and checking
if they exist. This seems difficult to develop on a local machine,
then play around on the shared server environment.

2. Use LOCK TABLES in mysql. This would be fine, but results in
locking tables more often and more restrictively than they need to be
locked.

3. Use a lockStatus table. I make a row that's called
"registerNewUser" and set it to "locked"; do my work; then set
"registerNewUser" back to "unlocked". This is fine, but if the user
clicks out or the steps are not completed, "registerNewUser" remains
set to "locked".
What I am leaning towards is a LOCK TABLES approach that tries to
mimic the semaphore file approach.
A- I create an empty (semaphore) table called registerNewUser.
B- I get a write lock on this table, and a read lock on all tables
needed for the subsequent work.
C- I release the locks.

Any other operation that would conflict with registering a new user
would also get a lock on the semaphore table registerNewUser. What I
think this achieves is that any other user can read from the tables
that are being written to (which is not usually a problem - if it is,
that user gets a read lock on the appropriate semaphore table). Also,
if the connection is lost, the lock is released.

Can anyone shoot holes in this thinking? Am I right in thinking this
reasonably imitates the semaphore file approach?

May 8 '07 #1
Share this Question
Share on Google+
10 Replies


P: n/a
e_*******@hotmail.com wrote:
Hello everyone,

I have read many threads about concurrency issues, and I think I
understand some of the pieces, but not the whole picture.

I believe I am like many people using php: developing a personal
website on a windows machine, that will be hosted on a shared linux
server. Financial reality makes it impossible to think of a dedicated
server, and I'm not ready to jump into developing on a linux box.

I am looking at how to efficiently code some database operations,
while using a MyISAM mySQL database. The options seem to be:

1. Use flock() in the code to lock a semaphore file. This sounded
fine, until I read that different systems respond to flock()
differently, and we get into making temporary directories and checking
if they exist. This seems difficult to develop on a local machine,
then play around on the shared server environment.

2. Use LOCK TABLES in mysql. This would be fine, but results in
locking tables more often and more restrictively than they need to be
locked.

3. Use a lockStatus table. I make a row that's called
"registerNewUser" and set it to "locked"; do my work; then set
"registerNewUser" back to "unlocked". This is fine, but if the user
clicks out or the steps are not completed, "registerNewUser" remains
set to "locked".
What I am leaning towards is a LOCK TABLES approach that tries to
mimic the semaphore file approach.
A- I create an empty (semaphore) table called registerNewUser.
B- I get a write lock on this table, and a read lock on all tables
needed for the subsequent work.
C- I release the locks.

Any other operation that would conflict with registering a new user
would also get a lock on the semaphore table registerNewUser. What I
think this achieves is that any other user can read from the tables
that are being written to (which is not usually a problem - if it is,
that user gets a read lock on the appropriate semaphore table). Also,
if the connection is lost, the lock is released.

Can anyone shoot holes in this thinking? Am I right in thinking this
reasonably imitates the semaphore file approach?
Maybe over thinking a bit... the idea behind LOCK TABLES is that
MySQL will que the requests as they come in. You want to spend as little
time as possible processing locked tables as you can. Don't do anything
that will take considerable amounts of time. Sometimes you don't even
need LOCK TABLES. My process is (simplified):

table users = id big int, email char(), first char(), last char(),
status enum('U','R','D'), date date()

1) new user registers...
2) is there a match in the db already? ... options
3) no match found, add info, get LAST_INSERT_ID(). status is 'U'
unregistered.
4) continue with other processing with newly acquired id
5) at this point you have a tag (id) to the person. I usually do email
verification such as a clickable/cut/paste url like
http://www.yourdomain.com/complete_r...on.php?id=????
6) when the person follows the link, I UPDATE their account to 'R' for
registered. I have ('U','R','D'). 'D' for deletable. Unregistered
accounts over ??? days old.

INSERTs are atomic (won't conflict with each other) and LAST_INSERT_ID()
is connection specific (no cross-over). Basically, no one ends up with
the same id. And besides, your options 1 and 2 only provide the
functionality of what MySQL is already doing for you behind the scenes.

Norm
May 8 '07 #2

P: n/a
On May 8, 4:24 am, Norman Peelman <npeel...@cfl.rr.comwrote:
e_matt...@hotmail.com wrote:
Hello everyone,
I have read many threads about concurrency issues, and I think I
understand some of the pieces, but not the whole picture.
I believe I am like many people using php: developing a personal
website on a windows machine, that will be hosted on a shared linux
server. Financial reality makes it impossible to think of a dedicated
server, and I'm not ready to jump into developing on a linux box.
I am looking at how to efficiently code some database operations,
while using a MyISAM mySQL database. The options seem to be:
1. Use flock() in the code to lock a semaphore file. This sounded
fine, until I read that different systems respond to flock()
differently, and we get into making temporary directories and checking
if they exist. This seems difficult to develop on a local machine,
then play around on the shared server environment.
2. Use LOCK TABLES in mysql. This would be fine, but results in
locking tables more often and more restrictively than they need to be
locked.
3. Use a lockStatus table. I make a row that's called
"registerNewUser" and set it to "locked"; do my work; then set
"registerNewUser" back to "unlocked". This is fine, but if the user
clicks out or the steps are not completed, "registerNewUser" remains
set to "locked".
What I am leaning towards is a LOCK TABLES approach that tries to
mimic the semaphore file approach.
A- I create an empty (semaphore) table called registerNewUser.
B- I get a write lock on this table, and a read lock on all tables
needed for the subsequent work.
C- I release the locks.
Any other operation that would conflict with registering a new user
would also get a lock on the semaphore table registerNewUser. What I
think this achieves is that any other user can read from the tables
that are being written to (which is not usually a problem - if it is,
that user gets a read lock on the appropriate semaphore table). Also,
if the connection is lost, the lock is released.
Can anyone shoot holes in this thinking? Am I right in thinking this
reasonably imitates the semaphore file approach?

Maybe over thinking a bit... the idea behind LOCK TABLES is that
MySQL will que the requests as they come in. You want to spend as little
time as possible processing locked tables as you can. Don't do anything
that will take considerable amounts of time. Sometimes you don't even
need LOCK TABLES. My process is (simplified):

table users = id big int, email char(), first char(), last char(),
status enum('U','R','D'), date date()

1) new user registers...
2) is there a match in the db already? ... options
3) no match found, add info, get LAST_INSERT_ID(). status is 'U'
unregistered.
4) continue with other processing with newly acquired id
5) at this point you have a tag (id) to the person. I usually do email
verification such as a clickable/cut/paste url likewww.yourdomain.com/complete_registration.php?id=????
6) when the person follows the link, I UPDATE their account to 'R' for
registered. I have ('U','R','D'). 'D' for deletable. Unregistered
accounts over ??? days old.

INSERTs are atomic (won't conflict with each other) and LAST_INSERT_ID()
is connection specific (no cross-over). Basically, no one ends up with
the same id. And besides, your options 1 and 2 only provide the
functionality of what MySQL is already doing for you behind the scenes.

Norm
Use PDO.
$db->beginTransaction();
$db->exec($query);
$db->commit();
if( some condition )
{
$db->rollBack();
}
but i dont really see you have a massive problem, its not about shared
hosting, its about many concurrent users of yours, operating on the
same table. Fix it with logic in your code using PDOs abilities.

May 8 '07 #3

P: n/a
On May 8, 2:45 am, shimmyshack <matt.fa...@gmail.comwrote:
On May 8, 4:24 am, Norman Peelman <npeel...@cfl.rr.comwrote:
e_matt...@hotmail.com wrote:
Hello everyone,
I have read many threads about concurrency issues, and I think I
understand some of the pieces, but not the whole picture.
I believe I am like many people using php: developing a personal
website on a windows machine, that will be hosted on a shared linux
server. Financial reality makes it impossible to think of a dedicated
server, and I'm not ready to jump into developing on a linux box.
I am looking at how to efficiently code some database operations,
while using a MyISAM mySQL database. The options seem to be:
1. Use flock() in the code to lock a semaphore file. This sounded
fine, until I read that different systems respond to flock()
differently, and we get into making temporary directories and checking
if they exist. This seems difficult to develop on a local machine,
then play around on the shared server environment.
2. Use LOCK TABLES in mysql. This would be fine, but results in
locking tables more often and more restrictively than they need to be
locked.
3. Use a lockStatus table. I make a row that's called
"registerNewUser" and set it to "locked"; do my work; then set
"registerNewUser" back to "unlocked". This is fine, but if the user
clicks out or the steps are not completed, "registerNewUser" remains
set to "locked".
What I am leaning towards is a LOCK TABLES approach that tries to
mimic the semaphore file approach.
A- I create an empty (semaphore) table called registerNewUser.
B- I get a write lock on this table, and a read lock on all tables
needed for the subsequent work.
C- I release the locks.
Any other operation that would conflict with registering a new user
would also get a lock on the semaphore table registerNewUser. What I
think this achieves is that any other user can read from the tables
that are being written to (which is not usually a problem - if it is,
that user gets a read lock on the appropriate semaphore table). Also,
if the connection is lost, the lock is released.
Can anyone shoot holes in this thinking? Am I right in thinking this
reasonably imitates the semaphore file approach?
Maybe over thinking a bit... the idea behind LOCK TABLES is that
MySQL will que the requests as they come in. You want to spend as little
time as possible processing locked tables as you can. Don't do anything
that will take considerable amounts of time. Sometimes you don't even
need LOCK TABLES. My process is (simplified):
table users = id big int, email char(), first char(), last char(),
status enum('U','R','D'), date date()
1) new user registers...
2) is there a match in the db already? ... options
3) no match found, add info, get LAST_INSERT_ID(). status is 'U'
unregistered.
4) continue with other processing with newly acquired id
5) at this point you have a tag (id) to the person. I usually do email
verification such as a clickable/cut/paste url likewww.yourdomain.com/complete_registration.php?id=????
6) when the person follows the link, I UPDATE their account to 'R' for
registered. I have ('U','R','D'). 'D' for deletable. Unregistered
accounts over ??? days old.
INSERTs are atomic (won't conflict with each other) and LAST_INSERT_ID()
is connection specific (no cross-over). Basically, no one ends up with
the same id. And besides, your options 1 and 2 only provide the
functionality of what MySQL is already doing for you behind the scenes.
Norm

Use PDO.
$db->beginTransaction();
$db->exec($query);
$db->commit();
if( some condition )
{
$db->rollBack();}

but i dont really see you have a massive problem, its not about shared
hosting, its about many concurrent users of yours, operating on the
same table. Fix it with logic in your code using PDOs abilities.
That is very interesting; I had not heard of PDO until now. I mention
shared hosting because the host I'm most familiar with doesn't allow
certain db engines, because of the potential for bad coding to eat up
resources with that engine. So I'm stuck for now with MyISAM (no
transaction support). I find this warning when reading about PDO at
http://us.php.net/manual/en/ref.pdo-mysql.php:

"Beware: Some MySQL table types (storage engines) do not support
transactions. When writing transactional database code using a table
type that does not support transactions, MySQL will pretend that a
transaction was initiated successfully. In addition, any DDL queries
issued will implicitly commit any pending transactions."

Does this mean PDO emulates transactions for engines that do not
support transactions? Or is it a warning not to try using
transactions in PDO when using an engine that doesn't support
transactions?

May 8 '07 #4

P: n/a
e_*******@hotmail.com wrote:
On May 8, 2:45 am, shimmyshack <matt.fa...@gmail.comwrote:
>On May 8, 4:24 am, Norman Peelman <npeel...@cfl.rr.comwrote:
>>e_matt...@hotmail.com wrote:
Hello everyone,
I have read many threads about concurrency issues, and I think I
understand some of the pieces, but not the whole picture.
I believe I am like many people using php: developing a personal
website on a windows machine, that will be hosted on a shared linux
server. Financial reality makes it impossible to think of a dedicated
server, and I'm not ready to jump into developing on a linux box.
I am looking at how to efficiently code some database operations,
while using a MyISAM mySQL database. The options seem to be:
1. Use flock() in the code to lock a semaphore file. This sounded
fine, until I read that different systems respond to flock()
differently, and we get into making temporary directories and checking
if they exist. This seems difficult to develop on a local machine,
then play around on the shared server environment.
2. Use LOCK TABLES in mysql. This would be fine, but results in
locking tables more often and more restrictively than they need to be
locked.
3. Use a lockStatus table. I make a row that's called
"registerNewUser" and set it to "locked"; do my work; then set
"registerNewUser" back to "unlocked". This is fine, but if the user
clicks out or the steps are not completed, "registerNewUser" remains
set to "locked".
What I am leaning towards is a LOCK TABLES approach that tries to
mimic the semaphore file approach.
A- I create an empty (semaphore) table called registerNewUser.
B- I get a write lock on this table, and a read lock on all tables
needed for the subsequent work.
C- I release the locks.
Any other operation that would conflict with registering a new user
would also get a lock on the semaphore table registerNewUser. What I
think this achieves is that any other user can read from the tables
that are being written to (which is not usually a problem - if it is,
that user gets a read lock on the appropriate semaphore table). Also,
if the connection is lost, the lock is released.
Can anyone shoot holes in this thinking? Am I right in thinking this
reasonably imitates the semaphore file approach?
Maybe over thinking a bit... the idea behind LOCK TABLES is that
MySQL will que the requests as they come in. You want to spend as little
time as possible processing locked tables as you can. Don't do anything
that will take considerable amounts of time. Sometimes you don't even
need LOCK TABLES. My process is (simplified):
table users = id big int, email char(), first char(), last char(),
status enum('U','R','D'), date date()
1) new user registers...
2) is there a match in the db already? ... options
3) no match found, add info, get LAST_INSERT_ID(). status is 'U'
unregistered.
4) continue with other processing with newly acquired id
5) at this point you have a tag (id) to the person. I usually do email
verification such as a clickable/cut/paste url likewww.yourdomain.com/complete_registration.php?id=????
6) when the person follows the link, I UPDATE their account to 'R' for
registered. I have ('U','R','D'). 'D' for deletable. Unregistered
accounts over ??? days old.
INSERTs are atomic (won't conflict with each other) and LAST_INSERT_ID()
is connection specific (no cross-over). Basically, no one ends up with
the same id. And besides, your options 1 and 2 only provide the
functionality of what MySQL is already doing for you behind the scenes.
Norm
Use PDO.
$db->beginTransaction();
$db->exec($query);
$db->commit();
if( some condition )
{
$db->rollBack();}

but i dont really see you have a massive problem, its not about shared
hosting, its about many concurrent users of yours, operating on the
same table. Fix it with logic in your code using PDOs abilities.

That is very interesting; I had not heard of PDO until now. I mention
shared hosting because the host I'm most familiar with doesn't allow
certain db engines, because of the potential for bad coding to eat up
resources with that engine. So I'm stuck for now with MyISAM (no
transaction support). I find this warning when reading about PDO at
http://us.php.net/manual/en/ref.pdo-mysql.php:

"Beware: Some MySQL table types (storage engines) do not support
transactions. When writing transactional database code using a table
type that does not support transactions, MySQL will pretend that a
transaction was initiated successfully. In addition, any DDL queries
issued will implicitly commit any pending transactions."

Does this mean PDO emulates transactions for engines that do not
support transactions? Or is it a warning not to try using
transactions in PDO when using an engine that doesn't support
transactions?
It's a warning that if you do use transactions they will be ignored.

But the real question here is - what problem are you trying to solve?
Two people registering at the same time with the same userid, for
instance? If so, it's not a problem - just put a unique index (or
primary key) on the user name column. The first user will get
registered, the second will fail with a duplicate id.

Or is there another problem you're trying to solve?
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
May 8 '07 #5

P: n/a
On May 8, 4:10 pm, e_matt...@hotmail.com wrote:
On May 8, 2:45 am, shimmyshack <matt.fa...@gmail.comwrote:
On May 8, 4:24 am, Norman Peelman <npeel...@cfl.rr.comwrote:
e_matt...@hotmail.com wrote:
Hello everyone,
I have read many threads about concurrency issues, and I think I
understand some of the pieces, but not the whole picture.
I believe I am like many people using php: developing a personal
website on a windows machine, that will be hosted on a shared linux
server. Financial reality makes it impossible to think of a dedicated
server, and I'm not ready to jump into developing on a linux box.
I am looking at how to efficiently code some database operations,
while using a MyISAM mySQL database. The options seem to be:
1. Use flock() in the code to lock a semaphore file. This sounded
fine, until I read that different systems respond to flock()
differently, and we get into making temporary directories and checking
if they exist. This seems difficult to develop on a local machine,
then play around on the shared server environment.
2. Use LOCK TABLES in mysql. This would be fine, but results in
locking tables more often and more restrictively than they need to be
locked.
3. Use a lockStatus table. I make a row that's called
"registerNewUser" and set it to "locked"; do my work; then set
"registerNewUser" back to "unlocked". This is fine, but if the user
clicks out or the steps are not completed, "registerNewUser" remains
set to "locked".
What I am leaning towards is a LOCK TABLES approach that tries to
mimic the semaphore file approach.
A- I create an empty (semaphore) table called registerNewUser.
B- I get a write lock on this table, and a read lock on all tables
needed for the subsequent work.
C- I release the locks.
Any other operation that would conflict with registering a new user
would also get a lock on the semaphore table registerNewUser. What I
think this achieves is that any other user can read from the tables
that are being written to (which is not usually a problem - if it is,
that user gets a read lock on the appropriate semaphore table). Also,
if the connection is lost, the lock is released.
Can anyone shoot holes in this thinking? Am I right in thinking this
reasonably imitates the semaphore file approach?
Maybe over thinking a bit... the idea behind LOCK TABLES is that
MySQL will que the requests as they come in. You want to spend as little
time as possible processing locked tables as you can. Don't do anything
that will take considerable amounts of time. Sometimes you don't even
need LOCK TABLES. My process is (simplified):
table users = id big int, email char(), first char(), last char(),
status enum('U','R','D'), date date()
1) new user registers...
2) is there a match in the db already? ... options
3) no match found, add info, get LAST_INSERT_ID(). status is 'U'
unregistered.
4) continue with other processing with newly acquired id
5) at this point you have a tag (id) to the person. I usually do email
verification such as a clickable/cut/paste url likewww.yourdomain.com/complete_registration.php?id=????
6) when the person follows the link, I UPDATE their account to 'R' for
registered. I have ('U','R','D'). 'D' for deletable. Unregistered
accounts over ??? days old.
INSERTs are atomic (won't conflict with each other) and LAST_INSERT_ID()
is connection specific (no cross-over). Basically, no one ends up with
the same id. And besides, your options 1 and 2 only provide the
functionality of what MySQL is already doing for you behind the scenes.
Norm
Use PDO.
$db->beginTransaction();
$db->exec($query);
$db->commit();
if( some condition )
{
$db->rollBack();}
but i dont really see you have a massive problem, its not about shared
hosting, its about many concurrent users of yours, operating on the
same table. Fix it with logic in your code using PDOs abilities.

That is very interesting; I had not heard of PDO until now. I mention
shared hosting because the host I'm most familiar with doesn't allow
certain db engines, because of the potential for bad coding to eat up
resources with that engine. So I'm stuck for now with MyISAM (no
transaction support). I find this warning when reading about PDO athttp://us.php.net/manual/en/ref.pdo-mysql.php:

"Beware: Some MySQL table types (storage engines) do not support
transactions. When writing transactional database code using a table
type that does not support transactions, MySQL will pretend that a
transaction was initiated successfully. In addition, any DDL queries
issued will implicitly commit any pending transactions."

Does this mean PDO emulates transactions for engines that do not
support transactions? Or is it a warning not to try using
transactions in PDO when using an engine that doesn't support
transactions?
ah MyISAM, yes sorry I missed that no go there I am afriad, change
host and start using InnoDB if you need transactions which are nice.
PDO doesnt emulate transactions unfortunately, I am sure you could
write some code that did, but then thats your actual problem! I think
this is all solved in the app myseld, but go with a host that isnt so
restrictive, that rolls with the times and uses their knowledge to
help not hinder you, solving the problems they come across as part of
the service, rather than saying "oh no cant let you do that".

May 8 '07 #6

P: n/a
On May 8, 6:59 am, shimmyshack <matt.fa...@gmail.comwrote:
On May 8, 4:10 pm, e_matt...@hotmail.com wrote:
On May 8, 2:45 am, shimmyshack <matt.fa...@gmail.comwrote:
On May 8, 4:24 am, Norman Peelman <npeel...@cfl.rr.comwrote:
e_matt...@hotmail.com wrote:
Hello everyone,
I have read many threads about concurrency issues, and I think I
understand some of the pieces, but not the whole picture.
I believe I am like many people using php: developing a personal
website on a windows machine, that will be hosted on a shared linux
server. Financial reality makes it impossible to think of a dedicated
server, and I'm not ready to jump into developing on a linux box.
I am looking at how to efficiently code some database operations,
while using a MyISAM mySQL database. The options seem to be:
1. Use flock() in the code to lock a semaphore file. This sounded
fine, until I read that different systems respond to flock()
differently, and we get into making temporary directories and checking
if they exist. This seems difficult to develop on a local machine,
then play around on the shared server environment.
2. Use LOCK TABLES in mysql. This would be fine, but results in
locking tables more often and more restrictively than they need to be
locked.
3. Use a lockStatus table. I make a row that's called
"registerNewUser" and set it to "locked"; do my work; then set
"registerNewUser" back to "unlocked". This is fine, but if the user
clicks out or the steps are not completed, "registerNewUser" remains
set to "locked".
What I am leaning towards is a LOCK TABLES approach that tries to
mimic the semaphore file approach.
A- I create an empty (semaphore) table called registerNewUser.
B- I get a write lock on this table, and a read lock on all tables
needed for the subsequent work.
C- I release the locks.
Any other operation that would conflict with registering a new user
would also get a lock on the semaphore table registerNewUser. What I
think this achieves is that any other user can read from the tables
that are being written to (which is not usually a problem - if it is,
that user gets a read lock on the appropriate semaphore table). Also,
if the connection is lost, the lock is released.
Can anyone shoot holes in this thinking? Am I right in thinking this
reasonably imitates the semaphore file approach?
Maybe over thinking a bit... the idea behind LOCK TABLES is that
MySQL will que the requests as they come in. You want to spend as little
time as possible processing locked tables as you can. Don't do anything
that will take considerable amounts of time. Sometimes you don't even
need LOCK TABLES. My process is (simplified):
table users = id big int, email char(), first char(), last char(),
status enum('U','R','D'), date date()
1) new user registers...
2) is there a match in the db already? ... options
3) no match found, add info, get LAST_INSERT_ID(). status is 'U'
unregistered.
4) continue with other processing with newly acquired id
5) at this point you have a tag (id) to the person. I usually do email
verification such as a clickable/cut/paste url likewww.yourdomain.com/complete_registration.php?id=????
6) when the person follows the link, I UPDATE their account to 'R' for
registered. I have ('U','R','D'). 'D' for deletable. Unregistered
accounts over ??? days old.
INSERTs are atomic (won't conflict with each other) and LAST_INSERT_ID()
is connection specific (no cross-over). Basically, no one ends up with
the same id. And besides, your options 1 and 2 only provide the
functionality of what MySQL is already doing for you behind the scenes.
Norm
Use PDO.
$db->beginTransaction();
$db->exec($query);
$db->commit();
if( some condition )
{
$db->rollBack();}
but i dont really see you have a massive problem, its not about shared
hosting, its about many concurrent users of yours, operating on the
same table. Fix it with logic in your code using PDOs abilities.
That is very interesting; I had not heard of PDO until now. I mention
shared hosting because the host I'm most familiar with doesn't allow
certain db engines, because of the potential for bad coding to eat up
resources with that engine. So I'm stuck for now with MyISAM (no
transaction support). I find this warning when reading about PDO athttp://us.php.net/manual/en/ref.pdo-mysql.php:
"Beware: Some MySQL table types (storage engines) do not support
transactions. When writing transactional database code using a table
type that does not support transactions, MySQL will pretend that a
transaction was initiated successfully. In addition, any DDL queries
issued will implicitly commit any pending transactions."
Does this mean PDO emulates transactions for engines that do not
support transactions? Or is it a warning not to try using
transactions in PDO when using an engine that doesn't support
transactions?

ah MyISAM, yes sorry I missed that no go there I am afriad, change
host and start using InnoDB if you need transactions which are nice.
PDO doesnt emulate transactions unfortunately, I am sure you could
write some code that did, but then thats your actual problem! I think
this is all solved in the app myseld, but go with a host that isnt so
restrictive, that rolls with the times and uses their knowledge to
help not hinder you, solving the problems they come across as part of
the service, rather than saying "oh no cant let you do that".
Back in school I played a game where everyone wrote a sentence on a
piece of paper. Then everyone passed their paper to the right, and
everyone added a sentence to the paper in front of them. By the time
the papers made it back to the original people, you had a bunch of
stories that no one could have predicted. Let's say we want to set up
a page to make these stories.

Only registered users can participate, and they can only contribute
one sentence to each story. Each story is fairly long, say 500
sentences, and you want to make 500 stories altogether. Here's how I
would store each sentence:

1 - Verify it's a sentence, sanitize input, all the usual pre-database
work.
2 - Lock on the empty semaphore table as write, read-lock on all other
tables necessary.
3 - Pull the last sentence stored, make sure it is the same last
sentence the user built off of.
4 - Store the current sentence.
5 - If this was the 500th sentence, create a new table to hold the
next story.
6 - Release table locks.

This is not the scenario I am implementing (although it would be
fun!), but it gets at similar issues - checking against a previous
record before writing, checking to see if it is time for a new table
to be created. I know there are many ways to code this, as well, and
I will look around at hosts which offer transaction-support db
engines. But going back to the original question, would the semaphore-
table approach work reasonably? Or are there holes in this logic?

Thanks for the feedback.

May 8 '07 #7

P: n/a
On May 8, 3:47 pm, e_matt...@hotmail.com wrote:
On May 8, 6:59 am, shimmyshack <matt.fa...@gmail.comwrote:
On May 8, 4:10 pm, e_matt...@hotmail.com wrote:
On May 8, 2:45 am, shimmyshack <matt.fa...@gmail.comwrote:
On May 8, 4:24 am, Norman Peelman <npeel...@cfl.rr.comwrote:
e_matt...@hotmail.com wrote:
Hello everyone,
I have read many threads about concurrency issues, and I think I
understand some of the pieces, but not the whole picture.
I believe I am like many people using php: developing a personal
website on a windows machine, that will be hosted on a shared linux
server. Financial reality makes it impossible to think of a dedicated
server, and I'm not ready to jump into developing on a linux box.
I am looking at how to efficiently code some database operations,
while using a MyISAM mySQL database. The options seem to be:
1. Use flock() in the code to lock a semaphore file. This sounded
fine, until I read that different systems respond to flock()
differently, and we get into making temporary directories and checking
if they exist. This seems difficult to develop on a local machine,
then play around on the shared server environment.
2. Use LOCK TABLES in mysql. This would be fine, but results in
locking tables more often and more restrictively than they need to be
locked.
3. Use a lockStatus table. I make a row that's called
"registerNewUser" and set it to "locked"; do my work; then set
"registerNewUser" back to "unlocked". This is fine, but if the user
clicks out or the steps are not completed, "registerNewUser" remains
set to "locked".
What I am leaning towards is a LOCK TABLES approach that tries to
mimic the semaphore file approach.
A- I create an empty (semaphore) table called registerNewUser.
B- I get a write lock on this table, and a read lock on all tables
needed for the subsequent work.
C- I release the locks.
Any other operation that would conflict with registering a new user
would also get a lock on the semaphore table registerNewUser. What I
think this achieves is that any other user can read from the tables
that are being written to (which is not usually a problem - if it is,
that user gets a read lock on the appropriate semaphore table). Also,
if the connection is lost, the lock is released.
Can anyone shoot holes in this thinking? Am I right in thinking this
reasonably imitates the semaphore file approach?
Maybe over thinking a bit... the idea behind LOCK TABLES is that
MySQL will que the requests as they come in. You want to spend as little
time as possible processing locked tables as you can. Don't do anything
that will take considerable amounts of time. Sometimes you don't even
need LOCK TABLES. My process is (simplified):
table users = id big int, email char(), first char(), last char(),
status enum('U','R','D'), date date()
1) new user registers...
2) is there a match in the db already? ... options
3) no match found, add info, get LAST_INSERT_ID(). status is 'U'
unregistered.
4) continue with other processing with newly acquired id
5) at this point you have a tag (id) to the person. I usually do email
verification such as a clickable/cut/paste url likewww.yourdomain.com/complete_registration.php?id=????
6) when the person follows the link, I UPDATE their account to 'R' for
registered. I have ('U','R','D'). 'D' for deletable. Unregistered
accounts over ??? days old.
INSERTs are atomic (won't conflict with each other) and LAST_INSERT_ID()
is connection specific (no cross-over). Basically, no one ends up with
the same id. And besides, your options 1 and 2 only provide the
functionality of what MySQL is already doing for you behind the scenes.
Norm
Use PDO.
$db->beginTransaction();
$db->exec($query);
$db->commit();
if( some condition )
{
$db->rollBack();}
but i dont really see you have a massive problem, its not about shared
hosting, its about many concurrent users of yours, operating on the
same table. Fix it with logic in your code using PDOs abilities.
That is very interesting; I had not heard of PDO until now. I mention
shared hosting because the host I'm most familiar with doesn't allow
certain db engines, because of the potential for bad coding to eat up
resources with that engine. So I'm stuck for now with MyISAM (no
transaction support). I find this warning when reading about PDO athttp://us.php.net/manual/en/ref.pdo-mysql.php:
"Beware: Some MySQL table types (storage engines) do not support
transactions. When writing transactional database code using a table
type that does not support transactions, MySQL will pretend that a
transaction was initiated successfully. In addition, any DDL queries
issued will implicitly commit any pending transactions."
Does this mean PDO emulates transactions for engines that do not
support transactions? Or is it a warning not to try using
transactions in PDO when using an engine that doesn't support
transactions?
ah MyISAM, yes sorry I missed that no go there I am afriad, change
host and start using InnoDB if you need transactions which are nice.
PDO doesnt emulate transactions unfortunately, I am sure you could
write some code that did, but then thats your actual problem! I think
this is all solved in the app myseld, but go with a host that isnt so
restrictive, that rolls with the times and uses their knowledge to
help not hinder you, solving the problems they come across as part of
the service, rather than saying "oh no cant let you do that".

Back in school I played a game where everyone wrote a sentence on a
piece of paper. Then everyone passed their paper to the right, and
everyone added a sentence to the paper in front of them. By the time
the papers made it back to the original people, you had a bunch of
stories that no one could have predicted. Let's say we want to set up
a page to make these stories.

Only registered users can participate, and they can only contribute
one sentence to each story. Each story is fairly long, say 500
sentences, and you want to make 500 stories altogether. Here's how I
would store each sentence:

1 - Verify it's a sentence, sanitize input, all the usual pre-database
work.
2 - Lock on the empty semaphore table as write, read-lock on all other
tables necessary.
3 - Pull the last sentence stored, make sure it is the same last
sentence the user built off of.
4 - Store the current sentence.
5 - If this was the 500th sentence, create a new table to hold the
next story.
6 - Release table locks.

This is not the scenario I am implementing (although it would be
fun!), but it gets at similar issues - checking against a previous
record before writing, checking to see if it is time for a new table
to be created. I know there are many ways to code this, as well, and
I will look around at hosts which offer transaction-support db
engines. But going back to the original question, would the semaphore-
table approach work reasonably? Or are there holes in this logic?

Thanks for the feedback.

I answered my own question about the semaphore table. When you issue
a lock, you can't work with any tables you haven't locked. Write-
locking a semaphore table and read-locking the other tables does not
work, because then you can't write to those other tables. So, you
have to use a write lock on all the tables you'd write to anyway,
which brings us back to the over-restrictive lock tables approach.
Time to revisit one of the other approaches, which will probably mean
more efficient database design in the first place.

May 9 '07 #8

P: n/a
e_*******@hotmail.com wrote:
On May 8, 6:59 am, shimmyshack <matt.fa...@gmail.comwrote:
>On May 8, 4:10 pm, e_matt...@hotmail.com wrote:
>>On May 8, 2:45 am, shimmyshack <matt.fa...@gmail.comwrote:
On May 8, 4:24 am, Norman Peelman <npeel...@cfl.rr.comwrote:
e_matt...@hotmail.com wrote:
>Hello everyone,
>I have read many threads about concurrency issues, and I think I
>understand some of the pieces, but not the whole picture.
>I believe I am like many people using php: developing a personal
>website on a windows machine, that will be hosted on a shared linux
>server. Financial reality makes it impossible to think of a dedicated
>server, and I'm not ready to jump into developing on a linux box.
>I am looking at how to efficiently code some database operations,
>while using a MyISAM mySQL database. The options seem to be:
>1. Use flock() in the code to lock a semaphore file. This sounded
>fine, until I read that different systems respond to flock()
>differently, and we get into making temporary directories and checking
>if they exist. This seems difficult to develop on a local machine,
>then play around on the shared server environment.
>2. Use LOCK TABLES in mysql. This would be fine, but results in
>locking tables more often and more restrictively than they need to be
>locked.
>3. Use a lockStatus table. I make a row that's called
>"registerNewUser" and set it to "locked"; do my work; then set
>"registerNewUser" back to "unlocked". This is fine, but if the user
>clicks out or the steps are not completed, "registerNewUser" remains
>set to "locked".
>What I am leaning towards is a LOCK TABLES approach that tries to
>mimic the semaphore file approach.
>A- I create an empty (semaphore) table called registerNewUser.
>B- I get a write lock on this table, and a read lock on all tables
>needed for the subsequent work.
>C- I release the locks.
>Any other operation that would conflict with registering a new user
>would also get a lock on the semaphore table registerNewUser. What I
>think this achieves is that any other user can read from the tables
>that are being written to (which is not usually a problem - if it is,
>that user gets a read lock on the appropriate semaphore table). Also,
>if the connection is lost, the lock is released.
>Can anyone shoot holes in this thinking? Am I right in thinking this
>reasonably imitates the semaphore file approach?
Maybe over thinking a bit... the idea behind LOCK TABLES is that
MySQL will que the requests as they come in. You want to spend as little
time as possible processing locked tables as you can. Don't do anything
that will take considerable amounts of time. Sometimes you don't even
need LOCK TABLES. My process is (simplified):
table users = id big int, email char(), first char(), last char(),
status enum('U','R','D'), date date()
1) new user registers...
2) is there a match in the db already? ... options
3) no match found, add info, get LAST_INSERT_ID(). status is 'U'
unregistered.
4) continue with other processing with newly acquired id
5) at this point you have a tag (id) to the person. I usually do email
verification such as a clickable/cut/paste url likewww.yourdomain.com/complete_registration.php?id=????
6) when the person follows the link, I UPDATE their account to 'R' for
registered. I have ('U','R','D'). 'D' for deletable. Unregistered
accounts over ??? days old.
INSERTs are atomic (won't conflict with each other) and LAST_INSERT_ID()
is connection specific (no cross-over). Basically, no one ends up with
the same id. And besides, your options 1 and 2 only provide the
functionality of what MySQL is already doing for you behind the scenes.
Norm
Use PDO.
$db->beginTransaction();
$db->exec($query);
$db->commit();
if( some condition )
{
$db->rollBack();}
but i dont really see you have a massive problem, its not about shared
hosting, its about many concurrent users of yours, operating on the
same table. Fix it with logic in your code using PDOs abilities.
That is very interesting; I had not heard of PDO until now. I mention
shared hosting because the host I'm most familiar with doesn't allow
certain db engines, because of the potential for bad coding to eat up
resources with that engine. So I'm stuck for now with MyISAM (no
transaction support). I find this warning when reading about PDO athttp://us.php.net/manual/en/ref.pdo-mysql.php:
"Beware: Some MySQL table types (storage engines) do not support
transactions. When writing transactional database code using a table
type that does not support transactions, MySQL will pretend that a
transaction was initiated successfully. In addition, any DDL queries
issued will implicitly commit any pending transactions."
Does this mean PDO emulates transactions for engines that do not
support transactions? Or is it a warning not to try using
transactions in PDO when using an engine that doesn't support
transactions?
ah MyISAM, yes sorry I missed that no go there I am afriad, change
host and start using InnoDB if you need transactions which are nice.
PDO doesnt emulate transactions unfortunately, I am sure you could
write some code that did, but then thats your actual problem! I think
this is all solved in the app myseld, but go with a host that isnt so
restrictive, that rolls with the times and uses their knowledge to
help not hinder you, solving the problems they come across as part of
the service, rather than saying "oh no cant let you do that".

Back in school I played a game where everyone wrote a sentence on a
piece of paper. Then everyone passed their paper to the right, and
everyone added a sentence to the paper in front of them. By the time
the papers made it back to the original people, you had a bunch of
stories that no one could have predicted. Let's say we want to set up
a page to make these stories.

Only registered users can participate, and they can only contribute
one sentence to each story. Each story is fairly long, say 500
sentences, and you want to make 500 stories altogether. Here's how I
would store each sentence:

1 - Verify it's a sentence, sanitize input, all the usual pre-database
work.
2 - Lock on the empty semaphore table as write, read-lock on all other
tables necessary.
3 - Pull the last sentence stored, make sure it is the same last
sentence the user built off of.
4 - Store the current sentence.
5 - If this was the 500th sentence, create a new table to hold the
next story.
6 - Release table locks.

This is not the scenario I am implementing (although it would be
fun!), but it gets at similar issues - checking against a previous
record before writing, checking to see if it is time for a new table
to be created. I know there are many ways to code this, as well, and
I will look around at hosts which offer transaction-support db
engines. But going back to the original question, would the semaphore-
table approach work reasonably? Or are there holes in this logic?

Thanks for the feedback.
OK, well, you NEVER want to hold ANY locks across user interfaces.
That's not true for just MySQL or the web, but any database on any
application.

For instance - what happens if you get a lock and display the page, but
your user closes his browser? You never get notified.

Also, web pages are transactional - once you've sent the page, all
resources (except for session variables) are released. This would
include locks.

You're correct - you need to think of a different way of doing it.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
May 9 '07 #9

P: n/a
On May 9, 3:27 am, Jerry Stuckle <jstuck...@attglobal.netwrote:
e_matt...@hotmail.com wrote:
On May 8, 6:59 am, shimmyshack <matt.fa...@gmail.comwrote:
On May 8, 4:10 pm, e_matt...@hotmail.com wrote:
>On May 8, 2:45 am, shimmyshack <matt.fa...@gmail.comwrote:
On May 8, 4:24 am, Norman Peelman <npeel...@cfl.rr.comwrote:
e_matt...@hotmail.com wrote:
Hello everyone,
I have read many threads about concurrency issues, and I think I
understand some of the pieces, but not the whole picture.
I believe I am like many people using php: developing a personal
website on a windows machine, that will be hosted on a shared linux
server. Financial reality makes it impossible to think of a dedicated
server, and I'm not ready to jump into developing on a linux box.
I am looking at how to efficiently code some database operations,
while using a MyISAM mySQL database. The options seem to be:
1. Use flock() in the code to lock a semaphore file. This sounded
fine, until I read that different systems respond to flock()
differently, and we get into making temporary directories and checking
if they exist. This seems difficult to develop on a local machine,
then play around on the shared server environment.
2. Use LOCK TABLES in mysql. This would be fine, but results in
locking tables more often and more restrictively than they need to be
locked.
3. Use a lockStatus table. I make a row that's called
"registerNewUser" and set it to "locked"; do my work; then set
"registerNewUser" back to "unlocked". This is fine, but if the user
clicks out or the steps are not completed, "registerNewUser" remains
set to "locked".
What I am leaning towards is a LOCK TABLES approach that tries to
mimic the semaphore file approach.
A- I create an empty (semaphore) table called registerNewUser.
B- I get a write lock on this table, and a read lock on all tables
needed for the subsequent work.
C- I release the locks.
Any other operation that would conflict with registering a new user
would also get a lock on the semaphore table registerNewUser. What I
think this achieves is that any other user can read from the tables
that are being written to (which is not usually a problem - if it is,
that user gets a read lock on the appropriate semaphore table). Also,
if the connection is lost, the lock is released.
Can anyone shoot holes in this thinking? Am I right in thinking this
reasonably imitates the semaphore file approach?
Maybe over thinking a bit... the idea behind LOCK TABLES is that
MySQL will que the requests as they come in. You want to spend as little
time as possible processing locked tables as you can. Don't do anything
that will take considerable amounts of time. Sometimes you don't even
need LOCK TABLES. My process is (simplified):
table users = id big int, email char(), first char(), last char(),
status enum('U','R','D'), date date()
1) new user registers...
2) is there a match in the db already? ... options
3) no match found, add info, get LAST_INSERT_ID(). status is 'U'
unregistered.
4) continue with other processing with newly acquired id
5) at this point you have a tag (id) to the person. I usually do email
verification such as a clickable/cut/paste url likewww.yourdomain.com/complete_registration.php?id=????
6) when the person follows the link, I UPDATE their account to 'R' for
registered. I have ('U','R','D'). 'D' for deletable. Unregistered
accounts over ??? days old.
INSERTs are atomic (won't conflict with each other) and LAST_INSERT_ID()
is connection specific (no cross-over). Basically, no one ends up with
the same id. And besides, your options 1 and 2 only provide the
functionality of what MySQL is already doing for you behind the scenes.
Norm
Use PDO.
$db->beginTransaction();
$db->exec($query);
$db->commit();
if( some condition )
{
$db->rollBack();}
but i dont really see you have a massive problem, its not about shared
hosting, its about many concurrent users of yours, operating on the
same table. Fix it with logic in your code using PDOs abilities.
That is very interesting; I had not heard of PDO until now. I mention
shared hosting because the host I'm most familiar with doesn't allow
certain db engines, because of the potential for bad coding to eat up
resources with that engine. So I'm stuck for now with MyISAM (no
transaction support). I find this warning when reading about PDO athttp://us.php.net/manual/en/ref.pdo-mysql.php:
"Beware: Some MySQL table types (storage engines) do not support
transactions. When writing transactional database code using a table
type that does not support transactions, MySQL will pretend that a
transaction was initiated successfully. In addition, any DDL queries
issued will implicitly commit any pending transactions."
Does this mean PDO emulates transactions for engines that do not
support transactions? Or is it a warning not to try using
transactions in PDO when using an engine that doesn't support
transactions?
ah MyISAM, yes sorry I missed that no go there I am afriad, change
host and start using InnoDB if you need transactions which are nice.
PDO doesnt emulate transactions unfortunately, I am sure you could
write some code that did, but then thats your actual problem! I think
this is all solved in the app myseld, but go with a host that isnt so
restrictive, that rolls with the times and uses their knowledge to
help not hinder you, solving the problems they come across as part of
the service, rather than saying "oh no cant let you do that".
Back in school I played a game where everyone wrote a sentence on a
piece of paper. Then everyone passed their paper to the right, and
everyone added a sentence to the paper in front of them. By the time
the papers made it back to the original people, you had a bunch of
stories that no one could have predicted. Let's say we want to set up
a page to make these stories.
Only registered users can participate, and they can only contribute
one sentence to each story. Each story is fairly long, say 500
sentences, and you want to make 500 stories altogether. Here's how I
would store each sentence:
1 - Verify it's a sentence, sanitize input, all the usual pre-database
work.
2 - Lock on the empty semaphore table as write, read-lock on all other
tables necessary.
3 - Pull the last sentence stored, make sure it is the same last
sentence the user built off of.
4 - Store the current sentence.
5 - If this was the 500th sentence, create a new table to hold the
next story.
6 - Release table locks.
This is not the scenario I am implementing (although it would be
fun!), but it gets at similar issues - checking against a previous
record before writing, checking to see if it is time for a new table
to be created. I know there are many ways to code this, as well, and
I will look around at hosts which offer transaction-support db
engines. But going back to the original question, would the semaphore-
table approach work reasonably? Or are there holes in this logic?
Thanks for the feedback.

OK, well, you NEVER want to hold ANY locks across user interfaces.
That's not true for just MySQL or the web, but any database on any
application.
I don't think I've said anything about holding a lock across a user
interface. All of my coding completes its database work before
serving the page.

For instance - what happens if you get a lock and display the page, but
your user closes his browser? You never get notified.
That's fine. The lock is released, and I code defensively for an
incomplete series of database operations, while working with a
database engine that does not support transactions.
Also, web pages are transactional - once you've sent the page, all
resources (except for session variables) are released. This would
include locks.
This is good. I don't plan to hold any locks after I've sent the
page. Do you see anywhere in what I've written that seems to indicate
holding a lock after sending a page?
>
You're correct - you need to think of a different way of doing it.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck...@attglobal.net
==================
May 9 '07 #10

P: n/a
e_*******@hotmail.com wrote:
On May 9, 3:27 am, Jerry Stuckle <jstuck...@attglobal.netwrote:
>e_matt...@hotmail.com wrote:
>>On May 8, 6:59 am, shimmyshack <matt.fa...@gmail.comwrote:
On May 8, 4:10 pm, e_matt...@hotmail.com wrote:
On May 8, 2:45 am, shimmyshack <matt.fa...@gmail.comwrote:
>On May 8, 4:24 am, Norman Peelman <npeel...@cfl.rr.comwrote:
>>e_matt...@hotmail.com wrote:
>>>Hello everyone,
>>>I have read many threads about concurrency issues, and I think I
>>>understand some of the pieces, but not the whole picture.
>>>I believe I am like many people using php: developing a personal
>>>website on a windows machine, that will be hosted on a shared linux
>>>server. Financial reality makes it impossible to think of a dedicated
>>>server, and I'm not ready to jump into developing on a linux box.
>>>I am looking at how to efficiently code some database operations,
>>>while using a MyISAM mySQL database. The options seem to be:
>>>1. Use flock() in the code to lock a semaphore file. This sounded
>>>fine, until I read that different systems respond to flock()
>>>differently, and we get into making temporary directories and checking
>>>if they exist. This seems difficult to develop on a local machine,
>>>then play around on the shared server environment.
>>>2. Use LOCK TABLES in mysql. This would be fine, but results in
>>>locking tables more often and more restrictively than they need to be
>>>locked.
>>>3. Use a lockStatus table. I make a row that's called
>>>"registerNewUser" and set it to "locked"; do my work; then set
>>>"registerNewUser" back to "unlocked". This is fine, but if the user
>>>clicks out or the steps are not completed, "registerNewUser" remains
>>>set to "locked".
>>>What I am leaning towards is a LOCK TABLES approach that tries to
>>>mimic the semaphore file approach.
>>>A- I create an empty (semaphore) table called registerNewUser.
>>>B- I get a write lock on this table, and a read lock on all tables
>>>needed for the subsequent work.
>>>C- I release the locks.
>>>Any other operation that would conflict with registering a new user
>>>would also get a lock on the semaphore table registerNewUser. What I
>>>think this achieves is that any other user can read from the tables
>>>that are being written to (which is not usually a problem - if it is,
>>>that user gets a read lock on the appropriate semaphore table). Also,
>>>if the connection is lost, the lock is released.
>>>Can anyone shoot holes in this thinking? Am I right in thinking this
>>>reasonably imitates the semaphore file approach?
>> Maybe over thinking a bit... the idea behind LOCK TABLES is that
>>MySQL will que the requests as they come in. You want to spend as little
>>time as possible processing locked tables as you can. Don't do anything
>>that will take considerable amounts of time. Sometimes you don't even
>>need LOCK TABLES. My process is (simplified):
>>table users = id big int, email char(), first char(), last char(),
>>status enum('U','R','D'), date date()
>>1) new user registers...
>>2) is there a match in the db already? ... options
>>3) no match found, add info, get LAST_INSERT_ID(). status is 'U'
>>unregistered.
>>4) continue with other processing with newly acquired id
>>5) at this point you have a tag (id) to the person. I usually do email
>>verification such as a clickable/cut/paste url likewww.yourdomain.com/complete_registration.php?id=????
>>6) when the person follows the link, I UPDATE their account to 'R' for
>>registered. I have ('U','R','D'). 'D' for deletable. Unregistered
>>accounts over ??? days old.
>>INSERTs are atomic (won't conflict with each other) and LAST_INSERT_ID()
>> is connection specific (no cross-over). Basically, no one ends up with
>>the same id. And besides, your options 1 and 2 only provide the
>>functionality of what MySQL is already doing for you behind the scenes.
>>Norm
>Use PDO.
>$db->beginTransaction();
>$db->exec($query);
>$db->commit();
>if( some condition )
>{
> $db->rollBack();}
>but i dont really see you have a massive problem, its not about shared
>hosting, its about many concurrent users of yours, operating on the
>same table. Fix it with logic in your code using PDOs abilities.
That is very interesting; I had not heard of PDO until now. I mention
shared hosting because the host I'm most familiar with doesn't allow
certain db engines, because of the potential for bad coding to eat up
resources with that engine. So I'm stuck for now with MyISAM (no
transaction support). I find this warning when reading about PDO athttp://us.php.net/manual/en/ref.pdo-mysql.php:
"Beware: Some MySQL table types (storage engines) do not support
transactions. When writing transactional database code using a table
type that does not support transactions, MySQL will pretend that a
transaction was initiated successfully. In addition, any DDL queries
issued will implicitly commit any pending transactions."
Does this mean PDO emulates transactions for engines that do not
support transactions? Or is it a warning not to try using
transactions in PDO when using an engine that doesn't support
transactions?
ah MyISAM, yes sorry I missed that no go there I am afriad, change
host and start using InnoDB if you need transactions which are nice.
PDO doesnt emulate transactions unfortunately, I am sure you could
write some code that did, but then thats your actual problem! I think
this is all solved in the app myseld, but go with a host that isnt so
restrictive, that rolls with the times and uses their knowledge to
help not hinder you, solving the problems they come across as part of
the service, rather than saying "oh no cant let you do that".
Back in school I played a game where everyone wrote a sentence on a
piece of paper. Then everyone passed their paper to the right, and
everyone added a sentence to the paper in front of them. By the time
the papers made it back to the original people, you had a bunch of
stories that no one could have predicted. Let's say we want to set up
a page to make these stories.
Only registered users can participate, and they can only contribute
one sentence to each story. Each story is fairly long, say 500
sentences, and you want to make 500 stories altogether. Here's how I
would store each sentence:
1 - Verify it's a sentence, sanitize input, all the usual pre-database
work.
2 - Lock on the empty semaphore table as write, read-lock on all other
tables necessary.
3 - Pull the last sentence stored, make sure it is the same last
sentence the user built off of.
4 - Store the current sentence.
5 - If this was the 500th sentence, create a new table to hold the
next story.
6 - Release table locks.
This is not the scenario I am implementing (although it would be
fun!), but it gets at similar issues - checking against a previous
record before writing, checking to see if it is time for a new table
to be created. I know there are many ways to code this, as well, and
I will look around at hosts which offer transaction-support db
engines. But going back to the original question, would the semaphore-
table approach work reasonably? Or are there holes in this logic?
Thanks for the feedback.
OK, well, you NEVER want to hold ANY locks across user interfaces.
That's not true for just MySQL or the web, but any database on any
application.

I don't think I've said anything about holding a lock across a user
interface. All of my coding completes its database work before
serving the page.

>For instance - what happens if you get a lock and display the page, but
your user closes his browser? You never get notified.

That's fine. The lock is released, and I code defensively for an
incomplete series of database operations, while working with a
database engine that does not support transactions.
>Also, web pages are transactional - once you've sent the page, all
resources (except for session variables) are released. This would
include locks.

This is good. I don't plan to hold any locks after I've sent the
page. Do you see anywhere in what I've written that seems to indicate
holding a lock after sending a page?
>You're correct - you need to think of a different way of doing it.
No, but you weren't clear in what you were trying to do, either.

And I really think you're going overboard on this - even without
transactions, I doubt you'll have enough traffic on your site that
you'll have problems.

And if you try to handle every possible contingency you'll quickly go crazy.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
May 9 '07 #11

This discussion thread is closed

Replies have been disabled for this discussion.