Multi-user environment

Anon

I have a simple PHP pgm which accept user info by using FORM and POST to my PHP pgm and update the DB. Can someone told me what will happen under multi-user environment and how can I handle these situations:

1) If two or more users add different records (different primary key) at the same time.

2) If two or more users update the same record at the same time.

3) Is there any COMMIT / ROLLBACK features in MySQL?

Thanks

Anon

1) and 2) MySQL works on a first-come-first-server basis.
It is never ever (not even in the biggest databases) a problem if two requests come in at the same time, because they can't. One is always before the other.

If you want to prevent two users updating at the same time like:
one user opens a page with database data (let's say a client order with products)
and another person does the same for the same client, then both will have
the same record on the screen. if they both press "submit" then both updates will be executed, the second overwritng the first.

It's up to you to create a mechanism that allows only one user to access a record at any given time. This can simply be a flag in the database table that says "in use". The point being, you should not try to prevent two people updating the same record, you should try to prevent two people from accessing the same record if they intend to update it.

3) No.

Anon

I see thanx Vicent. Well is there any record locking function provided by MySQL/PHP to prevent two people updating the same record.

Anon

No there no row-level locking at this time.
You'll have to create your own (not very difficult)

Anon

1) should be handled fine by any database. It's why you use a database, in fact. Flat files could have a problem of one record overwriting the other here while a database won't let that happen.

2) This can be a pickle. A. opens page, B. opens same page, A. commits change, B. commits change, B.'s record overwrites A.'s

You can either lock the row of the record, in a database with row level locking, or write your own code to do the equivalent as Vincent suggested.

3) No Commit and Rollback are transactional commands that mySQL doesn't (yet) support. Postgresql does support that and much more.

If you want or need transactions, triggers, stored procedures, row level locking, autolocking, and a couple of other cool features for transactional stuff, postgresql is much better than mysql. But it is not super fast like mysql is. Conversely, it scales to literally thousands of simo connects with a large percent (10%+) beind writes.