Sometimes we find that some "orphan" web transactions leave seats locked for several hours and even days. We are trying to find a way to detect these locked seats so we can release them immediately. Although it is easy to find the seats that are locked, we don't know how to find if they are being held by current web purchases. Does anyone knows if there is a way to find when a seat has been locked? That way we can identify the seats that have been locked for some time, e.g .1 hour.
Thanks
Business Analyst The National Ballet of Canada 470 Queens Quay West Toronto, Ontario M5V 3K4 P: 416 345 9686 x453 F: 416 345 8323
Mark,
Do you run TP_CLEANUP_CONNECTION after you run this script? I have a seat locked that the procedure won’t unlock. You say it uses the locked_by values and then executes the procedure but I don’t see in your script where it runs it.
Gloria
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Mark Ridley Sent: Thursday, November 21, 2013 6:09 AM To: Gloria Ormsby Subject: Re: [Tessitura Technical Forum] Finding when a seat has been locked
We had a similar issue where when we had some network glitches seat server wouldn't release seats.
The following sql is part of an automated job that unlocks the seats which is why the date parameter is not as tight ie we look at seats that were locked before yesterday and limit is to only ones locked by the anonymous web user ie added to basket but user never logged in. The process only returns the locked_by values as it then uses that value to execute tp_cleanup_connnection
select distinct ps.locked_by from TX_PERF_SEAT ps (NOLOCK) join T_WEB_ORDER o (NOLOCK) on ps.order_no=o.order_no where ps.customer_no=<anonymous web user account> and o.order_dt <convert(varchar(10),DATEADD(day,-1, GETDATE()),120) and seat_status=3
Mark
From: Fernando Margueirat <bounce-fernandomargueirat4910@tessituranetwork.com> Sent: 11/20/2013 2:18:00 PM
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
That was just the snippet for finding the seats the whole code is
declare @list as table ( session int not null primary key)
insert into @listselect distinct ps.locked_byfrom TX_PERF_SEAT ps (NOLOCK)join T_WEB_ORDER o (NOLOCK) on ps.order_no=o.order_nowhere ps.customer_no=<anonymous web user id>and o.order_dt <convert(varchar(10),DATEADD(day,-1, GETDATE()),120)and seat_status=3
declare @session int
while (select COUNT(*) from @list) >0begin
select top 1 @session=sessionfrom @list
exec [dbo].[TP_CLEANUP_CONNECTION] @session_id=@session
delete @list where session=@session
end
Your code saved my butt tonight! I had about 3 minutes to spare! Thanks so much.