Clearing stranded active locks

A stranded lock is an active lock retained on a record, even though the session associated with that lock is no longer active. If a stranded lock exists on a record, no other user can actively lock that record, although they could still read the record, using a get statement, for example. Stranded locks are most often caused by a user abnormally terminating his or her session.

When the session for a given session ID is terminated, either normally or abnormally, the session’s connection to the server is dropped. When this occurs, the session table associated with that session ID is deleted from tempdb by the SQL server.

If a session associated with a given session ID is concluded normally, Dexterity checks the DEX_LOCK table once the session table is deleted and removes all rows containing that session ID in the session_id column. Thus, it removes any remaining locks associated with that session ID.

When a session associated with a given session ID is concluded abnormally, the session table is removed but Dexterity does not automatically clear locks associated with that session ID. The locks must be cleared by some other means.

If your application integrates with Microsoft Dynamics GP, stranded locks will be cleared for you. Stand-alone applications, however, must include a means to clear these locks.

To ensure that stand-alone applications recognize and remove stranded locks on a regular basis, we suggest you include a stored procedure in your application similar to the one shown in the following script. You can call this stored procedure from your Dexterity login script, thereby checking for and removing stranded locks each time a user logs in.

create procedure Release_Strandeds
as
declare @DEX_LOCK_ID char(15)
declare @iSession_ID int
declare T_cursor CURSOR for select session_id from tempdb..DEX_LOCK 
set nocount on
OPEN T_cursor 
FETCH NEXT FROM T_cursor INTO @iSession_ID
WHILE (@@FETCH_STATUS <> -1)
begin
	select @DEX_LOCK_ID = stuff( '##DL0000000000', 14 - 
	datalength(rtrim(ltrim((convert(char(10),@iSession_ID)))))+1, 
	datalength(rtrim(ltrim((convert(char(10),@iSession_ID ))))),
	convert(char (10), @iSession_ID))

	if not exists ( select name
		from
			tempdb..sysobjects
		where
			tempdb..sysobjects.name = @DEX_LOCK_ID)
	begin
		/*This is a stranded lock, so clear it.*/
		delete from tempdb..DEX_LOCK where
	 tempdb..DEX_LOCK.session_id = @iSession_ID
	end
	FETCH NEXT FROM T_cursor INTO @iSession_ID
end
DEALLOCATE T_cursor
return
go

To create the stored procedure, copy the code shown here and paste it into a utility such as ISQL that can execute SQL statements. When you execute the SQL statements shown here, the Release_Strandeds stored procedure is created. Then you can create a prototype procedure and use the call sproc statement to call this stored procedure from within your Dexterity application. Calling stored procedures is described in Stored Procedures.

Documentation Feedback

---