Database Locking

Results 1 to 2 of 2

Thread: Database Locking

  1. #1
    Join Date
    Dec 1969

    Default Database Locking

    I was just curious if anyone has had this problem before.<BR><BR>I have a gaming ladder at<BR><BR><BR>for spades, and I ran into something pretty interesting today. I just<BR>upraded the db their from access to sql server, anyway on to my point<BR>spades is a partner game, so when two people lose they both report to two<BR>people their opponent and his/her partner. Well I had two people report at<BR>the exact same time, I&#039;m not sure about seconds but it was same day, same<BR>hour, same minute.<BR><BR>I use ADO to do the updates<BR><BR>looks like this:<BR><BR>dim rsWinner<BR>set rsWinner = Server.CreateObject("ADODB.Recordset")<BR>rsWinner .Open "Stats", db, adOpenForwardOnly, adLockOptimistic, adCmdTable<BR>rsWinner.Filter = "UserID = " & UserID1<BR><BR><BR>rsWinner("Games") = Games1<BR>rsWinner("Skill") = New_Skill1<BR>rsWinner("Wins") = Wins1<BR>rsWinner("Streak") = NewStreak1<BR>rsWinner("Rankpts") = New_Rankpts1<BR>rsWinner("Last_Played") = strToday<BR>rsWinner("Win_Percent") = Win_Percent1<BR>rsWinner("Total") = New_Skill1 + New_Rankpts1 + Tourney1<BR><BR>rsWinner.Update<BR>rsWinner.Close< BR><BR>Well when those two people reported at the same time, it only updated the<BR>winners record once, instead of twice. a report from opp1 and opp2. Am I<BR>not doing something right here, or can anyone offer some suggestions.<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Database Locking

    It would be much better if you wrote a SQL statement to do your update.<BR><BR>Update stats set field = value, field2 = value ...<BR>where UserID = UseriD <BR><BR>Make it a stored procedure or get a connection object and .execute() the sql.<BR><BR>First off using filter is not very efficient. Adding a where statement to SQL essentially lets SQL do the filtering and SQL can do it much faster than ADO (especially since stats is probably a relatively large table for you).<BR><BR>I&#039;m not sure about your update scenario. Were both users supposed to update the same record? I suppose if you stayed w/ this scenario you might have to change your lock type to adLockPessimistic to make it work, but I&#039;d strongly reccomend the SQL Update route.<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts