How to deal with several users using php at once

[fjm]

Hi all..

Didn't expect to be back so soon but I have an other dilema going on where I can sure use some expert advice.

I have a php system that will be used in a multi-user environment. There is one screen that concerns me with regard to records that are sitting there waiting to be worked with.

If there are say, 3 records on the screen and 3 users using the application. What happens to the data (MySQL) if two users grab the same record.

I'm sure I have to use transactions to protect the data but is that enough? Will a transaction throw an error to the screen in a windoze environment to let the user know that the record is locked? I would like the user, through php to be alerted to the fact that someone has already grabbed the record and is working with it.

I was thinking that instead of the user chosing a record to work with, I could just use the session somehow to *assign* the record to an available person. Not quite sure if that is doable or an easy fix.

Can somebody please advise or share your experiences.

Man, I am hoping that there is an easy solution for me on this.

Thanks!

Frank

[pbmods]

Heya, Frank.

MySQL is very good about queueing queries. Especially if you are using transactions, MySQL will require any query that modifies any table in that transaction to wait until the current transaction is committed or rolled back.

'out-of-the-box', you should be good to go.

[jx2]

well trasaction are oki but mysql_connect() close the connection at the end of the script - iam not sure if mysql_pconnect() does the job (i didnt try)

what i did in one of the projects ,were many people write and updates many articles, i added to extra columns "lock_it" and "unlock_time"
i did that cos i wanted others to see the article all the time but if someone chose it to update it was clearly shown that someone is changing it right now.


unlock_time - user have one hour to update if he failed the lock_it=0
(some people picked staff and never did updates)

of course YMMV

regards
jx2

[fjm]

Heya, Frank.

MySQL is very good about queueing queries. Especially if you are using transactions, MySQL will require any query that modifies any table in that transaction to wait until the current transaction is committed or rolled back.

'out-of-the-box', you should be good to go.

Hey there Pbmods,

Ok, that is good to know that I should be good to go with transactions. I have yet to use them for anything so I will have to read up on them to find out a little more about exactly what thay can do for me.

I am wondering.... If two users open the same record for updating (User1 and User2) at the same time, I understand that the record will lock. Thats cool..

But what happens to User2's update data info once user1 finishes the update? Is it just discarded? or will the transaction then overwrite what User1 has done.

Thanks

Frank

[fjm]

well trasaction are oki but mysql_connect() close the connection at the end of the script - iam not sure if mysql_pconnect() does the job (i didnt try)

what i did in one of the projects ,were many people write and updates many articles, i added to extra columns "lock_it" and "unlock_time"
i did that cos i wanted others to see the article all the time but if someone chose it to update it was clearly shown that someone is changing it right now.


unlock_time - user have one hour to update if he failed the lock_it=0
(some people picked staff and never did updates)

of course YMMV

regards
jx2

Jx2,

Thank you for that suggestion. I may be able to implement something like that to let the users know that the record is being modified. I'm not sure if maybe I should use the users session_id and lock the record for just that user for a certain amount of time, or what.. Certainley something I can ponder while I am in the bathroom. :)

If you have any additional suggestions, please feel free to post them..

[pbmods]

Heya, Frank.

MySQL isn't multi-threaded; it will run one query at a time. So if you are doing everything in a single transaction, you'll be fine.

If you needed to execute multiple queries and/or transactions per connection, then you might benefit from using the LOCK TABLES command at the start of the script, and then the UNLOCK TABLES command at the end.

[fjm]

Heya, Frank.

MySQL isn't multi-threaded; it will run one query at a time. So if you are doing everything in a single transaction, you'll be fine.

If you needed to execute multiple queries and/or transactions per connection, then you might benefit from using the LOCK TABLES command at the start of the script, and then the UNLOCK TABLES command at the end.

Hey Pbmods.. Thanks for that! I appriciate that link as I think I will probably have to go multi-threaded but it doesn't seem bad at all. :)