0

In Sybase, I can specify the locking schema for the table if it is, data rows, pages or table lock. The below is an example in SYBASE how to create a table with specifying the lock table.

create  table dbo.EX_EMPLOYEE(
   TEXT   varchar(1000)  null
)
alter table EX_EMPLOYEE lock allpages
go

In SQL server there are such lock tables(SO answer) but can I specify the lock for the table?

My question: Can I specify the table type of locks ? or in SQL server it is different? Does it depend on the query that I run?

in this link it says :

As Andreas pointed out there is no default locking level locks are taken as per operation you are trying to perform in the database. Just some examples. If it is delete/update for a particular row exclusive lock will be taken on that row If it is select operation Shared lock will be taken If it is altered table Schema Mod lock will be taken soon and so forth As Jeremy pointed out If you are looking for Isolation level it is read committed.

are they are right ? can I say that locking table in Sybase is different than SQL server?

Sunny Jangid
  • 578
  • 4
  • 19
Moudiz
  • 7,211
  • 22
  • 78
  • 156
  • If you create clustered index for your table and set index options ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS to OFF on it, what then happens? (I have not tried and thus no answer, just comment.) – Arvo Oct 25 '17 at 12:49
  • @Arvo but my question is not about indexes and doesn't need to have index to know the type of locks – Moudiz Oct 25 '17 at 12:51
  • @Moudiz - the Clustered Index is sometimes a confusing name - a clustered index on a table is the table, e.g. you could refer to it as a Clustered Table.. – Andrew Oct 25 '17 at 13:01
  • Moudiz already answered - clustered index IS in fact the table itself. What about 'doesnt need to have index' - sorry, this smells of bad design (actually locking of entire table is not good behavior either, but this was not your question :)). All tables should have primary key, which itself is an index; you can create it as clustered (by default it is) and then you have all requirements fulfilled. – Arvo Oct 25 '17 at 13:52
  • @Arvo well as this [so question explain](https://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean) clustered index is something else , thats why i was confused. as for the set index option is for the all the index in the database or specified in the table ? – Moudiz Oct 25 '17 at 14:01
  • Index option is for single index only. – Arvo Oct 26 '17 at 06:22

1 Answers1

1

The locking mechanisms are not the same, but you do have some control in SQL Server for locking - you can specify with rowlock, with paglock or with (tablockx) for example on a query to take an exclusive table lock.

As with all such locks when you take control - you have to take responsibility for the blocking you can cause - so use carefully.

Docs with full descriptions : https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table

Andrew
  • 26,629
  • 5
  • 63
  • 86
  • yes in a query I can specify the type of lock [explained here](http://www.sqlteam.com/article/introduction-to-locking-in-sql-server) but in the documents it doesn't mention the table if it has lock. so my question is about the table locks not query , so as i understand from you the table has no locks right ?. – Moudiz Oct 25 '17 at 13:14
  • The table will only be locked if there is an operation, whether DDL or DML requiring the lock. – Andrew Oct 25 '17 at 13:28
  • if i am updating table T1 , then a table lock will happen on it right ? i cannot select or update on it on different session correct ? if i added hint on it then it can be row lock right ? – Moudiz Oct 25 '17 at 13:32
  • If you use a holdlock, tablockx in a transaction, it will take out an exclusive table lock for your update and hold it for the duration of the transaction. That may / may not prevent others selecting though, depending on the transaction isolation level being used. It will prevent updates. – Andrew Oct 25 '17 at 13:46