sql server programming: [quoted text, click to view]

Mark,

Sounds familiar, I described a similar scenario here:

http://www.devx.com/dbzone/Article/32957/0/page/3

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

[quoted text, click to view]

I would try to use DELETE and have OUTPUT clause populate your table
variable. I am not sure if your DELETEs could embrace in a deadlock.
So, if there a need to serialize, I would use a toggle table:

CREATE TABLE Toggle(Toggle INT)
go
INSERT Toggle VALUES(0)
go

In the body of the INSTEAD OF trigger, I would first of all acquire an
update lock on the toggle table:

UPDATE Toggle SET Toggle = 1 - Toggle

Good luck!

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

Another way to serialize processes is to use sp_getapplock. I usually
prefer this to a toggle table since it gives you finer control over the
locking types and timing. For example you can release locks before the
current transaction ends, or keep them even after the current transaction
ends, or, of course, have them automatically release when the current
transaction ends.

Tom

[quoted text, click to view]

Hello,

We have a high traffic web server farm where each server captures data and
using c# bulk insert stores the data in tbDataDump (SQL Server 2005) almost
every second throughout the day.

I've been using a INSTEAD OF INSERT trigger to apply business rules to the
data and then store the results in their proper permanent tables. The
problem is each trigger launches it's own process resulting concurrency
issues.

So I thought I'd drop the trigger, and instead schedule a stored procedure
to process tbDataDump. In my mind, this stored proc locks tbDataDump,
selects the entire table into a table variable, truncates tbDataDump,
releases the lock and then continues it's processing. But then again, I know
enough to be dangerous...

These two articles where very interesting about snap shot isolation, but I
don't understand it well enough.
-- http://msdn2.microsoft.com/en-us/library/ms345124.aspx
-- http://www.informit.com/articles/article.asp?p=327394&seqNum=2&rl=1

TIA for your thoughts and code hints.

M

Alex,

Thank you for the link, some good reading, but still doesn't clear it up for
moi.

Is there a good way to lock the table so the numerous incoming bulk inserts
have to wait a 1 mil sec while the select and trucate statements execute?

M

Mark,

I'm not that experienced in high-volume sites, but have you considered
a new field to your tbDataDump; processed (tinyint) default 0,
when a job grabs things to do, it runs an update to set Processed = 1,
and when finished it sets Processed = 2.

This would not involve table locks on tbDataDump.
Then you have an archive job to delete 100 rows at a time from
tbDataDump which are processed and older than 2 days.

Would this work?

Best regards,
Henrik Staun Poulsen

[quoted text, click to view]

One more thing: on 2000 I always have my staging tables in a separate
database where evereone is dbo, so that they can truncate and bulk
insert. However the same users are not dbo in the databases for my
permanent data - that is one more reason to move staging tables to a
different database, that is to safeguard the permanent tables. I am
not sure how exactly security has changed in 2005, but are your users
running the processes as dbo? If yes, that IMO is not the best
practice.

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

Alex: Deleting with Output is a great idea, however I would have to move
tbDataDump to a new db instance where logging was set to simple. The shear
number of deletes wouldn't play nice with the log files.

Tom: Thanks for the sp_getapplock tip, it's going into testing.

Group: Here's what I came up with, feel free to suggest alterations.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbTest](
[col1] [int] NOT NULL
) ON [PRIMARY]

DECLARE @table TABLE (
col1 int
)

SET NOCOUNT ON;
DECLARE @result INT;
BEGIN TRANSACTION;
EXEC @result = sp_getapplock @Resource = 'myLock', @LockMode = 'Exclusive';

IF @result >= 0 -- from bol: >= 0 (success), or < 0 (failure)
BEGIN
-- select @result;
-- select tbDataDump into table var
INSERT INTO @table
SELECT * FROM tbTest
-- truncate table
TRUNCATE TABLE tbTest
-- clean up
EXEC @result = sp_releaseapplock @Resource = 'myLock';
COMMIT TRANSACTION;
END
ELSE
BEGIN
ROLLBACK TRANSACTION;
-- clean up
EXEC @result = sp_releaseapplock @Resource = 'myLock';
END;
GO

Henrick,

[quoted text, click to view]

Your idea has merit but on high volume sites it soon isn't appropriate. With
+600 rows per second coming in the table size grows to many millions of rows
per day. This means the delete statement has to scan the table for it's
matches, this simple act adds concurrency complexity. When dealing with high
traffic, real-time, db coding every line of code, every table scan, every
assumption, every log entry, every constraint/index, everything must be
questioned, removed from the equation unless it's absolutely necessary and
then tested again and again. It's why my hair is graying!

Early today I put the table lock code, mentioned previously, under heavy
live load and it's working well so far. If it causes problems I'll move to
the delete output method like so:

declare @table table (
col1 int
)
DELETE tbTest OUTPUT DELETED.col1 INTO @table;
select * from @table

Hope that helps.

M

Mark,

Ok, with 600+ rows a second, you have a busy system.
Good luck with the other stuff.

Henrik