SQL Server (και άλλες databases)

Απ: Custom, yet safe identity number generation

Markos — Mon, 24 Jun 2013 03:45:39 GMT

Dimitris Papadimitriou:
...Κατά τα άλλα οι πίνακές μου χρησιμοποιούν παραδοσιακά identity columns....

Αν δε ρωτήσω θα σκάσω... Δηλαδή ο πίνακας έχει ΚΑΙ identity column ΚΑΙ custom generated id column; Αν ναι, τι είδους παραπάνω έλεγχο μπορεί να έχω μ 'αυτή την πρακτική; (Αντιλαμβάνομαι ότι η ερώτηση δεν έχει άμεση σχέση με το thread και ζητώ συγνώμη γι' αυτό...). Επίσης, ένα μειονέκτημα σε σχέση με την built in υλοποίηση των sequences είναι ότι δεν έχω caching. Συνεπώς, τι κερδίζω με όλ' αυτά;

Απ: Custom, yet safe identity number generation

Antonios Chatzipavlis — Mon, 24 Jun 2013 03:11:27 GMT

Κάτι τέτοιο απλά θα πρέπει να είσαι προετοιμασμένος για αυτούς που θα προσπαθούν να πάρουν αριθμό όταν κάποιος άλλος έχει ήδη κάνει αυτό. Ιδανική λύση όπως έχω ήδη πει δεν υπάρχει

Απ: Custom, yet safe identity number generation

Dimitris Papadimitriou — Mon, 24 Jun 2013 02:58:31 GMT

Is this what you mean?

UPDATE [Configuration] WITH (SERIALIZABLE) SET @NewValue = [LastNumber] = [LastNumber] + 1

Απ: Custom, yet safe identity number generation

Antonios Chatzipavlis — Mon, 24 Jun 2013 02:42:52 GMT

Αρχικά να τονίζω ότι και με τα sequence αλλά και με τα πεδία identity όταν γίνεται rollback ένα transaction αυτά χάνουν τον αριθμό που ήταν να δώσουν. Οι διαφορά μεταξύ τους είναι ότι με τα sequence έχεις μεγαλύτερο έλεγχο αλλά μπορείς να γνωρίζεις τον αριθμό αυτό πριν μπει η εγγραφή στο πίνακα. Αν το ζητούμενο είναι να έχω συνεχώμενο αριθμό χωρίς να χάνω ούτε έναν αυτές οι λύσεις δεν παίζουν. Αντίθετα θα πρέπει να το κάνεις μόνος σου.

Για το identity που αναφέρει ο Παναγιώτης με το τρικ του Itzik θα πρέπει να τον ρωτήσεις τώρα για αυτό καθώς και αυτό δεν φέρνει το εποθυμητό αποτέλεσμα καθώς για να υλοποιηθεί το identity δημιουργούνται κάποιοι buffers και από εκει δίνονται τα νούμερα. Υπάρχουν όμως περιπτώσεις που ενώ περιμένεις να παρεις τον επόμενο που είναι πχ το 10 να πάρεις το 1287(τυχαίο). Επίσης να τονισθεί ότι με το identity ο SQL Server εγγυάται είναι ότι θα είναι μοναδικός σειρακός αριθμός αλλά μη συνεχές.

Αυτό που έχει φτιάξει ο Δημήτρης και έχουμε κάνει κατ ιδία την συζήτηση αυτή δεν είναι λάθος του έχω τονίσει την ιδιαιτερότητα με τα transactions και αν παίξει με transaction isolation level serializiable ή ένα exclusive lock hint δεν θα έχει πρόβλημα

Αυτό είναι τα 2cents μου

Απ: Custom, yet safe identity number generation

Παναγιώτης Καναβός — Mon, 24 Jun 2013 02:28:26 GMT

Το πρόβλημα είναι ότι ΔΕΝ είναι ασφαλές το stored procedure έτσι όπως το έχεις γράψει. Υπάρχει πάντα η πιθανότητα να δωθεί ο ίδιος αριθμός σε δύο clients, εκτός και αν διαχειρίζεσαι τα transactions μέσα στο ίδιο το stored procedure. Επιπλέον, υπάρχει πάντα η πιθανότητα δύο clients να διαβάσουν το ίδιο ακριβώς νούμερο και να επιστρέψουν την ίδια ακριβώς τιμή, αν χρησιμοποιείς snapshot isolation.

