September 21, 2011 at 10:46 am
Hi,
I am developing a Web Site and I hope to have many simultaneous users (normal users -- read only).
A part of the Web Site is dedicated to professionals who will update their data. Data is of course spread into several tables.
I am writing a lot of Stored procedures.
I read a lot about dead locks but never found good articles which practically explain how to do the following bullets (best practices).
Could you, gurus, either explain me how practically best write the stored procedures, or refer to clear and precise articles on the different topics here below?
Also, some tables could be updated "automatically" even when no professional apply modifications to their data (e.g. increment number of views or via triggers) but this is limited to a low number of tables/records. In the majority of the cases, updates take place on "request" of a professional (and the latter is not supposed to run multiple update sessions in parallel, but I cannot prevent this).
Finally, I suppose the ISOLATION LEVEL READ COMMITTED
1. INSERT only 1 record in ONE table
Is the table locked during the insertion? Do I need to write anything else but: INSERT INTO tbl (....) VALUES (....) ?
Are other parallel SELECT statements UPDATE on different records of the same table, blocked during the INSERT?
2. UPDATE only 1 record in ONE table
Is the record locked by default? Do I need to write anything else but: UPDATE tbl SET ... WHERE ... ?
Are other parallel SELECT statements or UPDATE on different records of the same table, blocked during the UPDATE?
I suppose it would be better to put the UPDATE statement in a TRANSACTION, just in case the record could be under deletion during the update?
3. UPDATE (or DELETE) many records in ONE table
Do I need to do something else than considering a TRANSACTION during these updates?
BEGIN TRY
BEGIN TRANSACTION
UPDATE tbl SET ..... WHERE ....
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
END TRANSACTION
Are other parallel SELECT statements (linked to the same records) blocked during the UPDATE?
4. Lot of activities within a transaction
Activities could include : INSERT, DELETE, UPDATE
Similar to bullet 3
5. Invoking Stored Procedure(s) from another Stored Procedure
If I invoke another stored procedure (stp2) from a stored procedure (stp1) within a transaction....
Is the transaction extended to the activities of stp2? If an error occurs at stp2 level, is the transaction of stp1 notified? (TRY...CATCH...)
How is the best way of implementing this? Also, if in stp2 I have another transaction that fails, how is the best way of notifying stp1 (via OUTPUT value and check on it?)
6. SELECT
Do I need to mention anything special in my stored procedures, in order to be certain that I won't suffer from any delays during the processing of the SELECT (e.g. while updates are being done in parallel)?
SELECT * FROM ....
7. Retry
What is the best way of implementing a "retry mechanism" in case of failure, within a transaction?
I know that these questions could seem to be stupid but I want to prevent the system from experiencing dead locks and slow down.
In advance, many thanks
September 21, 2011 at 12:54 pm
I'm not sure that deadlocks are really your concern here. Of course, this all depends on the data. Are each of these users going to be altering the same data? We developed a contract entry web application for my previous company and used simple insert, update, and delete stored procedures without any special locking concerns. Operations typically happen so fast that it is not a concern for simple web applications. Deadlocks only occur when operation 1 is stopping operation 2 from completing AND operation 2 is stopping operation 1 from completing; i.e. neither can complete until the other does causing a deadlock. In your situation, it sounds like you may potentially have some waits, but (again, not knowing exactly what is happening) not deadlocks. These bullet points you listed... Where did you get these and what are they "best practices" for?
Thanks,
Jared
Jared
CE - Microsoft
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply