Based on Scott's recommendation to handle this on the database side rather
than the Java side, I have worked with my DBA to come up with the following
T-SQL script for our CAS database on MS SQL Server. It hard codes in a TGT
expiration period (1 hour) and deletes the TGTs, their corresponding STs,
and their corresponding TGTs. We would schedule this to run every 30
minutes on the database server. In talking with my DBA, he said that he
believes SQL Server will do a table lock if there are more than 5000 row
locks on a table. Because of this, he recommended deleting each TGT one by
one rather than deleting all of the TGTs at once. Does anyone have any
comments on this proposed solution to the locking problem we are having with
SQL Server? Here is the T-SQL:
declare @tgt varchar(255)
declare tgts cursor local fast_forward for
SELECT ID FROM TICKETGRANTINGTICKET
WHERE ((DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE())) -
(LAST_TIME_USED / 1000)) > 3600
open tgts
fetch tgts into @tgt
while @@fetch_status = 0
begin
DELETE FROM SERVICETICKET WHERE ticketGrantingTicket_ID =
@tgt
DELETE FROM TICKETGRANTINGTICKET WHERE
ticketGrantingTicket_ID = @tgt
DELETE FROM TICKETGRANTINGTICKET WHERE ID = @tgt
fetch tgts into @tgt
end
close tgts
deallocate tgts
Thanks for any help!
--
You are currently subscribed to [email protected] as:
[email protected]
To unsubscribe, change settings or access archives, see
http://www.ja-sig.org/wiki/display/JSG/cas-user
