Re: ADO - Retrieve and update values


> On occasions (it appears to be random) the above code doesn't work and the
> 'except' block is executed but one of the queries appears to lock and
> remains 'locked' no matter how often you retry. The application has to be
> closed before the code works again.
>
> I'm fairly new to this stuff but could anyone see why the above would
> occasionally lock or suggest a better way of retrieving a value and then
> updating it (for a variety of reasons, I can't use an autoinc \ identity
> number type).
>
> Many thanks
>
> Ian

AFAIK locking could occur if you are executing this procedure within
transaction (but you don't show us code which is dealing with them).

Select exposes shared lock on this record, which mean that record will be
available for reading from other connections, but they won't be able to
update it. Update exposes exclusive lock, which mean that this record won't
be available for read and update for others connections. Lock are closed
when transaction commits or rollbacks.

So my advices are as follows:

1.. Consider to switch to autoinc PK since you are just simulating its
functionality and this simulation is rather bad. Another option would be to
use GUIDs as primary key this way you could generate new ids on a client
without bothering server
2.. If (1) is not an option then you could do the following:
1.. Put all this stuff with select and update in the separate stored
proc with proper transaction handling, so that you could obtain new value in
a single shoot on the client side
2.. Add separate connection for ids generation so that to lock current
id record for the minimal time.
3.. Make sure that you are always Commiting or Rollbacking transactions
and keep them as short as possible


Regards,

Vitali


.

Relevant Pages

  • Re: Basic Question Re Quantities
    ... What will happen to the client executing this code when it is ... In sql server you must lock the row between the time you ... > Another is to use a transaction and lock the row when you select from T. ...
    (microsoft.public.sqlserver.programming)
  • Re: Working Transactions somehow started not to work
    ... Transaction and locking are not properties of recordsets but are properties ... Opening a served-based cursor will put a lock on ... If a connection is closed, ... SL> and are not associated necessarily with server-based cursors. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: controlling lock order in transactions
    ... Andrew J. Kelly SQL MVP ... > ROLLBACK TRANSACTION; ... > find in the clients sp that could be causing the dead lock is the join on ...
    (microsoft.public.sqlserver.programming)
  • Re: controlling lock order in transactions
    ... I believe the default isolation level for .net may be ... My transaction ONLY performs INSERTS. ... Yes it will put an exclusive lock on the row being inserted but Serializable ... COMMIT TRANSACTION ...
    (microsoft.public.sqlserver.programming)
  • Re: Tansaction/locking MSSQL2000
    ... >transaction behavior in single statements [not a BEGIN TRANSACTION ... >tables I need to lock so there is not a race later]? ... If the table is already held by an exclusive lock ... When the transaction is finished (due to ROLLBACK/COMMIT, releasing the ...
    (microsoft.public.sqlserver.programming)