Το θέμα των sequences δεν είναι κάτι καινούριο, έχει συζητηθεί εξαντλητικά εδώ και τουλάχιστον μία δεκαετία. Κάποιες τεχνικές τις είχα πρωτοσυναντήσει στα βιβλία του Joe Celko με ημερομηνία έκδοσης το 1998. Αντί να προσπαθείς να φτιάξεις κάτι από το μηδέν, ψάξε να δεις τί υπάρχει ήδη και που έχει καταλήξει η συζήτηση.

Αλήθεια, ΠΟΥ θέλεις να χρησιμοποιήσεις αυτό το νούμερο? Αν θέλεις να το χρησιμοποιήσεις σε κώδικα ίσως θα μπορούσες να παρακάμψεις εντελώς το sequence και να χρησιμοποιήσεις μία τεχνική HiLo όπου ο κάθε client θα έχει το δικό του high word ενός long και το low word θα έρχεται π.χ. από τον αριθμό των millisecond ή των ticks. Είναι η τεχνική που χρησιμοποιούν τα ORMs για να παρακάμψουν τη δημιουργία των unique keys στην database και το επιπλέον round-trip.

Απ: Custom, yet safe identity number generation

Markos — Mon, 24 Jun 2013 01:54:09 GMT

Dimitris Papadimitriou:
...Πρόκειται για ένα ιδιαίτερο αριθμό στο σύστημά μου και όχι για ένα καθολικό σύστημα ID generation που αντικαθιστά τα πάντα στην βάση μου...

Αυτός ο "ιδιαίτερος αριθμός" προσδιορίζει κάτι; Εμπεριέχει μέσα του πληροφορία που χρειάζεσαι; Αν ναι, ποια τα χαρακτηριστικά του; Κάτι μου λέει ότι για να είναι "ιδιαίτερος" το generation του έχει κάποιες προδιαγραφές και δεν πρόκειται για ένα απλό increment κατά μία μονάδα κάθε φορά. Σ' αυτό που γράφει ο Παναγιώτης έχει δίκιο (ακόμα κι αν κάνεις rollback η τιμή δεν θα επανέλθει σε κάποια προηγούμενη). Συνεπώς τι το ιδιαίτερο πρέπει να έχει το custom generated key που θέλεις;

Απ: Custom, yet safe identity number generation

Dimitris Papadimitriou — Mon, 24 Jun 2013 01:43:01 GMT

Παναγιώτη δέχομαι την παραδοχή που λες: "Για να δουλέψει σωστά ένα sequence βασισμένο σε πίνακα θα πρέπει να εκτελείται ανεξάρτητα από το transaction που το περιβάλλει. Αυτό σημαίνει ότι ακόμα και αν κάνεις rollback, η τιμή του δεν θα επανέλθει σε κάποια προηγούμενη.", όπως επίσης και δέχομαι ότι αυτή η τεχνική μπορεί να οδηγήσει σε χαμένα νούμερα.

Το Insert/Rollback σε εικονικό πίνακα με identity column μου το προτείνετε και οι δυο, αλλά δε με ενθουσιάζει περισσότερο από αυτό που ήδη έγραψα.

Μάρκο θέλω μια δική μου υλοποίηση απλά για να έχω περισσότερο έλεγχο πάνω στη διαδικασία. Πρόκειται για ένα ιδιαίτερο αριθμό στο σύστημά μου και όχι για ένα καθολικό σύστημα ID generation που αντικαθιστά τα πάντα στην βάση μου. Κατά τα άλλα οι πίνακές μου χρησιμοποιούν παραδοσιακά identity columns.

Τα sequences που τα επισήμανε και ο Μάστερ Αντώνης Χατζηπαυλής αλλά εγκαταλήψαμε την ιδέα καθώς δεν υποστηρίζονται στο sql azure.

Οπότε ας κάνουμε redefine τις απαιτήσεις:

- Custom id generation

- Safety so that two clients running that stored procedure cannot get the same number

- Not a problem if a few numbers a lost

- Runs on sql azure

Νομίζω ότι με αυτές τις απαιτήσεις δεν είναι κακή λύση, σωστά;

Απ: Custom, yet safe identity number generation

Παναγιώτης Καναβός — Mon, 24 Jun 2013 01:28:41 GMT

Αυτό υπάρχει στον SQL Server 2012 με τη μορφή των sequences. Σε παλαιότερες εκδόσεις υπάρχουν διάφορες τεχνικές για να δημιουργήσεις sequences και σίγουρα δεν υπάρχει λόγος να περιοριστείς μόνο σε ένα sequence.

