Database Reference
In-Depth Information
processing the same rows, and serializing the data loading process is one of the options we have. Exclusive (X) table
lock would not work, because it blocks any table access, rather than data loading. Implementing serialization on the
application server level is not a trivial task either. Fortunately, application locks could do the trick.
Let's assume that we have the table shown in Listing 22-1. For simplicity sake, there is a column called
Attributes
that represents all of the row data.
Listing 22-1.
Table structure
create table dbo.RawData
(
ID int not null,
Attributes char(100) not null
constraint DEF_RawData_Attributes
default 'Other columns',
ProcessingTime datetime not null
constraint DEF_RawData_ProcessingTime
default '2010-01-01',
constraint PK_RawData
primary key clustered(ID)
)
There are two important columns:
ID
, which is the primary key, and
ProcessingTime
, which represents the
time the row was loaded for processing. We should use UTC time here to support the situations in which application
servers are residing in the different time zones, as well as to prevent issues when the clock is adjusted to Daylight
Saving Time. This column also helps to prevent other sessions from re-reading the data while it is still processing.
Boolean (bit) column would not work, because, if the application server crashes, the row would remain in the table
forever. With the time column, we can read it again based on some processing timeouts.
Now let's create the stored procedure that reads the data as shown in Listing 22-2.
Listing 22-2.
Stored procedure that reads the data
create proc dbo.LoadRawData(@PacketSize int)
as
begin
set nocount on
set xact_abort on
declare
@EarliestProcessingTime datetime
,@ResCode int
declare
@Data table
(
ID int not null,
Attributes char(100) not null,
primary key(ID)
)
