SQL gurus: question on transactions

Results 1 to 3 of 3

Thread: SQL gurus: question on transactions

  1. #1
    Join Date
    Dec 1969

    Default SQL gurus: question on transactions

    You can have a stored procedure that is just a list of sql statements:<BR><BR>SELECT * FROM mytable<BR>UPDATE mytable SET firstfield=1<BR><BR>Or you can wrap your SQL statements in a "transaction", allowing for error trapping within the SP.<BR><BR>BEGIN TRAN mystoredprocedure<BR>SELECT * FROM mytable<BR>UPDATE mytable SET firstfield=1<BR>IF @@ERROR != 0<BR> BEGIN<BR> --rollback the transaction!<BR> ROLLBACK TRAN mystoredprocedure<BR> --return error code<BR> SELECT 1 AS &#039;errorcode&#039;<BR> RETURN<BR> END<BR>COMMIT TRAN mystoredprocedure<BR><BR>I&#039;ve been getting deadlocks. Is it possible that my deadlocks are happending because I wrapped my statements in a transactions. I&#039;m not an expert at this, I just did it because I copied some code from a tutorial.<BR><BR>Thanks for the help.

  2. #2
    Join Date
    Dec 1969

    Default One question...

    When you do a "Select * from...", you are locking all those rows. So if someone hits the same stored proc in the meantime he will face a deadlock. You are doing a Pessimistic locking here.<BR><BR>Why not remove the remove the SELECT statement and just have the update which would is a optimistic locking and will not lock the table for long(may be a few seconds depening on the number of rows.)<BR>

  3. #3
    Join Date
    Dec 1969

    Default Yep

    this can cause a lot of deadlocks.<BR><BR>When you start a transaction, a &#039;page&#039; or row is locked. <BR><BR>In this case, it&#039;s not nessecary to use a transaction. You only use transactions if you need multiple statements to either all succeed or all rolled back. So you don&#039;t need it in the above scenario.<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts