Έχω φτιάξει ένα web service method το οποίο δέχεται ως input μία νέα καταχώρηση για τον SQL Server, και την περνάει στη βάση μέσω stored procedures.

Προσπάθησα να το καλέσω ασύγχρονα, ώστε να μην κολλάει το κυρίως πρόγραμμα όσο γίνεται η διαδικασία, όμως επειδή αυτό στέλνει με For... Each πολλές εγγραφές, σε κάποιο σημείο μου πετάει Exception ότι ο SQL Server εντόπισε deadlock και επέλεξε να σταματήσει τη συγκεκριμένη transaction.

Πώς μπορώ να το ξεπεράσω αυτό?

Χωρίς να δούμε κώδικα είναι λίγο δύσκολο να βοηθήσουμε. Δεν κατάλαβα πως μπλέκεται το ασύγχρονο με το For Each.

---

Vir prudens non contra ventum mingit

OK. Λοιπόν έχουμε:

Client:

Private Sub btnStart_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStart.Click
    StartTransmission()
End Sub

Private Service As New DBService

Private Sub StartTransmission()
    For Each entry As Person In Me.Persons
        Dim Result As IAsyncResult = Service.BeginUpload(entry, AddressOf Responder, entry)
    Next
End Sub

Private Sub Responder(ByVal Result As IAsyncResult)
    Service.EndUpload(Result)

    If (InvokeRequired) Then
        Invoke(New MyDelegate(AddressOf test), New Object() {CType(Result.AsyncState, Person)})
    End If
End Sub

Private Delegate Sub MyDelegate(ByVal prsn As Person)

Private Sub test(ByVal prsn As Person)
    Console.WriteLine("Uploaded: " & prsn.Name)
End Sub


Web Service:

<WebMethod(Description:="Upload a new entry")> _
Function Upload(ByVal prsn As Person) As Boolean

    Dim msConn As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=addressbook;Integrated Security=True;")
    Dim myCmd As SqlCommand

    myCmd = New SqlCommand("AddPerson", msConn)
    myCmd.CommandType = CommandType.StoredProcedure

    myCmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = prsn.Name
    ....
   
    myCmd.Connection.Open()
    myCmd.ExecuteNonQuery()
    myCmd.Connection.Close()
   
    Return True
End Function


Και συγκεκριμένα πετάει αυτό, μετά από λίγη ώρα:

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.

Είναι πολύ καλή επιλογή να καλέσεις το service σου ασύγχρονα για να δώσεις καλύτερη αίσθηση και έλεγχο στον τελικό χρήστη. Αν όμως έχεις πολλά Person instances να στείλεις τότε δεν είναι και τόσο αποδοτική η λύση. Επειδή η αποθήκευση ενός person είναι αρκετά ελαφρυά δουλειά, θα μπορούσες να περνάς ένα array από Person στο service σου για να μειώσεις τα threads και το overhead των κλήσεων. Το array θα μπορούσε να έχει όλα τα Person objects ή ομάδες αυτών (π.χ. 30 persons μαζί).

Κατά τα άλλα, σχετικά με το μήνυμα που παίρνεις, ο sql server κλειδώνει τον πίνακα κατά το update, by default. Και επειδή καλείς πολλές φορές τον server και αυτός κάνει πολλές αποθηκεύσεις ταυτόχρονα τον μπουκώνεις λίγο. Διάβασε εδώ λίγο για το πως λειτουργούν τα locks όταν κάνεις update και δες στο τέλος του άρθρου τις εναλλακτικές έχεις.

http://articles.techrepublic.com.com/5100-6329_11-5181472.html

---

Dimitris Papadimitriou Software Development Professional

Οι απαντήσεις παρέχονται για συγκεκριμένες ερωτήσεις και χωρίς καμιά εγγύηση. Διαβάστε επίσης τους όρους χρήσης.

Αντί να έχεις το loop στον client και να καλείς το web service πολλαπλές φορές (όπου προφανώς ξεκινάει το insert, κλειδώνει εγγραφές και πριν προλλάβει να τελειώσει, ανοίγει εκ νέου connection και προσπαθεί να ξανακάνει insert πάνω στα κλειδωμένα), πέρνα όλα μαζί τα Person instances και κάνε το loop στον server. Με αυτόν τον τρόπο, πέρα του ότι θα λύσεις το πρόβλημα, θα έχεις και πιο efficient κώδικα αφού θα κάνεις μόνο ένα web method call (πράγμα που κοστίζει) και δεν θα ανοιγοκλείνεις το connection στη βάση (πράγμα που επίσης κοστίζει).

---

Vir prudens non contra ventum mingit

Εντάξει, το έκανα έτσι και δεν υπάρχει προς το παρόν πρόβλημα. Η βελτίωση από πλευράς απόδοσης είναι επίσης σημαντική!

