none
table challange RRS feed

Answers

  • Multiple users can update the same table in Read committed and read uncommitted and repeatable read isolation level .However , I would recommend you to either choose read committed or repeatable read.In read committed there will be no blocking until the row that is in the process of being updated is already not blocked ...In repeatable read there will be no blocking if the update is not with in the range of other transactions ......You can also use another option called READPAST hint.

    FROM BOL

    Specifies that the Database Engine not read rows that are locked by other transactions. Under most circumstances, the same is true for pages. When READPAST is specified, both row-level and page-level locks are skipped. That is, the Database Engine skips past the rows or pages instead of blocking the current transaction until the locks are released. For example, assume table T1 contains a single integer column with the values of 1, 2, 3, 4, 5. If transaction A changes the value of 3 to 8 but has not yet committed, a SELECT * FROM T1 (READPAST) yields values 1, 2, 4, 5. READPAST is primarily used to reduce locking contention when implementing a work queue that uses a SQL Server table. A queue reader that uses READPAST skips past queue entries locked by other transactions to the next available queue entry, without having to wait until the other transactions release their locks.

    READPAST can be specified for any table referenced in an UPDATE or DELETE statement, and any table referenced in a FROM clause. When specified in an UPDATE statement, READPAST is applied only when reading data to identify which records to update, regardless of where in the statement it is specified. READPAST cannot be specified for tables in the INTO clause of an INSERT statement. Read operations that use READPAST do not block. Update or delete operations that use READPAST may block when reading foreign keys or indexed views, or when modifying secondary indexes.

    READPAST can only be specified in transactions operating at the READ COMMITTED or REPEATABLE READ isolation levels. When specified in transactions operating at the SNAPSHOT isolation level, READPAST must be combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

     


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Sunday, March 20, 2011 11:20 AM

All replies

  • Pri,

     

    What you mean? many user updating the same table at same time? If yes: Impossivel, miliseccond would be diferent, so, if i update the table 01:10:05:005 and you 01:10:05:006, your update will replace mine...


    ------------------------------------------------------------- Oracle OCA11g
    Sunday, March 20, 2011 5:02 AM
  • I have created a trigger for insert on a table.The trigger contains the code which will update the inserted record.If multiple user insert some records at the same time,deadlock occurs.How to solve it??
    Sunday, March 20, 2011 6:13 AM
  • Multiple users can update the same table in Read committed and read uncommitted and repeatable read isolation level .However , I would recommend you to either choose read committed or repeatable read.In read committed there will be no blocking until the row that is in the process of being updated is already not blocked ...In repeatable read there will be no blocking if the update is not with in the range of other transactions ......You can also use another option called READPAST hint.

    FROM BOL

    Specifies that the Database Engine not read rows that are locked by other transactions. Under most circumstances, the same is true for pages. When READPAST is specified, both row-level and page-level locks are skipped. That is, the Database Engine skips past the rows or pages instead of blocking the current transaction until the locks are released. For example, assume table T1 contains a single integer column with the values of 1, 2, 3, 4, 5. If transaction A changes the value of 3 to 8 but has not yet committed, a SELECT * FROM T1 (READPAST) yields values 1, 2, 4, 5. READPAST is primarily used to reduce locking contention when implementing a work queue that uses a SQL Server table. A queue reader that uses READPAST skips past queue entries locked by other transactions to the next available queue entry, without having to wait until the other transactions release their locks.

    READPAST can be specified for any table referenced in an UPDATE or DELETE statement, and any table referenced in a FROM clause. When specified in an UPDATE statement, READPAST is applied only when reading data to identify which records to update, regardless of where in the statement it is specified. READPAST cannot be specified for tables in the INTO clause of an INSERT statement. Read operations that use READPAST do not block. Update or delete operations that use READPAST may block when reading foreign keys or indexed views, or when modifying secondary indexes.

    READPAST can only be specified in transactions operating at the READ COMMITTED or REPEATABLE READ isolation levels. When specified in transactions operating at the SNAPSHOT isolation level, READPAST must be combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

     


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Sunday, March 20, 2011 11:20 AM