Request suggestion for concurrent access same table. 
Author Message
 Request suggestion for concurrent access same table.

Could anyone give me some suggestion and guideline on how to let

multiple users access same table of a database at the same time?

I do know SQL2000 provide the following features:

Lock granularity - "TabLock", "TabLockX", "RowLock",.........
Isolation Level  - "Read Committed", "Repeatable Read",......

Should I lock the table only when I do "UPDATE" command?

If I have a stored procedure called SELECT_Proc which using SELECT
to return multiple records back to VB through ADO recordset.

VB will display those records for user to edit.

After users finished editing in VB screen, the VB will call another
stored procedure called UPDATE_Proc. It will use UPDATE SQL command
for updating.

Could anyone tell me how do I "lock" the rows in SELECT_Proc all
the way to another stored procedure UPDATE_Proc? The users
will be sure there is no change by other people between the SELECT
and the UPDATE process.

Is it possible to have one "LOCK" for multiple stored procedure?

What is best approach for doing the "LOCK" only when call
SQL "UPDATE" command?

In the old day when I was using Visual Basic worked with DB2 programmers,
each tables has an extra field called timestamp. I download multiples records
to VB grid for display WITHOUT any rows lock. After the users had finished
edit certain records at VB screen, the upload compared the timestamps of
those rows between the one store in database and the one in VB screen.
If it is different, the row will not be updated, but change the background color
of that row in VB screen telling someone else has edited it.



Wed, 01 Dec 2004 04:17:43 GMT
 Request suggestion for concurrent access same table.

There isn't necessarily a simple answer to this and I suggest reading some info on locking and isolation levels.

Best isolation level that fits your description is SERIALIZABLE (or REPEATABLE READ depending on your application). However be aware that there will be likely more locks places then the records you access (especially if you don't have an index on the table). And make sure you start a transaction before you select the records, that will cause the locks to be held until you commit.

If all you need is to lock the entire table for update it's pretty easy. Use isolation level serializable and start transaction and select from table. You can use sp_lock to verify locks placed.

Remember that the lock granularity are just hints. So they may not always be honored (e.g. higher lock granularity may be chosen).


  Could anyone give me some suggestion and guideline on how to let

  multiple users access same table of a database at the same time?

  I do know SQL2000 provide the following features:

  Lock granularity - "TabLock", "TabLockX", "RowLock",.........
  Isolation Level  - "Read Committed", "Repeatable Read",......

  Should I lock the table only when I do "UPDATE" command?

  If I have a stored procedure called SELECT_Proc which using SELECT
  to return multiple records back to VB through ADO recordset.

  VB will display those records for user to edit.

  After users finished editing in VB screen, the VB will call another
  stored procedure called UPDATE_Proc. It will use UPDATE SQL command
  for updating.

  Could anyone tell me how do I "lock" the rows in SELECT_Proc all
  the way to another stored procedure UPDATE_Proc? The users
  will be sure there is no change by other people between the SELECT
  and the UPDATE process.

  Is it possible to have one "LOCK" for multiple stored procedure?

  What is best approach for doing the "LOCK" only when call
  SQL "UPDATE" command?

  In the old day when I was using Visual Basic worked with DB2 programmers,
  each tables has an extra field called timestamp. I download multiples records
  to VB grid for display WITHOUT any rows lock. After the users had finished
  edit certain records at VB screen, the upload compared the timestamps of
  those rows between the one store in database and the one in VB screen.
  If it is different, the row will not be updated, but change the background color
  of that row in VB screen telling someone else has edited it.



Wed, 01 Dec 2004 07:02:01 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. lock table for concurrent user-access with RDO and Access-Database

2. Problems with two concurrent access to a table

3. Concurrent access to Paradox tables

4. concurrent request question

5. Web request broker - Planning concurrent users??

6. Problems with ADO, concurrent requests and COM

7. Oracle Apps - Cancel concurrent request at OS level

8. Suggestions requested for DB design issue...

9. Requesting Design Suggestions for SQL Server and VB

10. Database design suggestion requested

11. Document Manager - Suggestion Requested


 
Powered by phpBB® Forum Software