Για να αποφύγω περίεργες συμπεριφορές, είχα τα Stored Procedures μου να περιέχουν Transaction εσωτερικά.

Σκέφτομαι μήπως θα ήταν καλύτερο να βγάλω όλα τα Transactions από τον κώδικα των SPs, και να κάνω ένα Transaction για κάθε εισαγωγή, σε κάθε επανάληψη:

msConn.Open()
For Each prsn As Person In Persons
    trnNew = msConn.BeginTransaction
    Try
        Upload(prsn)
        trnNew.Commit()
    Catch ex As SqlException
        trnNew.Rollback()
        MsgBox(ex.Message.ToString)
    End Try
Next
msConn.Close()

Πιστεύετε ότι θα είναι πιο σωστό?

Φαντάζομαι ότι εννοείς να το κάνεις αυτό στον server. Καταρχήν θα βάλεις το BeginTransaction πριν το For Each και το Commit μετά το Next. Για την ακρίβεια πρέπει να το κάνεις, αν στην ίδια κλήση προς τον server κάνεις περισσότερα από ένα πράγματα προς τη βάση (π.χ. πολλά inserts).

Αν εννοείς στον client, φυσικά όχι. Το transactions έχει να κάνει με την βάση και πρέπει να γίνει στον server. Στον client δεν πρέπει να υπάρχουν Connection objects και άλλα τέτοια. 

---

Dimitris Papadimitriou Software Development Professional

Οι απαντήσεις παρέχονται για συγκεκριμένες ερωτήσεις και χωρίς καμιά εγγύηση. Διαβάστε επίσης τους όρους χρήσης.

Μέχρι τώρα αυτό το είχα στα StoredProcedures που είχα φτιάξει για τη βάση. Δηλαδή:

CREATE PROCEDURE AddPerson
...
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

....
  
COMMIT TRANSACTION
RETURN
GO

Οπότε τώρα σκέφτομαι να το βγάλω μεμονωμένα από κάθε Stored Procedure, και να το βάλω μέσω του .net κάθε φορά που τα καλώ, όπως παραπάνω. Πάντα στον server.

Είναι καλύτερα δηλαδή να ξεκινώ τα Transactions μέσω του Framework ή να είναι ενσωματωμένα στα Procedures?


Υ.Γ. Προτιμώ να βρίσκεται μέσα στον βρόγχο και όχι απέξω, επειδή στη συγκεκριμένη υλοποίηση δεν υπάρχει σημαντικό πρόβλημα αν δεν περαστεί μία εγγραφή.

Το Serializable isolation level είναι αρκετά βαρύ καθώς είναι το πιο αυστηρό και δεν χρησιμοποιείται συχνά. Μάλιστα, όταν κάνεις insert και update με serializable, κλειδώνει ΟΛΟΚΛΗΡΟΣ ο πίνακας, πράγμα που συνεπάγεται τρομερό resource utilization. Τι ακριβώς θες να κάνεις; Γιατί το επέλεξες; Τι προβλήματα θεωρείς ότι σου λύνει; Απ' όσο έχω δει, αν απλά θες να περάσεις μερικές εγγραφές στη βάση είναι τρομερό overkill. Εφόσον λες ότι δεν υπάρχει σημανικό πρόβλημα αν δεν περαστεί κάποια εγγραφή, δεν χρεάζεσαι καν transaction! 

Μου φαίνεται θα πρέπει να μας περιγράψεις τι προσπαθείς να κάνεις σε conceptual επίπεδο. Μπορεί τελικά να μην χρειάζεσαι transactions...

---

Vir prudens non contra ventum mingit

Όχι, δεν είναι τόσο απλός ο κώδικας ώστε να περνιούνται μόνο απλές εγγραφές.

Κάθε φορά ελέγχονται περίπου 7 πίνακες οι οποίοι συνδέονται μεταξύ τους με διάφορα Foreign Keys, σχετικά με το αν υπάρχει η εγγραφή, και τα διαφορα θυγατρικά της δεδομένα. Σε περίπτωση που δεν υπάρχει προστίθεται, αλλιώς γίνεται απλά UPDATE.

Χωρίς Transaction, φοβάμαι ότι θα προκληθεί σημαντικό πρόβλημα. Ήδη μεταξύ 2 πινάκων με σχέση 1-1 έτυχε να ακυρωθεί η μία εντολή και μετά έπρεπε να σβήσω τη συγκεκριμένη εγγραφή χειροκίνητα γιατί σε αντίθετη περίπτωση δεν μπορούσε να βρει αυτή που υποτίθεται ότι υπήρχε.

