writing in same table without locking it

Baron · ‎2024 Nov 16

I am using sql anywhere 17, and get several connections concurrently trying to update the same row in a table.

Each connection per se does lot of calculations and at the end updates some specific rows in a table.

The problem is that sometimes different connections cause an update on the same row/s, so that they causes an deadlock, and no one of them can accomplish the job and commit.

Is there a simple way to solve such a problem withouth implementing isolation levels? since I dont have any experience with isolation levels.

VolkerBarth · ‎2024 Nov 20

As to your sample:

It shows the typical deadlock problem of trying to acquire the same resources in different (or here random) order (as there is no defined order in the cursor), and it also misses access on the underlying table before the update itself.

Here's a slightly enhanced procedure that joins the sa_split_list-results with the MyInventary table, returns the rows in a declared order (so in the same order for each transaction) and uses FOR UPDATE to make the server place an "intent lock" on each row. In a separate connection, you can use "call sa_locks()" to notice how the rows are locked before the updates are tried, and there shopuld be no dead lock. (Of course both sample miss error handling in case the quantity would get negative.)

It basically forces a transaction to get its own resources in the same order, and no transaction can "overtake" another one, so basically they are serialized if they try to access the same products.

create or replace procedure ReceiveSinglePiecesShipping(ProductList long varchar)
begin
for ShippedProducts as mycursor cursor for
   select MI.ProductName as MyProductName, Quantity
   from sa_split_list(ProductList) SPL inner join MyInventary MI on trim(SPL.row_value) = MI.ProductName
   order by MI.ProductName
   for update
do
   waitfor delay ('00:00:02');
   update MyInventary set Quantity = Quantity - 1 where ProductName = MyProductName;
   end for;
end;

VolkerBarth · ‎2024 Nov 18

Well, you simply cannot write a row without locking it, writers do have to block other writers on the same row.

What do you mean by "deadlock" here? If transaction A updates row X and transaction B tries to update the same row (assuming A has already locked it), B will either have to wait to do its update (when option Blocking is "on" and A rollbacks/commits before B's blocking timeout has been exceeded) or it will fail. That's a failed transaction but no deadlock.
A deadlock arises when there are cyclic blocks (*), say, when A tries to update row Y which has already been updated by Y in Y's running transaction, so neither A nor B can succeed. Then the database server will rollback one to A or B to resolve the deadlock.
The classical text book approach to prevent such deadlocks is either to acquire resources in the same order, say by trying to update rows in the same PK order, or to make each transaction work on their separate area of resources.

(*) - There are other causes for deadlocks, e.g. running out of worker threads.

writing in same table without locking it

Know the answer?

Need more details?

Accepted Solutions (0)

Answers (2)

Answers (2)