Update and Select in the same statement?

[Terry Olsen]

Is there any way to select a record and update a field in that record in the
same statement? I have a table that several threads will be querying. Once
the record has been selected by a thread, I don't want that record to be
selected by any of the other threads. So my thought was to have a "busy"
column that would be flagged true during the select. However, I don't see a
way to do it.

I'm working with an MDB file so I don't think triggers would be the answer
here. Suggestions anyone?

 

[Petar Atanasov]

Is there any way to select a record and update a field in that record in the
same statement? I have a table that several threads will be querying. Once
the record has been selected by a thread, I don't want that record to be
selected by any of the other threads. So my thought was to have a "busy"
column that would be flagged true during the select. However, I don't see a
way to do it.

I'm working with an MDB file so I don't think triggers would be the answer
here. Suggestions anyone?

Hi Terry,
Use row-level locking (I suppose you use OLEDB):
http://msdn2.microsoft.com/en-us/li...connectionstringbuilder.connectionstring.aspx

In AD0.NET you can change the locking behaviour to pessimistic through
the IsolationLevel. [http://msdn2.microsoft.com/en-us/library/ms971557.aspx]

In addition you may consider using transactions.

Locks can be set at row, page and database level. Keep in mind that
since Jet 4.0 if row locks reach certain level [as defined in
PagesLockedToTableLock found under the
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0], these
locks will be automatically promoted up to page or table level.

HTH,
Petar Atanasov
http://www.a-wake.net