Thursday, March 29, 2012
Database concurrent access issue
I have this .NET application that inserts lucky draw entries into SQL server. Each entry may have a range of values that the winning number will be drawn from. For example,
Entry 1: [1, 2]
Entry 2: [3, 8]
Entry 3: [9, 10]
Entry 4: [11, 11]
Entry 5: [12, 20]
The winning number will be picked from [1, 20]. Given this scenario, what is the best design that can handle the concurrency issue? If two entries are input at the same time, how to make sure it won't have the same starting value? Anyway to lock a table when one is accessing, disallowing other to run select query?
Thanks.You'll have to explain that better.|||For example, Entry 3: [9, 10] means Entry 3 has the number range starting from 9 and ending at 10.
The winning number will be drawn from the number range.
You'll have to explain that better.|||Clear as mud.
If you want to ensure two entries do not share the same starting value, then make the starting value the primary key or a unique index.|||blindman: qxz wants to validate new entries to ensure that it does not conflict with any existing entry and wants to enforce this validation in case of multiple users updating the Db same time...
qxz: a table lock is needed to ensure proper insert. remember explicit locks can have negative effect on performance
create procedure CheckIt (@.StVal int, @.EndVal int)
as
BEGIN TRANSACTION
declare @.CheckFailed char(1)
set @.CheckFailed = 'N'
if exists (select * from MyTable with (tablockx) where @.StVal between StartVal and EndVal)
set @.CheckFailed = 'Y'
if exists (select * from MyTable where @.EndVal between StartVal and EndVal)
set @.CheckFailed = 'Y'
if @.CheckFailed = 'Y'
begin
ROLLBACK TRANSACTION
return 0
end
else
begin
insert into MyTable .....
COMMIT TRANSACTION
return 1
end
Monday, March 19, 2012
Database Audit
Hi,
We are currently porting our POINT OF SALE system to SQL Server 2005. In our existing software we log database changes (inserts, amendments and deletions) to a flat acsii files so that these files can be FTP'd to remote sites (largest number of sites 100+) and processed on to replicate the data.
I have looked at REPLICATION in MSSQL 2005 but we need two way replication and additional processing. Therefore I dont think we will be able to use this feature.
Therefore what I wanted to do was to try and setup an automated way of capturing database changes to all tables within the database and log these changes to XML to be shipped out to remote sites. Unfortunatly I can only find TABLE TRIGGERS which would require creating 100's of triggers as we have 100's of tables.
Is there anyway of setting up MSSQL server to automatically do something like this... I was looking to see if there was a DATABASE TRIGGER which could perform this action but I cant see anything...
Can anyone advise or is there a simpler way of doing this ?
Ray
I think that Log Shipping may be a useful solution in your situation.
Refer to Books Online, and the topic: Log Shipping.