Locking session tables and testing

Anon

Dear everyone,

I haven't been here in a while, but now I have a query that I'd like a little discussion on...

I have a set of pages loading up in frames. All the pages use sessions. The problem can been seen when the php processes access the same session data and try to write the data to the database at the the same time. We get duplicate entries on a unique key. Big poo.

So we sort it out by doing a write lock on the session table in the session class. Sorted. How do you think this will affect the performance of the site when I have 100+ simultaneous users? Lots of locks and suspended threads... I'm using MySQL, by the way.

Also, what's the best way to test site performance? There are lots of ways of going about but has anyone come up with a decent way of testing an entire system that they can recommend?

I'd appreciate your comments, guys.

Bye for now.

Matt B

Anon

We get duplicate entries on a unique key.

And you have this column indexed as unique? Do you have it set as the primary key, or is the primary key (or this unique index) generated by you?

I suspect your column flags and code is at fault here.

Anon

Sure, this will impose some slowdown. Only way to know exactly how much is to measure it. One drastic solution would be to move to PostgreSQL, which supports a much higher level of concurrency.

We get duplicate entries on a unique key. Big poo

Now, do you mean you get attemts to insert a duplicate key? You should be able to rewrite your session storage manager to loop (a few times--don't want deadlock!) when this happens. Or do you mean you really do get multiple rows with the same "unique" key? If the latter, then they aren't unique keys--i.e. there's either something wrong with your database schema (somebody forgot to actually create the unique index) or else an outright bug in MySql.

Anon

Yes, we get attempts at duplicate entries which, obviously, are not successfull.

I don't like the idea of creating an insert and then if we get a duplicate warning doing an update or looping until it fixes itself.

That seems wrong to me. I am a bit of a perfectionist really. I know that locking the tables is probably the right way of doing it from a technical point of view.

There is nothing wrong with the methodology or indexing here, I just was wondering if anyone knew how bad it would affect the performance.

Oh, by the way, regarding the second issue... does anyone know of any decent ways to effectively test perfomance of entire applications? Some way of replicating multiple concurrent site users?

Bye,

Matt B

Anon

Yes, we get attempts at duplicate entries which, obviously, are not
successfull.

Good, this means your database design is fine.

I don't like the idea of creating an insert and then if we get a
duplicate warning doing an update or looping until it fixes itself.

Why not? It's the most economical way to handle the situation
(i.e. the normal case happens quickly with the least amount of
code, and only when you do get a conflict does the extra
code get invoked), and it's guaranteed to give correct results.
I'm still looking for the downside here (other than the fact that
it might be counterintuitive to some people.)

Anon

Yes, you're right. It's counterintuitive and the indexes are there for a reason, why not use them?

Cheers for your opinions on this.

Anon

My take is: yes, the indexes are there for a reason. But perhaps you're only focussing on one of the reasons: providing fast lookups. Indexes also have another reason: providing the mechanism by which UNIQUENESS is enforced. (This is just a special case of the first: how can the dbms know a particular candidate key value is duplicate unless it can quickly look it up?)

When adding new rows you your table, it's not fast lookups that you the programmer care about, it's preventing duplicates. The method whereby you do that might seem a little odd, but once you learn the method it's straightforward. Furthermore, knowing that an INSERT in the presence of a unique key will do a lookup internally anyway, do you really want to keep issuing two queries for each insert? Even further, the incidence of duplicates will hopefully be fairly low, so why should the normal-case code be longer? But now it appears I'm repeating myself... If I go on any longer, you'll start to be sorry you asked. :-)