mpedrummer2

I'm designing a database system for monitoring/tracking service calls. Currently, there's about 6 users. However, in a few weeks, it should come out of beta and go live with 50 or so...

Here's my issue: if two (or more) people are editing the same call at the same time, whomever hits "update" last, wins. Their information overwrites everyone elses.

How do I get around this? I don't want to actually lock the row by switching to InnoDB...our current system does that, and people always somehow screw up the exit process and the row stays locked until someone with direct access can fix it.

I'd like to do something like forums do...where they show you "Users currently browsing this forum". I have it figured out how to deal with that on the way in, easy enough. How do I get the site to figure out when they exit a call using the back button, or simply closing the browser?

I suppose eventually I'll download an open-source forum and take a look at the code to see what I can figure out.

Anyone have a better idea (I'm sure)?

Thanks
MPEDrummer

__________________
My sig can beat up your honor student.

Mephex

So you have a ticket that has been entered.

Are you assigning those tickets ? If so, problem solved, the responsible party updates/closes the ticket.

If not, maybe you should have techs take ownership of the ticket, so that only one tech/person can edit it at a time.

I, on the other hand, have a ticket system where it stacks the replies, similiar to a msgboard, until the ticket is closed. If Joe closes the ticket while frank is still updating, then frank goes to hit update, it returns with an error stating that this ticket was already closed by Joe.

Or, you could get complicated, and track sessions to pages to show that someone is currently viewing, then have a process that timesout inactive sessions.

I don't allow users to hit the back button, simply by replacing the last page in the users history with the current page being viewed, then as an extra measure, having all pages set to expire -1d.

Hope this helps, sounds like fun.

kel

You will not get the synchronization you desire from a web browser, I really think that PHP is a bad way to implement the interface for your database.

Sounds like it's to late to switch, but it would be much easier to setup a client server system if you didn't have such a weak client (browser).

Your problem is that HTTP connections are
stateless. We get around that with sessions and cookies, but they don't provide the fine grained synchronization you require. The browser can close and the webserver won't know, The best you can do is timeouts.

As for forums, well... those "whose browsing" scripts are usually very inaccurate.

Do it in Java... JSP server engine, Full on swing client, it won't be as responsive client side as some other combinations (graphically), but the server will run VERY efficiently and the clients probably have the horsepower to spare.

__________________
"It better be funny"

mpedrummer2

You're right, too late to change.

The clients don't have the horsepower to spare (sadly) and IT has disabled Java on most of them anyway.

I should probably be a little more clear...these aren't tickets like "fix my email", this is a company that provides onsite service 24x7 for servers, printers, terminals, whatever. The system stores client contact information, which is what is most likely to change. Often, we're finding that the area code is wrong, that sort of thing.

The current system locks the record on access, but doesn't tell you WHO locked the record. This results in people paging across the intercom all pissed off yelling "whoever's in call 2222222, get out so I can order a part!" It's an issue when someone in the other building leaves for the day and has a call open...that's why I'm looking for a system that simply advises that someone else is in, and tells you who so you can go smack them.

Beh...

MPEDrummer

__________________
My sig can beat up your honor student.

JohnnyRoyale

Yea, the problem isn't your interface, it's your database. MySQL is really a flat file system with a SQL front end, so there's not a lot you can do there.. You might think about implementing postgreSQL as an alternative, which might be able to support the per-user record locking & report that back on query. I know that Oracle would do it without question, but that's definitely $$$ beyond.

__________________
I'm gonna be rich and famous, as soon I invent a device that lets you stab people in the face over the internet.

pixelbend

I'm not sure why you just couldn't add a boolean field to the table called "locked". Add a script in your php code to change this to true when a ticket is opened and move it to false after it is updated. You could even carry the userid to another feild and display who has the record currently locked.

Am I thinking too simplistic here?

__________________
But I don't want ANY Spam!

mpedrummer2

No, you're right where I was.

Here's my problem...if the user simply closes the browser after the field is set to locked, it's locked until someone notices and goes in to fix it.

How do I unlock it when the session expires? More importantly, if the user has multiple browsers open on the site and closes one, the session won't end until ALL browser windows are closed, or in the instance of Firefox or anything else with tabbed browsing, the session wouldn't end until the broswer is closed, not just the individual tab.

Is there a way to trigger events when a session ends? I know there's a good way to do this, or at least a pretty decent one.

MPEDrummer

__________________
My sig can beat up your honor student.

gigawatz

I really don't know for sure with PHP, but I know that in ASP, you can fire a on_session_end function to clean up what the user has done. I'm hoping that something like this exists for PHP seeing a project that I'm planning really requires it.

mpedrummer2

I'll keep you posted when I find something out.

__________________
My sig can beat up your honor student.

cliche

Is it worth making use of Javascript? I use Javascript a fair bit to allow an RPC-type behaviour with my databases; you should be able to make the browser fire something off at the server as it closes?

__________________
I can't understand why people are frightened of new ideas. I'm frightened of the old ones. -- John Cage (1912 - 1992)

Rawb

Don't ever rely on sessions dying and actually being dead. A lot of times, I start a session, half edit something, head out to lunch, come back, finish the edit and click submit. So what you really need is mid-air collision detection on the server, which is actually fairly easy to accomplish without ever having to worry about sessions.

How you get this is by saving the state of the row in the first page (FORM page), passing that state to the second page (WORK page) and having the second page make sure that state still matches up when it submits.

There are two simple approaches to doing this. The first (which is the easiest to code for, but pretty hard to implement because of all of the database changes), is to tack on a TIMESTAMP column (or your local database equivalent) to the table you are worried about. When you pull up the record in the FORM, also pull up the TIMESTAMP and store it in a hidden varible. When it comes to the WORK page, do your update as usual but add an additional WHERE clause to check that the timestamp is the same as the timestamp that was passed from the FORM page. Check how many rows were updated, if there were 0, you just had a mid-air collision, or the user didn't make any changes and the database noticed that fact.

The second, which is harder to code for but requires no database changes, looks a lot like the first, except that you either pass the whole row through a hidden varible, or a checksum of a serialized version of the row. If you pass a checksum, you either have to lock the tables and manually check the checksum on the WORK page, or have your SQL daemon generate the checksum internally (in a format that could be used for a WHERE clause in an UPDATE statement)

mpedrummer2

Actually, that's the most reasonable solution yet.

Thanks.

I'm even using timestamps already, so that wouldn't require database modification at all.

__________________
My sig can beat up your honor student.

ratbastid

Or even better, a field called "locked_by", containing the user ID of the user who currently has it locked. That way if you pull up a record you can alert that it's locked by Joe over there, and instruct the user to go whack Joe on the back of the head with a phone book.

That's a really nice approach. The only thing that's missing from it is advance warning that your data is about to go poof. It protects the database from clobber, but doesn't save the user the grief of having to pull up the newly-modified record, reenter their changes (reconciling their data with the other user's new data), and save again--and hope that they got the lock THIS time!

Personally, as a user, I'd rather know up front: hey, here's the record you just queried for, but that guy across the room is already editing it. Go talk to him, wouldja?

mpedrummer2

see, that's where I started...phone books and all. Problem is, when that jackass Joe doesn't log out of the system, but simply closes the browser instead, the call stays locked by Joe.

Hence the circle of crap I'm currently stuck in.

__________________
My sig can beat up your honor student.

devnull

Could you try setting it up so that the ticket keeps track of the Session ID that is working on it? Then have a cronjob that runs every 5 minutes that verifies that a Session is still active, and if not, it'll remove the Session ID from the table and let someone else get back to work on it.