Tag Info

New answers tagged

0

Assuming the availability of an index to provide the requested sort order (SomeColumn ASC) the sample query you posted will in all likelihood take a row lock, even where the index in question is not covering for the query. The query processor is smart enough to know that reading a single row from one end of an index (possibly followed by a lookup to ...


0

The lock spike coincides with a spike in page faults in MMS, which means that for that period of time (which looks pretty brief), there is data being paged in off disk (that is: not in memory). Since you mention that you are doing updates I would guess that something about the updates during that time frame is hitting a rarely used section of the data that ...


0

Following query gives details of all locks. SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name FROM V$Locked_Object A, All_Objects B WHERE A.Object_ID = B.Object_ID


0

It is possible that current replication thread is taking a lot of time updating a (big) change to a (big) table causing a long lock on the table. You may want to examine what the sql thread is trying to execute using mysqlbinlog with --start-position parameter. Hope this helps.


4

I think this does what you need. USE 'yourDB' GO SELECT OBJECT_NAME(p.[object_id]) BlockedObject FROM sys.dm_exec_connections AS blocking INNER JOIN sys.dm_exec_requests blocked ON blocking.session_id = blocked.blocking_session_id INNER JOIN sys.dm_os_waiting_tasks waitstats ON waitstats.session_id = blocked.session_id ...


2

RCSI applies only to reads. Writes take normal locks. See Understanding Row Versioning-Based Isolation Levels: [RCSI] uses update locks on the data rows selected. Acquires exclusive locks on actual data rows to be modified. No update conflict detection. So your expectation that under RCSI you cannot block is wrong. However, you have a valid point on ...


7

Mark's answer cleared up a lot of my confusion, but I wanted to post my findings after I tested this using NetBalancer to emulate latency. I had my local machine call a remote SQL server and execute both SELECTs and INSERTs on a table within a small transaction. On the remote machine, I connected to the local SQL instance and used a WHILE loop to ...


14

If client takes long time to receive data and in turn send acknowledgement to SQL Server that it has received the data SQL Server has to wait, due to this wait SQL Server will not release the locks held by the query unless acknowledgement is received from client. This is not accurate, it is dependent on the isolation level. At the default READ ...


0

If I'm making a single call to an MSSQL database over a high-latency network, will table locks occur due to that latency? When a query is fired and completed by SQL Server it produces the the results ,place it in output buffer and send it to client which then fetch the result from the Output buffer. SQL Server will not release the locks held by the ...



Top 50 recent answers are included