Difference between row lock and table lock

Difference between row lock and table lock

CREATE TABLE TBL_COURSE

(

ID INT PRIMARY KEY,

NAME VARCHAR(10) NOT NULL,

COURSE VARCHAR(10) NOT NULL,

BRANCH VARCHAR(10) DEFAULT 'HYDERABAD'

)

INSERT INTO TBL_COURSE (ID,NAME,COURSE) VALUES (102,'KALYAN','SQL_SERVER') -- updating sucessfully

INSERT INTO TBL_COURSE(ID,NAME,COURSE) VALUES (101,'PAWAN','MSBI')

Example For RowLock :

Execute below query in one query window.

BEGIN TRANSACTION

SELECT * FROM TBL_COURSE WITH(Rowlock) WHERE ID = 101

--SELECT * FROM TBL_COURSE WITH(rowlock, HOLDLOCK) WHERE ID = 101

WAITFOR DELAY '00:00:15'

ROLLBACK TRANSACTION

Execute this below query in some other query window :

update TBL_COURSE set course='SQL_SERVER' where ID=101 --SQL_SERVER,MSBI

check the data in both window.

Example For Tablock :

BEGIN TRANSACTION

SELECT * FROM TBL_COURSE WITH(TABLOCK) WHERE ID = 101

--SELECT * FROM TBL_COURSE WITH(TABLOCKX, HOLDLOCK) WHERE 0 = 1

WAITFOR DELAY '00:00:15'

ROLLBACK TRANSACTION

Execute this below query in some other query window :

update TBL_COURSE set course='SQL_SERVER' where ID=101 --SQL_SERVER,MSBI

check the data in both window.

After executing above queries check the data in different window you will get know how locks will effect the the result.

If you want understand clearly means try to add more rows in table and check it out.

If I’m wrong please let me know.