Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

Lock tables

Quote Reply
Lock tables
I'm customizing linksSQL to allow some kind of editors (administrators) to use the admin session. Here's the question... does mysql needs table locking to manage inserts, updates and deletion of records in the same time? I noticed Alex did leave locks everywhere (search.pm). How mysql manage multiple operations in the same time?

thanks in advance

cheers

lepo
Quote Reply
Re: Lock tables In reply to
In order to answer that you need to consult the docs, for your version of MySQL.

The docs are very complete, and should give you the answers you are looking for.

Quote Reply
Re: Lock tables In reply to
Hi Lepo,

No locks are not needed, unless you need to guarantee that a series of sql statements will happen in order (usually this doesn't matter). However, if you have multiple people doing multiple insert/update/delete/queries, you don't need to lock the database first.

(The code in Search.pm was a test, as you get much better performance by locking when doing _a lot_ of inserts all at once).

Cheers,

Alex
Quote Reply
Re: Lock tables In reply to
I guess I mis-read the question.

There is "record level" locking, meaning no two people can modify the same link at the same time, and more than 1 person can make changes to the database at one time.

I guess I read it as:

If Jane does a search to modify 10 records with a keyword of "Grass", which includes (for example) Link ID #222, and then Roger does a search to modify record #222 there is no way for the database to keep track of that.

The two changes can overwrite each other.

Example -- Jane's request is at 2:30 and Roger's is at 2:31 Both get the same copy of the record.

Roger saves his change back to the database at 2:32, but jane, who is modifying 10 records, takes 10 minutes to make the changes, and she doesn't save the changes back until 2:40. Her changes of record 222 will overwrite Rogers, because the Admin made a "select" request, and populated a form, it didn't put a "hold" or a "lock" on the records that were being returned.

When the "update" button is pressed, the form sends an "update" request to the database, but it assumes that any data in the new fiels overwrites the old data, so no check is made that the data has not changed since it was last accessed.

You could get around this using a "timestamp" variable, or a "datetime" variable that is updated whenever the record is modified.

If the hits are _not_ being written to the main Links table, a plain "timestamp" field would work... if they are, you'd have to define a new field such as "admin_edit" as a "datetime" and update it with the time the update was made. You'd need to check for that BEFORE updating the record, and if it's later than the one you have, you need a way to save the new edits, show the changes, and let them be overridden or not. It's really, really, really bad form and incredibly aggrevating to find all the changes you made thrown away and lost because the database updated the hits/ratings values and you've made significant changes to the Descriptions.

That's what I visualized by "lock" as opposed to multi-user access.

I could be wrong in the above -- but I've been able to make simultaneous changes to the database from different browser windows, without any "record locked" messages, so I'm assuming that what I see in the code (and explain above) is how it works.

Quote Reply
Re: Lock tables In reply to
Yes, your quite right. What I had meant is you won't get any internal mysql errors, or you won't corrupt your table. But yes, if two people try to modify something at the same time, only the last one will stick, and the other won't know their changes were overwritten.

Cheers,

Alex
Quote Reply
Re: Lock tables In reply to
I was thinking about this... I would figure it would be safer, especially with cookies enabled, if any "request" to the database for records from anyone with "edit" powers set the "Admin_Last_Edit" datetime field to the current time, and return that to the form.

Then, if the time does not match the time in the record, you know for sure something has changed.

It's much safter than simply using a "later than" type of time flag.

Quote Reply
Re: [Alex] Lock tables In reply to
Is there a way to prevent the modify of the same link at the same time by 2 different people with editing permission?

thanks!