Django village talk 42

download

of 29

description

- Gestione delle transazioni: commit/rollback a livello di db, autocommit, atomic, commit on success, TransactionMiddleware, etc. - Lock dei record in fase di modifica: lock acquisiti automaticamente da db, lock espliciti, lock ottimistici gestiti a livello applicativo - Gestione delle connessioni: persistent connections in django o connection pooling con tool esterni (pgBouncer)

transcript

<ul><li> 42! la risposta alle domande che non avete mai fatto </li> <li> e non sapevate di dover fare Transaction (... be atomic, please) Locking (... be optimistic, please) Connection (... be persistent, please) </li> <li> SQL-transactions "An SQL-transaction (sometimes simply called a "transaction") is a sequence of executions of SQL-statements that is atomic with respect to recovery" a single atomic unit of work in a database </li> <li> Nuclear power Usato come decorator o come context manager: transaction.atomic tuo amico ma occhio ai percorsi non convenzionali </li> <li> transactions jazz Nucleariziamo il progetto: ATOMIC_REQUEST Dividiamo latomo: savepoints Impiliamo transazioni: nested atomic (TY 1.6!) e leggiamo qualcosa (prima di altri danni): Django Transactions Transaction Management with Django 1.6 django.db.transaction source </li> <li> ...to 1.6 togliere il TransactionMiddleware usare ATOMIC_REQUESTS = True ma non esattamente la stessa cosa </li> <li> Martin Fowler - P of EAA Optimistic Offline Lock (416)... "second save loses" Pessimistic Offline Lock (426)... "second read loses" [ Coarse Grained Lock (438), Implicit Lock (449) ] Pessimistic, Optimistic and ... Cleese </li> <li> "first save loses and user isn't notified" </li> <li> It is a design issue! </li> <li> Apps per Django django-locking Prevents users from doing concurrent editing django-concurrency Optimistic lock implementation for Django. django-optimistic-lock Offline optimistic locking for Django. </li> <li> SAVE Snippet rows = cls.objects. filter(pk=self.pk, _change=self._change). update(_change=self._change + 1) if not rows: raise ConcurrentModificationError() self._change += 1 super(ConcurrentModel, self).save(*args, **kwargs) </li> <li> Setup drop table doctors; create table doctors(name char(20), is_available boolean); insert into doctors values ('alice', True); insert into doctors values ('tom', True); select * from doctors; Session Alice -- begin; Session Tom -- begin; Session Alice update doctors set is_available = False where name = 'alice' and (select count(1) from doctors where is_available=True) &gt;= 2; commit; Session Tom update doctors set is_available = False where name = 'tom' and (select count(1) from doctors where is_available=True) &gt;= 2; commit; Opps! please try and solve ;-) </li> <li> Setup create table mytab(class integer, value integer); insert into mytab values (1, 10); insert into mytab values (1, 20); insert into mytab values (2, 100); insert into mytab values (2, 200); Session A begin; Session B begin; Session A set transaction isolation level serializable; insert into mytab (class, value) select 1, sum(value) from mytab where class = 2; Session B set transaction isolation level serializable; insert into mytab (class, value) select 2, sum(value) from mytab where class = 1; Session A commit; Session B (???) commit; you can try this at home :-) Optimistic Lock... at DB level </li> <li> Connections default: connect/disconnect for each request problem: 70 ms 10 ms query reason: ssl handshake, backend process, etc. solutions: persistent connection connection pooling </li> <li> Persistent connections 1. start/end request: close_old_connections 2. foreach con: close_if_unusable_or_obsolete a. close_at b. ping c. errors 3. CONN_MAX_AGE: 0, positive, None Persistency - commit on github </li> <li> pgBouncer </li> <li> Unless you know how it works, you will write programs that corrupt data. It is that simple Concluding remark of the chapter on Locking and Concurrency in Tom Kytes best-selling book, Expert One-On-One Oracle We suspect that numerous isolation errors occur each day at many large sites because of this, leading to corrupt data sometimes noted in data warehouse applications. Making Snapshot Isolation Serializable Alan Fekete: University of Sydney; N.S.W; Australia, 2006; fekete@it.usyd.edu.au </li> </ul>