Locking table?

[Jacob Nielsen]

Hi,

I've got two tables that control my menu on my site (which is coded in ASP).
The first table (cam_menu) contains info about the sort order of the menu
and the second about my content. To insert a menuitem above a previous
menuitem I use these SQL-sentences:

'## Select id to insert new menuitem above
strSelectidSQL = "SELECT * FROM cam_menu WHERE id = " & intActionid & ""
Set SelectidRS = Conn.Execute(strSelectidSQL)

'## Add 1 to menu_sortorder for menuitems with a menu_sortorder greater than
or equal to
'## the one were inserting above AND where the parent_id equals the one
we're
'## inserting above
strUpdateidInfoSQL = "UPDATE cam_menu SET menu_sortorder = menu_sortorder+1
WHERE parent_id = " & SelectidRS("parent_id") & " AND menu_sortorder >= " &
SelectidRS("menu_sortorder") & ""
Conn.Execute(strUpdateidInfoSQL)

'## Insert new menuitem
strInsertNewidSQL = "INSERT INTO cam_menu(parent_id,menu_sortorder) VALUES("
& SelectidRS("parent_id") & "," & SelectidRS("menu_sortorder") & ")"
Conn.Execute(strInsertNewidSQL)

SelectidRS.Close
Set SelectidRS = Nothing

'## Select last inserted id
strSelectLastInsertedRecord = "SELECT LAST_INSERT_ID() AS intLastId FROM
cam_menu"
Set LastRecordRS = Conn.Execute(strSelectLastInsertedRecord)

'## Insert record into cam_content table
strInsertIntoContentSQL = "INSERT INTO cam_content(id, lng, show_status,
content_name, module_id) VALUES (" & LastRecordRS("intLastId") & ",'" &
Session("language") & "'," & strInsertMenuShowStatus & ",'" &
strInsertMenuName & "'," & strInsertContentModule & ")"
Conn.Execute(strInsertIntoContentSQL)

LastRecordRS.Close
Set LastRecordRS = Nothing

Now, this works fine and by using LAST_INSERT_ID() I won't get any conflicts
in the content table if two (or more for that matter) user(s) are inserting
a new menuitem at the same time, however that won't be the case for the
cam_menu table as far as I can see when it update the records that already
exists. Do I need to lock the tables or are there a better way of doing
this?

Also, if I need to lock the tables, how would I do that? And it has to be
done so two (or more) users can insert a menuitem at the same time, but
still not create any conflicts, which I guess means that User B has to wait
for User A's queryies to complete before executing.

Hope I'm making myself understandable, otherwise feel free to ask for more
info.

Best regards, Jacob