Έτσι όπως είναι το procedure θα έχει θέμα με τα transactions.

- Αν δύο διαφορετικά transactions προσπαθήσουν να πάρουν νούμερο, το ένα από αυτά θα μπλοκάρει μέχρι να τελειώσει το δεύτερο, καθώς τα update locks που θα πάρει το ένα transaction θα διατηρηθούν μέχρι το τέλος.

- Επιπλέον, αν το transaction που κάνει rollback, θα επανέλθει το sequence στην προηγούμενη τιμή του με αποτέλεσμα ο επόμενος που θα ζητήσει νούμερο, να πάρει το ίδιο νούμερο ξανά.

Για να δουλέψει σωστά ένα sequence βασισμένο σε πίνακα θα πρέπει να εκτελείται ανεξάρτητα από το transaction που το περιβάλλει. Αυτό σημαίνει ότι ακόμα και αν κάνεις rollback, η τιμή του δεν θα επανέλθει σε κάποια προηγούμενη.

Αξίζει να ψάξεις άρθρα των Itzik Ben Gan, Aaron Bertrand για sequences καθώς προτείνουν διάφορες τεχνικές, με διαφορετικά πλεονεκτήματα και μειονεκτήματα η κάθε μία. Τα πράγματα βέβαια έχουν γίνει λίγο "περίεργα" καθώς τα πρώτα αποτελέσματα που θα βρεις πλέον αφορούν τα Sequences του SQL Server 2012 αντί για τα custom sequences των προηγούμενων εκδόσεων.

Τουλάχιστον μία τεχνική πάντως περιγράφεται στο Inside SQL Server 2008: T-SQL Programming και ευτυχώς μπορείς να την βρεις και στο Google Books. Δημιουργεί ένα πίνακα με Identity column και μετά σε ένα stored procedure κάνει save ένα savepoint, insert στον πίνακα και rollback το savepoint. ΤΟ αποτέλεσμα είναι ότι έχει πάρει ένα νέο νούμερο μέσω του SCOPE_IDENTITY() χωρίς να κάνει κάποια εγγραφή:

CREATE PROC dbo.GetSequence

@val as INT OUTPUT

BEGIN TRAN

SAVE TRAN S1;

INSERT INTO dbo.Sequence DEFAULT VALUES;

SET @val=SCOPE_IDENTITY();

ROLLBACK TRAN S1;

COMMIT TRAN;

Απ: Custom, yet safe identity number generation

Markos — Mon, 24 Jun 2013 01:20:34 GMT

Γεια σου Δημήτρη,

Νομίζω ότι το θέμα δεν είναι τόσο απλό και δεν ξέρω αν μπορώ να απαντήσω γρήγορα και σωστά στα πλαίσια ενός post. Φαντάζομαι ότι έχεις διαβάσει το άρθρο "Custom Auto-Generated Sequences with SQL Server". Εν ολίγοις, ο συγγραφέας καταλήγει ότι ο καλύτερος και ασφαλέστερος τρόπος για να πετύχεις κάτι τέτοιο είναι χρησιμοποιήσεις ένα "κρυφό" identity column και μια συνάρτηση που "παράγει" το custom key που θέλεις, χρησιμοποιώντας, όμως, την "κρυφή" τιμή του identity column. Διαβάζοντάς το, η μόνη παρατήρηση που έχω να κάνω είναι πως, αν πρόκειται για θέμα presentation, γιατί η συνάρτηση αυτή να είναι γραμμένη σε T-SQL και όχι σε κάποια .NET γλώσσα και η μετατροπή να γίνεται στον client...

Λαμβάνοντας υπ' όψη όλα τα παραπάνω, η ερώτηση που ακολουθεί είναι αναπόφευκτη:

Γιατί χρειάζεσαι custom key generation;

Custom Auto-Generated Sequences with SQL Server

Custom, yet safe identity number generation

Dimitris Papadimitriou — Sun, 23 Jun 2013 21:41:53 GMT

Πως σας φαίνεται το παρακάτω; Προϋποθέτει ότι υπάρχει ένας πίνακας που λέγεται Configuration, έχει μια στήλη LastNumber int NOT NULL με αρχική τιμή 0 και μόνο μια εγγραφή πάντα.

CREATE PROCEDURE GetUniqueID

BEGIN

SET NOCOUNT ON;

DECLARE @NewValue as int

UPDATE [Configuration] SET @NewValue = [LastNumber] = [LastNumber] + 1

RETURN @NewValue

END