Simultaneous select/update/insert

[ignace]

Hi

 

How would I need to go about when 2 users update a single row simultaneous? And how would I need to go about when 2 users want to edit a row?

 

Should I first lock the table, update the row to indicate who is editing the data unlocking the table and selecting the row where the editor_id = the current user_id? Plus what happens when I lock the table do other users get a failed query? and what errno does mysql return in this case?

 

Do I need to use not null on required fields or should I leave it out?

[sKunKbad]

I don't know if you have thought to create a new row, then get that row number and complete your transaction. If you are using auto-increment, two users shouldn't be able to make the same number row. If you're not using auto-increment, then you should still be able to create a row number that doesn't yet exist. I do this in my e-commerce system when orders are added to the database. I'm interested to hear more answers as well, as I am no DB guru.

[nimeshrmr]

Best way to do that is keeping a version id for every record in the table.then when you want to update select the record then check the version id before updating. if the two version ids are same update the record. else select the record again and update.

 

For more details contact me.

 

<a href='www.innovativephp.com'>Innovative PHP Articles</a>

[btherl]

nimeshrmr's approach sounds good to me.  That's the basic method used in collaborative code repositories, though resolving conflicts can get messy.

 

Another approach which may work (it depends on the situation) is to record the time and identity of the person wanting to edit, and give them a fixed amount of time to do the editing.  Make sure the client software warns them if their time is running out.  This can be simpler to implement as there's no need to resolve conflicts when two people make changes to the same record.

[ignace]

@skunkbad I use indeed an auto_increment

 

I actually got to question myself these answers because of this use-case:

A user opens a page for editing during his editing session no other user is allowed to edit this page (as one may overwrite the work of the other) However what happens when users at the same time open this page for editing?

 

So I figured something like this might work:

LOCK TABLES page WRITE;
UPDATE page SET editor_id = :user_id, is_locked = true WHERE id = :page_id
UNLOCK TABLES;

 

And afterwards I would use a query like:

SELECT * FROM page WHERE id = :page_id && editor_id = :session_user_id

 

However my knowledge of DBMS is limited and I have no idea what the DBMS will do in the event two users query at the same time to lock a table.. Or what happens when a table is locked while another tries to retrieve information?

 

Something different I am also pondering about is NOT NULL should I write it? As most do not. I can understand why as when one column gets added with a not null and somewhere in your application it updates a few columns in this table then this query will fail and thus your application will break. Any advice?

[btherl]

The database will make one locker wait while the other proceeds.  It's first come first served.  This is true for write locks, but not necessarily for read locks, as multiple users can read data at the same time.

 

You should probably do it like this

 

1.  Lock the tables involved

2.  Read the relevant data (check if anyone else is editing)

3.  Mark yourself as the editor

4.  Release the locks

 

The reason for that approach is that if you read before locking, you may get two users reading at the same time, both thinking they can mark themselves as the editor.  If you lock the tables first, then any other user will be locked out until after you have updated, and when they read they will find you are editing.

 

Are you asking if you should put NOT NULL in your table definitions?  I usually do, if a value should not be null.  If a value should not be null and you set it to null, then that's an error and I want to be notified.  But if a value is allowed to be null, then of course it should not be declared "not null"   So it depends on the meaning of the value.