REQ: Best practices locking, truncating, processing data d..

On Jan 31, 9:40 am, "Mark S." wrote:
> 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

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/
 >> Stay informed about: REQ: Best practices locking, truncating, processing data d.. 

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

"Alex Kuznetsov" wrote in message

> On Jan 31, 9:40 am, "Mark S." wrote:
>> 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
>
> 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/
>
 >> Stay informed about: REQ: Best practices locking, truncating, processing data d.. 

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
 >> Stay informed about: REQ: Best practices locking, truncating, processing data d.. 

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
 >> Stay informed about: REQ: Best practices locking, truncating, processing data d.. 

On Jan 31, 9:25 pm, "Mark S." wrote:
> 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.
>

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/
 >> Stay informed about: REQ: Best practices locking, truncating, processing data d.. 

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
 >> Stay informed about: REQ: Best practices locking, truncating, processing data d.. 

Henrick,

> Would this work?

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
 >> Stay informed about: REQ: Best practices locking, truncating, processing data d.. 

Mark,

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

Henrik
 >> Stay informed about: REQ: Best practices locking, truncating, processing data d..