update query fails as table gets locked by select query (sql blocking issue).

15 pts.
Tags:
Perl
SQL Server 2000
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
ASKED: May 1, 2008  9:32 PM
UPDATED: May 2, 2008  10:48 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Shashanksingh19
    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 0x0800 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 [] 0x0008 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 0x0800 67 0 0x0008 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
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following