update query fails as table gets locked by select query (sql blocking issue).
I have a perl script which updates some table records in sqlserver 2000, the flow is like below:
* A select query
This select query selects IDs(with other records too)
* An update query
This update query updates a bit column from 0 to 1 for the IDs retrieved from above select query.
The problem I am facing is: the update query can not update the records because the table is being locked by the first select query( only when select query returns records more than some threshold, let say if it return 18 records update runs fine but if select returns more than 18 records, update fails as table gets locked by select query.
Please help me out.
Thanks
Software/Hardware used:
Software/Hardware used:
ASKED:
May 1, 2008 9:32 PM
UPDATED:
May 2, 2008 10:48 PM
Answer Wiki:
This is probably because the lock on the table is escalating. There are a few ways to prevent this.
1. Have both queries run in the same session.
2. Close the session to the database that the select statement is using before you start running update statements.
3. Use a stored procedure to return the values as well as flip the value in the bit column, so that you only have to make a single call.
4. Add the NOLOCK hint to your SELECT statement so that it won't lock the records. You may get a problem with invalid data being shown as if anyone else has a lock on a record, that lock will not be honored.
<pre>SELECT *
FROM Table WITH (NOLOCK)
WHERE Something = SomethingElse</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
Here I am more keen to know the root cause of the sql blocking. I ran sp_blocker_pss80 stored proc suggested at http://support.microsoft.com/kb/271509/EN-US/ to closely look into this issue, I am posting the output of this stored proc for your inspection, after going through the log at first glance it seems like issue is related to network IO. Any idea??
8.2 Start time: 2008-05-02 15:10:12.717 0
SYSPROCESSES THEEDGE\NEXUS 134219767
spid status blocked open_tran waitresource waittype waittime cmd lastwaittype cpu physical_io memusage last_batch login_time net_address net_library dbid ecid kpid hostname hostprocess loginame program_name nt_domain nt_username uid sid sql_handle stmt_start stmt_end
—— —————————— ——- ——— —————————————————————————————————————————————————————————————————————————————————————- ——– ———– —————- ——————————– ———– ——————– ———– ————————– ————————– ———— ———— —— —— —— ——————————————————————————————————————————– ———– ——————————————————————————————————————————– ——————————————————————————————————————————– ——————————————————————————————————————————– ——————————————————————————————————————————– —— —————————————————————————————————————————————————————————— —————————————— ———– ———–
66 runnable 0 0 0×0800 9735 SELECT NETWORKIO 250 0 49 1900-01-01 00:00:00.000 2008-05-02 15:10:02.717 001B78735686 LPC 7 0 6852 THEEDGE 13264 bld ActivePerl 0 0x9C2D61033CBBB84F9A9F3F3369ABF70300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000700C50EFB0CC0E00F540000000000000000 4 -1
67 sleeping 66 1 TAB: 7:1510296440 [] 0×0008 9703 UPDATE LCK_M_IX 0 0 3 2008-05-02 15:10:03.013 2008-05-02 15:10:02.980 001B78735686 LPC 7 0 4004 THEEDGE 13264 bld ActivePerl 1 0x9C2D61033CBBB84F9A9F3F3369ABF70300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000700B656FD03C0A0C1530000000000000000 0 -1
ESP 13
SYSPROC FIRST PASS
spid ecid waittype
—— —— ——–
66 0 0×0800
67 0 0×0008
Blocking via locks at 2008-05-02 15:10:12.717
SPIDs at the head of blocking chains
spid
——
66
SYSLOCKINFO
spid ecid dbid ObjId IndId Type Resource Mode Status TransID TransUOW
—— —— —— ———– —— —- —————- ———— —— ——————– ————————————
67 0 7 0 0 DB S GRANT 0 00000000-0000-0000-0000-000000000000
66 0 7 0 0 DB S GRANT 0 00000000-0000-0000-0000-000000000000
66 0 7 1657772963 0 TAB IS GRANT 1353119 00000000-0000-0000-0000-000000000000
66 0 7 34099162 0 TAB IS GRANT 1353119 00000000-0000-0000-0000-000000000000
66 0 7 1510296440 0 TAB S GRANT 1353119 00000000-0000-0000-0000-000000000000
67 0 7 1510296440 0 TAB IX WAIT 1353148 00000000-0000-0000-0000-000000000000
ESL 0
DBCC SQLPERF(WAITSTATS)
Wait Type Requests Wait Time Signal Wait Time
——————————– ————– ————– —————-
MISCELLANEOUS 8.0 0.0 0.0
LCK_M_SCH_S 0.0 0.0 0.0
LCK_M_SCH_M 0.0 0.0 0.0
LCK_M_S 1.0 1062.0 0.0
LCK_M_U 0.0 0.0 0.0
LCK_M_X 0.0 0.0 0.0
LCK_M_IS 6.0 140765.0 0.0
LCK_M_IU 0.0 0.0 0.0
LCK_M_IX 168.0 5652466.0 0.0
LCK_M_SIU 0.0 0.0 0.0
LCK_M_SIX 0.0 0.0 0.0
LCK_M_UIX 0.0 0.0 0.0
LCK_M_BU 0.0 0.0 0.0
LCK_M_RS_S 0.0 0.0 0.0
LCK_M_RS_U 0.0 0.0 0.0
LCK_M_RIn_NL 0.0 0.0 0.0
LCK_M_RIn_S 0.0 0.0 0.0
LCK_M_RIn_U 0.0 0.0 0.0
LCK_M_RIn_X 0.0 0.0 0.0
LCK_M_RX_S 0.0 0.0 0.0
LCK_M_RX_U 0.0 0.0 0.0
LCK_M_RX_X 0.0 0.0 0.0
SLEEP 419983.0 3.4250582E+8 3.4221018E+8
IO_COMPLETION 13840.0 24545.0 0.0
ASYNC_IO_COMPLETION 10.0 16938.0 0.0
RESOURCE_SEMAPHORE 0.0 0.0 0.0
DTC 0.0 0.0 0.0
OLEDB 361939.0 1.8825596E+9 3.8566502E+8
FAILPOINT 0.0 0.0 0.0
RESOURCE_QUEUE 125834.0 1.0200108E+9 3.4012707E+8
ASYNC_DISKPOOL_LOCK 97.0 0.0 0.0
UMS_THREAD 0.0 0.0 0.0
PIPELINE_INDEX_STAT 0.0 0.0 0.0
PIPELINE_LOG 0.0 0.0 0.0
PIPELINE_VLM 0.0 0.0 0.0
WRITELOG 4342.0 20443.0 127.0
LOGBUFFER 228.0 441.0 16.0
PSS_CHILD 0.0 0.0 0.0
EXCHANGE 7.0 0.0 0.0
XCB 0.0 0.0 0.0
DBTABLE 0.0 0.0 0.0
EC 0.0 0.0 0.0
TEMPOBJ 0.0 0.0 0.0
XACTLOCKINFO 0.0 0.0 0.0
LOGMGR 0.0 0.0 0.0
CMEMTHREAD 279.0 156.0 156.0
CXPACKET 30665.0 1971760.0 459.0
PAGESUPP 267.0 0.0 0.0
SHUTDOWN 0.0 0.0 0.0
WAITFOR 295.0 3293019.0 3221954.0
CURSOR 0.0 0.0 0.0
EXECSYNC 0.0 0.0 0.0
LATCH_NL 0.0 0.0 0.0
LATCH_KP 0.0 0.0 0.0
LATCH_SH 60.0 504372.0 0.0
LATCH_UP 2.0 0.0 0.0
LATCH_EX 107306.0 4172431.0 2401608.0
LATCH_DT 0.0 0.0 0.0
PAGELATCH_NL 0.0 0.0 0.0
PAGELATCH_KP 0.0 0.0 0.0
PAGELATCH_SH 2459.0 1187.0 0.0
PAGELATCH_UP 17423.0 2794.0 0.0
PAGELATCH_EX 1349.0 0.0 0.0
PAGELATCH_DT 0.0 0.0 0.0
PAGEIOLATCH_NL 0.0 0.0 0.0
PAGEIOLATCH_KP 0.0 0.0 0.0
PAGEIOLATCH_SH 822.0 69221.0 0.0
PAGEIOLATCH_UP 2764.0 28411.0 0.0
PAGEIOLATCH_EX 80.0 1487.0 0.0
PAGEIOLATCH_DT 0.0 0.0 0.0
TRAN_MARK_NL 0.0 0.0 0.0
TRAN_MARK_KP 0.0 0.0 0.0
TRAN_MARK_SH 0.0 0.0 0.0
TRAN_MARK_UP 0.0 0.0 0.0
TRAN_MARK_EX 0.0 0.0 0.0
TRAN_MARK_DT 0.0 0.0 0.0
NETWORKIO 49935.0 2224208.0 0.0
Total 1140169.0 3.263202E+9 1.0736266E+9
(78 rows affected)
(78 rows affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*********************************************************************
Print out DBCC Input buffer for all blocked or blocking spids.
*********************************************************************
DBCC INPUTBUFFER FOR SPID 66
EventType Parameters EventInfo
————– ———- —————————————————————————————————————————————————————————————————————————————————————
Language Event 0
SELECT target._id as target_id, *
FROM target INNER JOIN
depot ON target.target_depot_id = depot._id INNER JOIN
change ON target.target_depot_id = change.change_depot_id AND target.target_change_number = change.change_id
WHERE (tar
(1 row affected)
(1 row affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC INPUTBUFFER FOR SPID 67
EventType Parameters EventInfo
————– ———- ———————————————————————-
Language Event 0 UPDATE target SET target_finished = 1 WHERE target._id = 1289201
(1 row affected)
(1 row affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*******************************************************************************
Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.
*******************************************************************************
DBCC OPENTRAN FOR DBID 7 [nexus]
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
End time: 2008-05-02 15:10:12.730