I'm new to TSQL, still struggling in understanding some basic concepts:
My textbook provides an example: there three session(3 query windows, I will refer to them as Connection 1, Connection 2, and Connection 3)
Run the following code in Connection 1 to update a row in the Production.Products table, adding 1.00 to the current unit price of 19.00 for product 2.
BEGIN TRAN; UPDATE Production.Products SET unitprice += 1.00 WHERE productid = 2;
Run the following code in Connection 2 to try to query the same row
SELECT productid, unitprice FROM Production.Products WHERE productid = 2;
query the dynamic management view (DMV) sys.dm_tran_locks in Connection 3.
SELECT -- use * to explore other available attributes request_session_id AS spid, resource_type AS restype, resource_database_id AS dbid, DB_NAME(resource_database_id) AS dbname, resource_description AS res, resource_associated_entity_id AS resid, request_mode AS mode, request_status AS status FROM sys.dm_tran_locks;
and I gets an output:
we can see that spid 53( second query window) is blocked and wait for spid 52( firstquery window) to release the lock. But I don't understand why the page (720...440) can have both intent share(IS) and intent exslusive(IX)? what does it mean?