begin tran does the row level lock or table level lock

Question

when we write begin tran and update few employee data then other transaction can read those employee data that we are updating with begin tran. 

lock placed on table level or on only those rows which are getting change? 

execute from a session 1

begin tran
        update emp set Salary=999 where ID=1
        waitfor delay '00:00:15'
        commit

when i execute this below query from session2

set transaction isolation level read committed
select Salary from Emp where ID=2

then i saw i got result after 15 sec, so it means begin trans place lock on table instead of rows.

Answer 1

i got a nice article link http://www.besttechtools.com/articles/article/sql-server-isolation-levels-by-example

Answer 2

Hello,

In SQL Server we have Lock Escalation (Database Engine) which controls lock level; it starts with row lock, after 5,000 locks exist, it escalate to page lock and after 5,000 page locks to table lock.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 3

It really depends on your isolation level

Read about various isolation levels and their effect here

see the illustration here

https://www.mssqltips.com/sqlservertip/2977/demonstrations-of-transaction-isolation-levels-in-sql-server/

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 4

CREATE TABLE employee (id INT NOT NULL PRIMARY KEY, empname VARCHAR(20))

INSERT INTO employee VALUES (1,'John')
INSERT INTO employee VALUES (2,'Tim')
INSERT INTO employee VALUES (3,'Allen')
INSERT INTO employee VALUES (4,'Todd')

---First conection
BEGIN TRAN
UPDATE employee SET empname='Uri' WHERE id=2
---Rollback TRAN

----Second connection  window

SELECT * FROM employee  ----this statement is locked under default transaction isolation level

SELECT id,empname FROM employee WHERE id=2  ---this statement is locked under default transaction isolation level

But you can read this one

SELECT id,empname FROM employee WHERE id=1

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 5

Lock escalation, as mentioned my Olaf is one possibility for the bloc (row level locks escalated to table level log).

Another is if you don't have an index on the ID column. Without such index, query 2 need to scan and look at each value to see if it equals 2. And it cannot do that for the row where ID = 1 because of query 1 having an exclusive lock on that row.

sp_lock (and sys.dm_tran_locks etc) and give you more insight to what locks are actually being held, as well as who are blocked.

---

Tibor Karaszi, SQL Server MVP (Web Blog)