Εξάλλου, για τους ελέγχους χρησιμοποιώ INSERT IF NOT EXISTS ()... για το οποίο νομίζω είναι απαραίτητο το SERIALIZABLE.

Γιαυτό προτιμώ να είναι και μεμονωμένα τα Transactions και όχι σε όλο τον βρόγχο, ώστε να δεσμεύουν τη βάση όσο λιγότερο γίνεται.

Χμμμ... Γι αυτό είπα ότι θα πρέπει να προδιορίσεις τί προσπαθείς να κάνεις σε conceptual level, αλλιώς κουβεντιάζουμε γενικά και αόριστα.

Όπως και να έχει, μερικές παρατηρήσεις με βάση αυτά που λες:

• Γιατί κάνεις εσύ τον έλεγχο για τον αν θα πρέπει να γίνει τελικά insert ή update; Οποιοδήποτε descent DAL (ξεκινώντας άκόμη και από τα απλά DataSets) μπορεί να το κάνει αυτό για σένα και μάλιστα με πολύ πιο κομψό τρόπο.
• Αν για το " INSERT IF NOT EXISTS ()..." εννοείς τη λογική "if not exists (select * from ... where ...) insert into ..." τότε ναι, απαιτείται transaction αλλά όχι απαραίτητα σε Serializable isolation level. Σκέψου μόνο το εξής: στο Serializable κλειδώνουν οι εγγραφές ενός πίνακα ακόμα και με το SELECT. Οπότε τελικά καταλήγεις σε πολύ un-scalable σύστημα.
• Το isolation level δεν το επιλέγουμε βάσει του πόσο περίπλοκα πράγματα κάνουμε κάθε φορά που μιλάμε με τη βάση. Το επιλέγουμε βάσει του τι conflicts περιμένουμε να έχουμε με τους άλλους χρήστες που κάνουν access τη βάση. Πολλές φορές ξεκινάμε από "light" isolation level και με monitoring της συμπεριφοράς του server, ανάλογα αν χρειάζεται, ανεβαίνουμε level.

 

---

Vir prudens non contra ventum mingit

KelMan:

• Γιατί κάνεις εσύ τον έλεγχο για τον αν θα πρέπει να γίνει τελικά insert ή update; Οποιοδήποτε descent DAL (ξεκινώντας άκόμη και από τα απλά DataSets) μπορεί να το κάνει αυτό για σένα και μάλιστα με πολύ πιο κομψό τρόπο.

Μπορείς να μου εξηγήσεις πώς γίνεται αυτό ή να μου δώσεις κάτι να διαβάσω πάνω σε αυτό για να το καταλάβω καλύτερα?

Η αρχική ιδέα ήταν να μη χρησιμοποιήσω ευκολίες του .net ώστε να μπορώ να μεταφέρω πιο εύκολα το service σε άλλη γλώσσα αν χρειαστεί.

KelMan:

• Αν για το " INSERT IF NOT EXISTS ()..." εννοείς τη λογική "if not exists (select * from ... where ...) insert into ..." τότε ναι, απαιτείται transaction αλλά όχι απαραίτητα σε Serializable isolation level. Σκέψου μόνο το εξής: στο Serializable κλειδώνουν οι εγγραφές ενός πίνακα ακόμα και με το SELECT. Οπότε τελικά καταλήγεις σε πολύ un-scalable σύστημα.
• Το isolation level δεν το επιλέγουμε βάσει του πόσο περίπλοκα πράγματα κάνουμε κάθε φορά που μιλάμε με τη βάση. Το επιλέγουμε βάσει του τι conflicts περιμένουμε να έχουμε με τους άλλους χρήστες που κάνουν access τη βάση. Πολλές φορές ξεκινάμε από "light" isolation level και με monitoring της συμπεριφοράς του server, ανάλογα αν χρειάζεται, ανεβαίνουμε level.

Ναι, εγώ θεώρησα ότι αυτό είναι το σωστότερο μια και σε περίπτωση που δύο χρήστες προσπαθήσουν να εισάγουν την ίδια εγγραφή θα υπάρξει σημαντικό πρόβλημα στη βάση και μετά θα πρέπει να επέμβω χειροκίνητα. Οπότε αποφάσισα ότι καλύτερα να μην είναι για κάποια δέκατα του δευτερολέπτου ανοιχτή η βάση παρά να συμβαίνουν τέτοια φαινόμενα.

Κάποια πρόταση?

Χμμμμ.... αρχικά θα πρέπει να επιλέξεις έναν μηχανισμό για το data access layer. DataSets; Linq to SQL; NHibernate; Από αυτό θα εξαρτηθεί και το πώς θα γίνεται το change tracking. Κατόπιν, ανάλογα με τον μηχανισμό θα πρέπει να γίνει το "πάντρεμα" με το web service.

---

Vir prudens non contra ventum mingit