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(st rSelectidSQL)

'## 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
strUpdateidInfo SQL = "UPDATE cam_menu SET menu_sortorder = menu_sortorder+ 1
WHERE parent_id = " & SelectidRS("par ent_id") & " AND menu_sortorder >= " &
SelectidRS("men u_sortorder") & ""
Conn.Execute(st rUpdateidInfoSQ L)

'## Insert new menuitem
strInsertNewidS QL = "INSERT INTO cam_menu(parent _id,menu_sortor der) VALUES("
& SelectidRS("par ent_id") & "," & SelectidRS("men u_sortorder") & ")"
Conn.Execute(st rInsertNewidSQL )

SelectidRS.Clos e
Set SelectidRS = Nothing

'## Select last inserted id
strSelectLastIn sertedRecord = "SELECT LAST_INSERT_ID( ) AS intLastId FROM
cam_menu"
Set LastRecordRS = Conn.Execute(st rSelectLastInse rtedRecord)

'## Insert record into cam_content table
strInsertIntoCo ntentSQL = "INSERT INTO cam_content(id, lng, show_status,
content_name, module_id) VALUES (" & LastRecordRS("i ntLastId") & ",'" &
Session("langua ge") & "'," & strInsertMenuSh owStatus & ",'" &
strInsertMenuNa me & "'," & strInsertConten tModule & ")"
Conn.Execute(st rInsertIntoCont entSQL)

LastRecordRS.Cl ose
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