Locks of table

  • Thread starter Thread starter Frank Dulk
  • Start date Start date
F

Frank Dulk

I develop applications in Access 97 and now I am migrating my BD for Sql
Server 2000, more I am having a great difficulty in finding a way to control
Locks of table I give an Addnew in the table the sql Server it blocks the
whole table. I don't get nor to consult of another machine. that this being
a serious problem, since, I don't find any material that talks about
blockades of Registration in the sql server.
I read at the forum certain time in a post that in some occasions SQL blocks
the whole table in ADDNew, it will be that somebody knows which are these
occasions for me to can you avoid them.

Obs: the Blockade of the table (Lock) it only happens after the update,
until the commit.


Set Solic = MeuBD.OpenRecordset("Solic_Separacao", dbOpenDynaset,
dbSeeChanges,dbPessimistic)

'------------------------------
Solic.AddNew
Solic!Codigo_Empresa = Forms!Abertura!Empresa
Solic!Data_Separacao = Now 'Forms!Abertura!DataReferência
Solic!Tipo_Entrega = 1
Solic!Codigo_Cliente = Sugest!Codigo_Cliente
Solic!Codigo_CFO = Sugest!Codigo_CFO
Solic!Codigo_Usuario = Forms!Abertura!UsuárioAtual
Solic!Situacao_Separacao = 2
Solic.Update
 
If you are wondering why the whole table is blocked, the blockade is exactly
caused by your code: opening a recordset on a WHOLE TABLE in Pessimistic
mode.

You need do some study on the arguments of OpenRecordSet() method, they are
there for reasons.

First of all, "Pessmistic" means to lock all records in the opened
recordset. Try to avoid this whenever possible. Say, a user open a recordset
with many records in it (in your case, it is all records in a table), then
he did not the the operatio on the oped recordset and leave his computer for
lunch (maybe never returned until next day). Now those records are locked
and no other users can work on them. Too bad.

Secondly, if you have to open recordset "Pessimistically", try to only grab
as less records as possible, only those absolutely needed for you operation,
with "SELECT ... FROM ...WHERE..." statement. You definitely need to avoid
open a recordset on entire table.

Open a recordset on entire table in Pessimistic mode is the worst way one
can manage to work in database app.
 
hi,

I tried to change the lock type and also to select the minimum of records
the possible, the fact is that even if I just selected 1 line in the
recordset,e it doesn't advance he blocks the whole table after an update.

I tried using the optimist tbem it joined the whole table, and even if I
make a select that recordset.EOF comes back = true, if I give addnew in him,
he joins the table finds out after the update.
 
Back
Top