[pyar] [PyAr] Bloqueo Pesimista en MySQL o SQLite

[Pedro Pezzarini]

Optimistic Locking is a strategy where you read a record, take note of a
version number (other methods to do this involve dates, timestamps or
checksums/hashes) and check that the version hasn't changed before you
write the record back. When you write the record back you filter the update
on the version to make sure it's atomic. (i.e. hasn't been updated between
when you check the version and write the record to the disk) and update the
version in one hit.

If the record is dirty (i.e. different version to yours) you abort the
transaction and the user can re-start it.

This strategy is most applicable to high-volume systems and three-tier
architectures where you do not necessarily maintain a connection to the
database for your session. In this situation the client cannot actually
maintain database locks as the connections are taken from a pool and you
may not be using the same connection from one access to the next.

Pessimistic Locking is when you lock the record for your exclusive use
until you have finished with it. It has much better integrity than
optimistic locking but requires you to be careful with your application
design to avoid Deadlocks. To use pessimistic locking you need either a
direct connection to the database (as would typically be the case in a two
tier client server application) or an externally available transaction ID
that can be used independently of the connection.

In the latter case you open the transaction with the TxID and then
reconnect using that ID. The DBMS maintains the locks and allows you to
pick the session back up through the TxID. This is how distributed
transactions using two-phase commit protocols (such as XA or COM+
Transactions) work


On Sat, Apr 7, 2018, 11:43 PM Gerardo Benitez <gbenitez en kiusys.com> wrote:

> Que es un bloqueo pesimista?
>
> El Sáb., 7 de Abril de 2018 9:08 PM, Javier Haramina <jharamina en gmail.com>
> escribió:
>
>> Hola, Necesitaría hacer un bloqueo Pesimista de registros, incluso para
>> lectura.
>>
>> La forma que encontré es haciendo una modificación por el mismo dato
>> sobre el registro,  y recién allí me lo bloquea, hasta cerrar la
>> transacción.
>>
>> Alguien conoce alguna otra forma de hacerlo, sin tener que modificar
>> ningún dato.
>>
>> Les agradecería me ayudaran con esto.
>>
>>
>>
>> Saludos Javier.
>>
>>
>> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient> Libre
>> de virus. www.avast.com
>> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient>
>> <#m_5695259123754154406_m_3607511803428291430_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>> _______________________________________________
>> Lista de Correo de PyAr - Python Argentina - pyar en python.org.ar
>> Sitio web: http://www.python.org.ar/
>>
>> Para administrar la lista (o desuscribirse) entrar a
>> http://listas.python.org.ar/listinfo/pyar
>>
>> La lista de PyAr esta Hosteada en USLA - Usuarios de Software Libre de
>> Argentina - http://www.usla.org.ar
>
> _______________________________________________
> Lista de Correo de PyAr - Python Argentina - pyar en python.org.ar
> Sitio web: http://www.python.org.ar/
>
> Para administrar la lista (o desuscribirse) entrar a
> http://listas.python.org.ar/listinfo/pyar
>
> La lista de PyAr esta Hosteada en USLA - Usuarios de Software Libre de
> Argentina - http://www.usla.org.ar
------------ próxima parte ------------
Se ha borrado un adjunto en formato HTML...
URL: <http://listas.python.org.ar/pipermail/pyar/attachments/20180408/dc0b589d/attachment-0001.html>