none
how i can solver these matter?? RRS feed

  • Question

  • Hello Everybody,
    I want to raise a flag to know that let me know that a table is under updation by any user. Based on this flag value i want to lock or unlock the table for further updation. Basically it is a LAN application and i dont want to allow more than one user to work on the same table of the database.

    Basically i want to know that how it can be known that a user is doing some work in a particular table??
    Can a table in databse be locked as long as any user performing some action in the table, and can be unlocked when the user has performed his task in the table. How it can be achieved?

    I m using VS2005 and SQL server and its a desktop application to be woked over LAN.

    Friday, October 22, 2010 9:32 AM

Answers

  • Unfortunately, your problem is one of the thornier problems that distributed applications must overcome. To my knowledge, there is no simple solution. As for client-server technology, I suggest WCF. Although it is not perfect, it is the most comprehensive distributed platform I've used.

    If simplicity and time is critical to you, I suggest my second approach. Create a table called "Locks" in your database. Create 1 row per table in that table, with a column called "LockerID". Each time a client wants to use a table, that client must first check if the LockerID is NULL. If it is, the client updates its own, unique ID in the table and releases that table when it's done by setting that row's LockerID back to NULL.

    There are a few dangers with this technique, but if simplicity and time is more important to you then stability, use this. The dangers of course are: race conditions: if 2 clients check the table at the same time and both try to update the Locker simultaneously, both clients might end up using the same table at the same time. The other issue is if a client crashes after it's "locked" a table, that table will remained locked forever. You can find workarounds to these issues (T-SQL for the first, and Application Events for the second for example).

    • Marked as answer by liurong luo Thursday, October 28, 2010 9:52 AM
    Monday, October 25, 2010 1:18 PM

All replies

  • You will probably find a better answer in the SQL forums.

    You can set locks in SQL to be table-wide, although that has some far-reaching performance consequences. You could create a "Lock" table in which each client could update its own ClientID before modifying a table, so that no 2 clients modify tables simultaneously. Be careful of race conditions and clients crashing before they unlock though.

    Finally, you can redo your entire project from scratch and adopt a client/server approach, where clients don't modify a DB themselves, but rather speak to a server which handles all synchronization easily. That's what I did.

    Friday, October 22, 2010 8:41 PM
  • Thanx Bob for ur quick reply, but still yet i never worked over client-server technology and i have no idea of the working at this technology. Is using client-server technology is more time consuming than other and can u give me some idea about this technology practices. Can u provide me any good e-book from which i can learn about this technology and coding practices in this technology. I m just novice at this, pls help me.

     

    Saturday, October 23, 2010 3:27 PM
  • Unfortunately, your problem is one of the thornier problems that distributed applications must overcome. To my knowledge, there is no simple solution. As for client-server technology, I suggest WCF. Although it is not perfect, it is the most comprehensive distributed platform I've used.

    If simplicity and time is critical to you, I suggest my second approach. Create a table called "Locks" in your database. Create 1 row per table in that table, with a column called "LockerID". Each time a client wants to use a table, that client must first check if the LockerID is NULL. If it is, the client updates its own, unique ID in the table and releases that table when it's done by setting that row's LockerID back to NULL.

    There are a few dangers with this technique, but if simplicity and time is more important to you then stability, use this. The dangers of course are: race conditions: if 2 clients check the table at the same time and both try to update the Locker simultaneously, both clients might end up using the same table at the same time. The other issue is if a client crashes after it's "locked" a table, that table will remained locked forever. You can find workarounds to these issues (T-SQL for the first, and Application Events for the second for example).

    • Marked as answer by liurong luo Thursday, October 28, 2010 9:52 AM
    Monday, October 25, 2010 1:18 PM