Stack Overflow
Stack Overflow

Questions tagged [pessimistic-locking]

Pessimistic locking is a strategy that will lock a database record, that is to be updated, for exclusive use until the update is complete.

Pessimistic locking is a strategy that will lock a database record, that is to be updated, for exclusive use until the update is complete. The database record is locked immediately when the lock is requested and it is then guaranteed that the database record will be updated. Unlike optimistic locking, great care must be taken in the application design to avoid deadlocks.

178 questions
0
votes
2 answers

Pessimistic locking relationship with database transaction

I know pessimistic locking with lock a database record and release it when "transaction" ends which obtained the locks. But is this means pessimistic locking is within a physical transaction like BEGIN TRANSACTION //pessimistic locking COMMIT…
user1169587
  • 1,104
  • 2
  • 17
  • 34
0
votes
1 answer

How to prevent Deadlock exception in SELECT FOR UPDATE after the update?

I have an account table with 1 row. I have 2 threads which do the next: 1st thread: begin transaction; select * from account where balance=0 for update; UPDATE account SET balance = 10 WHERE balance=0; // waiting here for several seconds commit…
Oleksandr
  • 3,574
  • 8
  • 41
  • 78
0
votes
1 answer

Why don't I see "FOR UPDATE" in the SQL generated by a locked ActiveRecord query?

I'm reading "The Rails 5 Way", and on page 191 I see the following: Pessimistic locking takes place at the database level. The SELECT statement generated by Active Record will have a FOR UPDATE (or similar) clause added to it... The Rails docs…
Richie Thomas
  • 3,073
  • 4
  • 32
  • 55
0
votes
1 answer

How to implement an optimistic (or pessimistic) locking when using two databases that need to be in sync?

I'm working on a solution in which we have two databases that are used with the following purposes: An Elasticsearch used for search purposes A Postgres database that acts as a source of truth for the data Our application allows users to retrieve…
David Jiménez Martínez
  • 3,053
  • 5
  • 23
  • 43
0
votes
1 answer

Multiple consumers trying to update same row of Mysql(5.7 version) table. How to fix concurrent update issue

Multiple consumers are trying to update 'processedRecords' and 'erredRecords' fields of JobTracker. But I am facing issue where random values updated in these columns. Mysql is on default isolation level(MySQL version 5.7) I thought of locking row…
AUK
  • 21
  • 3
0
votes
1 answer

How to implement spring boot pessimistic lock and which approach is better optimistic lock or pessimistic lock?

Setup version on entity class @Column(name = "VERSION") @Version private int version; } entityManager.find(getActiveUsers.class, 1, LockModeType.OPTIMISTIC);
Rupesh Kumar
  • 15
  • 3
0
votes
1 answer

Transaction isolation ANSI REPEATABLE_READ in DB2 not locking rows as expected

I have a situation with DB2 isolation levels I cannot explain. I have a transaction with isolation level REPEATABLE_READ, which in DB2 corresponds to READ_STABILITY. I have basic understanding on DB2 S-, U-, and X-locks. When I execute the…
Daniel
  • 744
  • 8
  • 24
0
votes
0 answers

PessimisticLockException while using Spring @Transactional

I have a requirement in which I need to modify a field in 6 tables, and need to do that in single transaction. So I used spring @Transactional (with default settings) for it. Now, while fetching and updating data within transactional method, if any…
user3106356
  • 31
  • 1
  • 3
0
votes
0 answers

Rails - How to retry in pessimistic locking?

Rails Guide and lots of other tutorials show me how to rescue ActiveRecord::StaleObjectError exception and do retry in optimistic locking. And I just curious can I do the same thing in pessimistic locking scenario. Any reply is appreciate! :D
gurugurusa
  • 45
  • 1
  • 6
0
votes
1 answer

Pessimistic vs optimistic concurrency control implementation

I've understood how both these concurrency controls work in plain English. However I was more interested how pessimistic control must be done in code. Here is what I feel, let's assume two users are trying to update a wiki document Pessimistic…
ffff
  • 2,853
  • 1
  • 25
  • 44
0
votes
1 answer

Testing pessimistic locking with Rails

To get a better understanding of pessimist locking (with InnoDB), I tried to run this code in my Rails application: Thread.new do Account.transaction do account = Account.lock(true).first account.balance += 250 account.save! …
Zakari
  • 1
  • 1
0
votes
1 answer

Ensuring release of pessimistic lock

I'm considering implementing the pessimistic locking pattern in a WinForms insurance quoting application using SQL Server. Before a user starts working on a quote, a record will be added to the lock table; when they're done, the record will be…
drew.cuthbert
  • 1,005
  • 1
  • 9
  • 21
0
votes
1 answer

rspec change expectation fails when using active record pessimistic locking

I have a Rails 4.2.0 method that uses pessimistic locking to change a counter class Foo < < ActiveRecord::Base def bump! transaction do lock! parent.lock! lock.counter += 1 parent.counter += 1 save! …
Chris Beck
  • 1,899
  • 2
  • 19
  • 26
0
votes
1 answer

OptimisticLockException with concurrent JPA update under Spring Boot

Here is the sample project where the exception is reproduced. This sample illustrates the issue when many concurrent transactions are modifiying Account balance. Account can have many Card entities bound. Transactions are related to Order and last…
E.G.
  • 181
  • 2
  • 9
0
votes
1 answer

Why do gaps appear in a List backed by @ManyToMany with @OrderColumn

We have queues of Ticket objects persisted to the database. Our entity containing the queues looks like this: @Entity public class StoreQueueCollection { @Id private int storeId; @ManyToMany(fetch=FetchType.LAZY) @OrderColumn …
bsa
  • 2,671
  • 21
  • 31
1 2 3
…
11
12