Lock Table in Sql Server2000

When I am updating table say "Custinfo" i want to lock that table so that no one can update unless i release the lock.

No one able to give answer.

Hi Mahesh ,
For ur problem i suggest you to try with "for update nowait" clause.
For more information please visit [url]http://www.dbmotive.com/oracle_error_codes.php?errcode=00054[/url]

:) ORA-00054: Resource busy and acquire with NOWAIT specified
You tried to execute a LOCK TABLE or SELECT FOR UPDATE command with the NOWAIT keyword but the resource was unavailable.

Wait and try the command again after a few minutes.
or
Execute the command without the NOWAIT keyword.

Let's say we have 2 session, one session updates a table:

SQL> create table test (n number);

Table created.

SQL> insert into test values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> update test set n=2;

1 row updated.

--> NO COMMIT yet


Now, the other session is trying to select a record for update:
eg:

SQL> declare
2 n number;
3 begin
4 select n into n from test for update nowait;
5 end;
6 /
declare
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at line 4
:)
Hope it will solve ur problem .

try by using tblock in select statement.

Regards,
Hari Prasad

Mahesh this is ashis roy chowdhury.call me at 09902916820 at bangalore. Hope this will help: - microsoft sql server 2005 supports four concurrency options for server cursors: • read_only • optimistic with values • optimistic with row versioning • scroll locks read_only positioned updates through the cursor are not allowed, and no locks are held on the rows that make up the result set. Optimistic with values optimistic concurrency control is a standard part of transaction control theory. Optimistic concurrency control is used in situations when there is only a slight chance that another user or process may update a row in the interval between when a cursor is opened and when the row is updated. When a cursor is opened with this option, no locks are held on the underlying rows, which helps maximize throughput. If the user attempts to modify a row, the current values in the row are compared with the values retrieved when the row was last fetched. If any of the values have changed, the server knows that another user or process has already updated the row, and it returns an error. If the values are the same, the server performs the modification. Selecting this concurrency option forces the user or programmer to accept the responsibility of dealing with the occasional error indicating another user has modified the row. A typical action taken by an application that receives this error is to refresh the cursor, get the new values, and then let the user decide whether to perform the modification on the new values. Text, ntext, and image columns are not used for concurrency comparisons in sql server version 6.5 or earlier. Note: in sql server 2000 and sql server 7.0, if the underlying table has a timestamp column, optimistic with row versioning is used even if optimistic with values is specified. If optimistic with row versioning is specified and the table does not have timestamps, optimistic with values is used. Optimistic with row versioning this optimistic concurrency control option is based on row versioning. With row versioning, the underlying table must have a version identifier of some type that the server can use to determine whether the row has been changed after it was read into the cursor. In sql server, that capability is provided by the timestamp data type, which is a binary number that indicates the relative sequence of modifications in a database. Each database has a global current timestamp value, @@dbts. Each time a row with a timestamp column is modified in any way, sql server stores the current @@dbts value in the timestamp column and then increments @@dbts. If a table has a timestamp column, then the timestamps are taken down to the row level. The server can then compare the current timestamp value of a row with the timestamp value that was stored when the row was last fetched to determine whether the row has been updated. The server does not have to compare the values in all columns, only the timestamp column. If an application requests optimistic concurrency with row versioning on a table that does not have a timestamp column, the cursor defaults to values-based optimistic concurrency control. Note: for cursors opened over remote data sources, updates are not supported through the cursor if the remote source does not contain a timestamp column. Scroll locks this option implements pessimistic concurrency control, in which the application attempts to lock the underlying database rows at the time they are read into the cursor result set. When using server cursors, an update lock is placed on the row when it is read into the cursor. If the cursor is opened within a transaction, the transaction update lock is held until the transaction is either committed or rolled back; the cursor lock is dropped when the next row is fetched. If the cursor has been opened outside a transaction, the lock is dropped when the next row is fetched. Therefore, a cursor should be opened in a transaction whenever the user wants full pessimistic concurrency control. An update lock prevents any other task from acquiring an update or exclusive lock, which prevents any other task from updating the row. An update lock, however, does not block a shared lock, so it does not prevent other tasks from reading the row unless the second task is also requesting a read with an update lock. Scroll locks these cursor concurrency options may generate scroll locks, depending on the locking hints specified in the select statement in the cursor definition. Scroll locks are acquired on each row in a fetch and held until the next fetch or the close of the cursor, whichever occurs first. On the next fetch, the server acquires scroll locks for the rows in the new fetch and then releases the scroll locks for the rows in the previous fetch. Scroll locks are independent of transaction locks and may persist past a commit or rollback operation. If the option to close cursors on commit is off, a commit does not close any open cursors and scroll locks are preserved past the commit to maintain the isolation of the fetched data. The type of scroll locks acquired depends on the cursor concurrency option and the locking hints in the cursor select statement. Note: in sql server 2005, scroll locks are supported only for keyset-driven and dynamic cursors. Locking hints read only optimistic with values optimistic with row versioning locking no hints - - - update nolock* - - - - holdlock - - - update updlock - - - update tablockx - - - update all others - - - update *specifying the nolock hint makes the table on which it is specified read-only through the cursor.

hello every body, i am preppairing for the oracle9i, to attend the interview in a very short span of time. can anybody help me out by giving ur valueble suggessions.

[QUOTE=subbaraokare;19039]hello every body, i am preppairing for the oracle9i, to attend the interview in a very short span of time. can anybody help me out by giving ur valueble suggessions.[/QUOTE]

If you are prepairing for Oracle, why are you posting in SQL Server section.

Dear hari ,
can you give me a syntax tblock for blocking table.