lowendtech


Hi guys,

I have a question regarding a locking scheme in MSSQL I hope you guys can help. In Sybase, I am able to specify datarow locking in DDL (ex. create table, alter table). Can I do the same in MSSQL or is there an equivalent option in CREATE TABLE statement in MSSQL I came across a few articles in MSDN about datarow locking and it seems to me that MSSQL only allows locking through DML... Is that true Thanks.




Re: Can we specify datarow locking in Create Table statement

asiaindian


Hello,

A row lock in SQL server may be applied using the 'ROWLOCK" hint as below ...

UPDATE Users WITH (ROWLOCK)
SET Username = 'fred' WHERE Username = 'foobar'

Please read http://www.sql-server-performance.com/articles/per/lock_contention_nolock_rowlock_p1.aspx

Hope this helps.

Regards....







Re: Can we specify datarow locking in Create Table statement

ggciubuc

In Sybase locking schemes allow table owners to specify what data in a table gets locked and how; in MS SQL the things are controlled lets's say at transaction level (or during transaction) and you can not specify that at creating table.

How is made locking transaction can be controlled by SET TRANSACTION ISOLATION LEVEL
and table hints.

I quote from books online (look for locking [SQL Server]):

"The isolation level can be overridden for individual query or DML statements, if necessary, by specifying a table-level hint. Specifying a table-level hint does not affect other statements in the session. We recommend that table-level hints be used to change the default behavior only when absolutely necessary."