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
933
votes
13 answers

Optimistic vs. Pessimistic locking

I understand the differences between optimistic and pessimistic locking. Now, could someone explain to me when I would use either one in general? And does the answer to this question change depending on whether or not I'm using a stored procedure…
Jason Baker
  • 192,085
  • 135
  • 376
  • 510
94
votes
25 answers

Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect)

I have a java project that runs on a webserver. I always hit this exception. I read some documentation and found that pessimistic locking (or optimistic, but I read that pessimistic is better) is the best way to prevent this exception. But I…
Georgian Citizen
  • 3,727
  • 6
  • 38
  • 46
28
votes
2 answers

How are locking mechanisms (Pessimistic/Optimistic) related to database transaction isolation levels?

I am writing a web application where two different users can update a list of things, to do list, for example. I have come to realize that, optimistic locking mechanism works best since I don't expect high contention. I was looking at transaction…
Abhishek Shukla Ravishankara
  • 613
  • 10
  • 17
22
votes
3 answers

Laravel lockforupdate (Pessimistic Locking)

i'm trying to figure out how to use/test the lockforupdate correctly, but i found is not function like what i expected this is just testing public function index() { return dd(\DB::transaction(function() { if…
user259752
  • 1,065
  • 2
  • 9
  • 24
17
votes
3 answers

SQL Server, the misleading XLOCK & optimizations

From some recent testing and reading I've done, it seems the "X" (exclusive) name part of XLOCK is misleading. It in fact doesn't lock any more than UPDLOCK. If it were exclusive, it would prevent external SELECTs, which it doesn't. I cannot see…
IamIC
  • 17,747
  • 20
  • 91
  • 154
12
votes
3 answers

Why my pessimistic Locking in JPA with Oracle is not working

I'm trying to implement some kind of semaphores for cron jobs that runs in different JBoss nodes. I'm trying to use the database (Oracle 11g) as a locking mechanism using one table to syncronize the cron jobs in the different nodes. The table is…
Ricardo Vila
  • 1,626
  • 1
  • 18
  • 34
10
votes
2 answers

How to code optimistic and pessimistic locking from java code

I know what optimistic and pessimistic locking is, but when you write a java code how do you do it? Suppose I am using Oracle with Java, do I have any methods in JDBC that will help me do that? How will I configure this thing? Any pointers will be…
user2434
  • 6,339
  • 18
  • 63
  • 87
8
votes
1 answer

How to get a PessimisticLockException with JPA

I am trying JPA's support of database locking in order to avoid concurrent requests in a record. In my scenario I need to use a pesssimistic lock. I got that using the following approach: EntityManager em; ... Map props = new…
Geison Santos
  • 187
  • 1
  • 3
  • 16
8
votes
3 answers

Is LockModeType.PESSIMISTIC_WRITE sufficient for an UPSERT in JPA?

I've read this article on JPA concurrency, but either I am too thick or it is not explicit enough. I am looking to do a database-controlled atomic update-if-found-else-insert operation (an UPSERT). It looks to my poor slow brain that I can--within a…
Laird Nelson
  • 15,321
  • 19
  • 73
  • 127
8
votes
3 answers

JPA Pessimistic Lock attempt never times out

I'm trying to use Pessimistic locking in JPA, over Hibernate 3 against a Postgres Database. I can't get the lock to time out - it just seems to hang forever. Here's an example: EntityManagerFactory factory; // (initialise the factory…
Alastair
  • 460
  • 2
  • 4
  • 13
8
votes
1 answer

select_for_update in development Django

The Django documentation states that: If you were relying on “automatic transactions” to provide locking between select_for_update() and a subsequent write operation — an extremely fragile design, but nonetheless possible — you must wrap the …
Trent
  • 2,328
  • 3
  • 33
  • 51
7
votes
1 answer

How to perform SELECT FOR UPDATE with SKIP LOCKED on MySQL with Django

I have a Django project which uses a MySQL v5.5 backend with InnoDB storage. To avoid race condition updates in DB, I'm using select_for_update to lock the rows. Now if this lock stays for a long time, any queries on the locked rows will timeout. I…
akhil_
  • 233
  • 1
  • 4
  • 10
7
votes
3 answers

Could there be a deadlock when using optimistic locking?

As is known, there are two locking strategy: Optimistic vs. Pessimistic locking 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…
Alex
  • 12,578
  • 15
  • 99
  • 195
7
votes
1 answer

Avoid dead lock by ordering explicitly

I want explicitly provide an order on how MySql InnoDB should acquire locks for rows. If this is possible there shouldn't be any dead locks just stalling. (If we follow the convention.) First, the databse should lock all rows found in table "models"…
mazatwork
  • 1,275
  • 1
  • 13
  • 20
6
votes
3 answers

How to implement pessimistic locking in a php/mysql web application?

How to implement pessimistic locking in a php/mysql web application? web-user opens a page to edit one dataset (row) web-user clicks on the button "lock", so other users are able to read but not to write this dataset web-user makes some…
user1027167
  • 4,320
  • 6
  • 33
  • 40
1
2 3
…
11 12