Trying to Understand SQL server 2000
locking using SERIALIZABLE isolation level
My Goal : My database has a manual upload procedure that can
happen at any time during the working day by my fellow work mates
in different departments. Reports can be run at any time during the day
by the same workmates. I wish that when data is EITHER to be
read via SELECT statements for reports OR that data is
being INSERTED or DELETED that the tables be secured while
each process runs. Its is preferred that a user is LOCKED out
of a process if another user is using the same tables in another
process ( The only exception is when a user fires a process
that is purely a READ ONLY one).
Assume that the same tables are used in all three processes.
CLIENT A : I have a Transaction script like this used by an
client(MS Access). This client database is used for only
data inputs, and deletions. Its not a reporting client.
PROCESS ONE: INSERTING DATA
-- Set Blocking
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
--Code Function : Read data in a Load Table, data check it, if all ok load
data into a MainStoragetable
--Set back to SQL server default
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
PROCESS TWO: DELETING DATA
-- Set Blocking
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
--Code Function : Delete type by parameters
--Set back to SQL server default
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CLIENT B : I have another client (MS Access) that builds
reports on request from data tables managed by the client A.
PROCESS THREE: Load report table to send recordset to client B
-- Set Blocking
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
--Code Function : Read data from data table ( managed by Client A)
and insert records into report table. Using INSTER INTO via SELECT statements
--Set back to SQL server default
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
I believe SERIALIZABLE means that no updating or deleting or
inserting data can occur, BUT READING data can.
My understandings are using SERIALIZABLE here that..
1) While process 3 is running, process 1 and 2 cannot run.
2) While process 1 is running, 2 cannot, but 3 can run and
it will not read phantoms, it reads data before process 1 was started.
3) While process 2 is running, 1 cannot, but 3 can run and
it will not read phantoms, it reads data before process 2 was started.
4) When transactions process is used with “SET TRANSACTION ISOLATION
LEVEL SERIALIZABLE” all tables used between BEGIN TRANS and
the COMMIT/ROLLBACK are allocated the locking isolation level
of SERIALIZABLE, no matter what SQL statement is being used
upon them ( ie SELECT, UPDATE, INSERT, DELETE).
5) I understand that when process 3 is running by User A,
User B cannot run process 3 until User A process is completed.
If my five unsertanding are correct then “SET TRANSACTION ISOLATION
LEVEL SERIALIZABLE” will achive all I require.
Is this correct, have I missed anything, is there a better way,
do I have a other concurrency risks to consider ?