is it possible to lock oracle 10g database table with ADO.NET?

Question

I have a table that contains a maximum value that needs to be get and set by multiple programs. How can I lock the table for a while when old value is got and new is updated in C#? In other words:

string sql = "lock table MaxValueTable in exclusive mode";  
using (DbCommand cmd = cnctn.CreateCommand())
{
    cmd.CommandText = sql;
    // execute command somehow!!
}

maxValue = GetMaxValue();
SetMaxValue(maxValue + X);

sql = "lock table MaxValueTable in share mode";  
using (DbCommand cmd = cnctn.CreateCommand())
{
    cmd.CommandText = sql;
    // execute command somehow!!
}

Answer 1

There is a lock table command

LOCK TABLE [schema.] table IN lockmode EXCLUSIVE [NOWAIT]

Note: you can't reset a lock until you end the transaction. So if you locked the table, you can't unlock it.

Answer 2

Oracle can handle locking itself. Try to use Database features as far as possible.

Sequence is the preferred way in Oracle as comments above will tell you.

Answer 3

To keep the lock after the statement that takes it you need a transaction.

Answer 4

Sounds like you should be using an Oracle Sequence instead - as already mentioned in my comment :)

It will return unique numbers whenever calling NEXTVAL.

---

If for some reason you cant' use a sequence, just perform an UPDATE on that row. Oracle will lock that row until you end your transaction (COMMIT or ROLLBACK) and all other updates will wait until the lock is released.

---

EDIT:

If transactions are not supported in ADO, you could also put that into an Oracle Procedure using an AUTONOMOUS_TRANSACTION:

CREATE OR REPLACE PROCEDURE allocate_sequence_numbers(
  in_size IN max_value_table.val%TYPE,
  out_next_sequence_number OUT max_value_table.val%TYPE
)
AS
  PRAGMA AUTONOMOUS_TRANSACTION;
  next_sequence_number max_value_table.val%TYPE;
BEGIN
  UPDATE max_value_table
  SET val = val + in_size
  RETURNING val - in_size
  INTO out_next_sequence_number;
  COMMIT;
END allocate_sequence_numbers;

It will update your table to "allocate" the specified number of values, and return the first number of the sequence you allocated. The next application that calls it will receive the next sequence number.

The UPDATE causes a lock on that row, so other calls have to wait for that lock to be released. By using COMMIT inside the AUTONOMOUS_TRANSACTION, the lock is released, while your own transaction will not be affected.

Answer 5

You can try another method. First enable table locking on the table. Say my table is T. Then

SQL> alter table t enable table lock   
2    /

Now, you can select, and any other select will wait, because you have a lock.

SQL> select *
  2  from t
  3  for update
  4  /

So, any select command else where in another session will not return and wait for the lock to be released. Remember, you need to do COMMIT to release the lock.

Answer 6

It's generally a good practice to use providers for a specified database. Oracle has a dedicated provider for Microsoft.Net on his site, I would recommend using it. Note that all dedicated providers implement the same base classess so certain level of abstraction is kept. Secondly U definately need a transaction. I'd recommend doing that by invoking a stored procedure (either by executeNonQuery() or executeScalar() methods - depending on the result U need.

If You can't use stored procedures (U can't create them) then You have to use transations offered by the provider. Both Ole provider and ORacle provider offer transaction

using (Transaction t = cnctn.BeginTransation())


`//set some options like timeout, use serialization level like //Serializable in .Net TransactionScope


   {
        string sql = "lock table MaxValueTable in exclusive mode";
        using (DbCommand cmd = cnctn.CreateCommand())
        { 
           cmd.CommandText = sql;
           cmd.ExecuteNonQuery();
          // execute command somehow!! 
        } 
       maxValue = GetMaxValue();
       SetMaxValue(maxValue + X);
       //I presume U need to update the value in the table so some Update would be nice
       sql = "lock table MaxValueTable in share mode";
       using (DbCommand cmd = cnctn.CreateCommand())
       { 
           cmd.CommandText = sql;
           cmd.ExecuteNonQuery();
           // execute command somehow!! 
       }
       cnctn.Commit();
  }
  catch(SQLException e)
  {
     //log whatever, gracefully handle things
     t.Rollback();
     //throw;?
  }
  finally
  {
        cntn.close();
  }