[MariaDB] Dosegljivost podatkov med DELETE + INSERT operacijo.

deadlock se ne bi sam rešil brez pomoči serverja tako, da eno transakcijo ubije. blocking se reši po nekem času sam.
deadlocke se reši tako, da se najde oba problematična queryja in se ju ponovno napiše optimizirano oz vsaj enega. če ni možno pa try catch in v catch če je dedlock še 1x poženemo kodo oz damo flag, da se naslednjič sprocesira.

chatgpt je dober za učenje


explain why here deadlock happens: CREATE TABLE DeadlockTest (
ID INT PRIMARY KEY,
Value INT
);
INSERT INTO DeadlockTest (ID, Value) VALUES (1, 100);
INSERT INTO DeadlockTest (ID, Value) VALUES (2, 200);

-- SSMS1 zavihek 1
BEGIN TRANSACTION;
UPDATE DeadlockTest SET Value = Value + 10 WHERE ID = 1;
WAITFOR DELAY '00:00:05'; -- Wait for 5 seconds before executing the next query
UPDATE DeadlockTest SET Value = Value + 10 WHERE ID = 2;

-- SSMS2 zavihek 2
BEGIN TRANSACTION;
UPDATE DeadlockTest SET Value = Value - 10 WHERE ID = 2;
WAITFOR DELAY '00:00:05'; -- Wait for 5 seconds before executing the next query
UPDATE DeadlockTest SET Value = Value - 10 WHERE ID = 1;
ChatGPT
The deadlock in your scenario occurs due to a classic case of two transactions acquiring locks on resources in a different order, leading to a deadlock situation.

Let's break down the sequence of events:

SSMS1 (Transaction 1):

Transaction 1 starts by updating the row with ID 1 in the DeadlockTest table, increasing its Value by 10.
This transaction acquires an exclusive lock on the row with ID 1.
SSMS2 (Transaction 2):

Transaction 2 starts by updating the row with ID 2 in the DeadlockTest table, decreasing its Value by 10.
This transaction acquires an exclusive lock on the row with ID 2.
SSMS1 (Transaction 1):

Transaction 1 now tries to update the row with ID 2, increasing its Value by 10.
However, it cannot acquire the exclusive lock on the row with ID 2 because Transaction 2 already holds it.
SSMS2 (Transaction 2):

Transaction 2 tries to update the row with ID 1, decreasing its Value by 10.
However, it cannot acquire the exclusive lock on the row with ID 1 because Transaction 1 already holds it.
Both transactions are now waiting for resources that the other transaction holds, resulting in a deadlock situation. SQL Server automatically detects the deadlock and resolves it by choosing one of the transactions as the deadlock victim and rolling it back.

To avoid deadlocks, it's crucial to ensure that transactions acquire locks on resources in the same order. In this case, you might consider updating rows in a consistent order based on their IDs to prevent deadlocks from occurring. Additionally, you could explore using appropriate isolation levels and transaction handling techniques to minimize the occurrence of deadlocks in your application.

User
other scenarios than updating in different order for deadlock?
ChatGPT
Certainly! Deadlocks can occur due to various scenarios beyond just updating rows in a different order. Here are a few common scenarios:

Insertion Deadlocks: When multiple transactions try to insert rows into the same table simultaneously, they might contend for the same resources and end up deadlocking if they acquire locks on the same resources in a different order.

Index Deadlocks: Deadlocks can occur when transactions try to update or insert rows in a table that requires index modifications. For example, if two transactions need to update different rows but both need to modify the same index, a deadlock can occur if they acquire locks on resources in different orders.

Locking on Non-Clustered Indexes: If queries access the same table but use different non-clustered indexes, they might contend for locks on different index pages, leading to deadlocks.

Cursor Deadlocks: Cursors hold locks on the rows they fetch. If two cursors are opened by different transactions and they try to update rows that the other cursor has locked, a deadlock can occur.

Lock Escalation Deadlocks: In some cases, SQL Server might escalate row-level locks to table-level locks to optimize performance. If multiple transactions are contending for table-level locks and deadlock conditions are met, deadlocks can occur.

Parallelism Deadlocks: Deadlocks can occur in scenarios where multiple parallel queries are executing simultaneously and contend for the same resources.

To prevent deadlocks, it's essential to design transactions and queries carefully, consider the isolation level of transactions, minimize the duration of transactions, and handle exceptions and retries gracefully. Additionally, understanding the application's data access patterns and concurrency requirements can help identify potential deadlock scenarios and mitigate them effectively.

User
give example for 1
ChatGPT
Certainly! Let's consider a scenario where insertion deadlocks can occur:

Suppose we have two transactions executing simultaneously, each trying to insert data into the same table. If both transactions attempt to acquire locks on resources in a different order, a deadlock can occur. Here's an example:

sql
Copy code
-- Transaction 1
BEGIN TRANSACTION;
INSERT INTO MyTable (ID, Name) VALUES (1, 'Transaction 1');
WAITFOR DELAY '00:00:05'; -- Simulate a delay
INSERT INTO MyTable (ID, Name) VALUES (2, 'Transaction 1');
COMMIT TRANSACTION;

-- Transaction 2
BEGIN TRANSACTION;
INSERT INTO MyTable (ID, Name) VALUES (2, 'Transaction 2');
WAITFOR DELAY '00:00:05'; -- Simulate a delay
INSERT INTO MyTable (ID, Name) VALUES (1, 'Transaction 2');
COMMIT TRANSACTION;
In this scenario:

Transaction 1 starts by inserting a row with ID 1 into MyTable.
Transaction 2 starts by inserting a row with ID 2 into MyTable.
Transaction 1 then attempts to insert a row with ID 2, but it's blocked because Transaction 2 already holds a lock on that row.
Similarly, Transaction 2 attempts to insert a row with ID 1, but it's blocked because Transaction 1 already holds a lock on that row.
Both transactions are waiting for resources that the other transaction holds, resulting in a deadlock. SQL Server detects the deadlock and resolves it by choosing one of the transactions as the deadlock victim and rolling it back.

To avoid insertion deadlocks, it's crucial to ensure that transactions acquire locks on resources in the same order. In this case, you might consider ordering the insertion of rows based on their IDs or using appropriate transaction isolation levels and concurrency control mechanisms to prevent deadlocks from occurring.

User
REWRITE LAST 2 QUERIES TO USE MY TABLE
ChatGPT
Certainly! Here are the last two queries rewritten to use a table named MyTable:

sql
Copy code
-- Transaction 1
BEGIN TRANSACTION;
INSERT INTO MyTable (ID, Name) VALUES (1, 'Transaction 1');
WAITFOR DELAY '00:00:05'; -- Simulate a delay
INSERT INTO MyTable (ID, Name) VALUES (2, 'Transaction 1');
COMMIT TRANSACTION;

-- Transaction 2
BEGIN TRANSACTION;
INSERT INTO MyTable (ID, Name) VALUES (2, 'Transaction 2');
WAITFOR DELAY '00:00:05'; -- Simulate a delay
INSERT INTO MyTable (ID, Name) VALUES (1, 'Transaction 2');
COMMIT TRANSACTION;
In this script:

Transaction 1 inserts rows with IDs 1 and 2 into MyTable.
Transaction 2 inserts rows with IDs 2 and 1 into MyTable.
Both transactions simulate a delay to illustrate a scenario where deadlock might occur due to contention for the same resources.
The scenario remains the same as described earlier, where if both transactions attempt to acquire locks on resources in a different order, a deadlock can occur.

User
NO, USE MY TABLE DeadlockTest
ChatGPT
Apologies for the oversight. Here are the last two queries rewritten to use the DeadlockTest table:

sql
Copy code
-- Transaction 1
BEGIN TRANSACTION;
INSERT INTO DeadlockTest (ID, Value) VALUES (1, 'Transaction 1');
WAITFOR DELAY '00:00:05'; -- Simulate a delay
INSERT INTO DeadlockTest (ID, Value) VALUES (2, 'Transaction 1');
COMMIT TRANSACTION;

-- Transaction 2
BEGIN TRANSACTION;
INSERT INTO DeadlockTest (ID, Value) VALUES (2, 'Transaction 2');
WAITFOR DELAY '00:00:05'; -- Simulate a delay
INSERT INTO DeadlockTest (ID, Value) VALUES (1, 'Transaction 2');
COMMIT TRANSACTION;
In this script:

Transaction 1 inserts rows with IDs 1 and 2 into DeadlockTest.
Transaction 2 inserts rows with IDs 2 and 1 into DeadlockTest.
Both transactions simulate a delay to illustrate a scenario where deadlock might occur due to contention for the same resources.
The scenario remains the same as described earlier, where if both transactions attempt to acquire locks on resources in a different order, a deadlock can occur.

User
value should be int!!
ChatGPT
Apologies for the oversight. Let me correct that for you. Here are the queries rewritten with the Value column as an INT type:

sql
Copy code
-- Transaction 1
BEGIN TRANSACTION;
INSERT INTO DeadlockTest (ID, Value) VALUES (1, 100);
WAITFOR DELAY '00:00:05'; -- Simulate a delay
INSERT INTO DeadlockTest (ID, Value) VALUES (2, 200);
COMMIT TRANSACTION;

-- Transaction 2
BEGIN TRANSACTION;
INSERT INTO DeadlockTest (ID, Value) VALUES (2, 200);
WAITFOR DELAY '00:00:05'; -- Simulate a delay
INSERT INTO DeadlockTest (ID, Value) VALUES (1, 100);
COMMIT TRANSACTION;
In this script:

Transaction 1 inserts rows with IDs 1 and 2 into DeadlockTest.
Transaction 2 inserts rows with IDs 2 and 1 into DeadlockTest.
Both transactions simulate a delay to illustrate a scenario where deadlock might occur due to contention for the same resources.
If both transactions attempt to acquire locks on resources in a different order, a deadlock can occur.

User
thats ok just need truncate table DeadlockTest in transaction 1
ChatGPT
Understood. Here's the revised version of the transactions, where Transaction 1 truncates the DeadlockTest table:

sql
Copy code
-- Transaction 1
BEGIN TRANSACTION;
TRUNCATE TABLE DeadlockTest; -- Truncate the table
INSERT INTO DeadlockTest (ID, Value) VALUES (1, 100);
WAITFOR DELAY '00:00:05'; -- Simulate a delay
INSERT INTO DeadlockTest (ID, Value) VALUES (2, 200);
COMMIT TRANSACTION;

-- Transaction 2
BEGIN TRANSACTION;
INSERT INTO DeadlockTest (ID, Value) VALUES (2, 200);
WAITFOR DELAY '00:00:05'; -- Simulate a delay
INSERT INTO DeadlockTest (ID, Value) VALUES (1, 100);
COMMIT TRANSACTION;
In this script:

Transaction 1 starts by truncating the DeadlockTest table to remove all existing records.
It then proceeds to insert new rows with IDs 1 and 2 into DeadlockTest.
Transaction 2 inserts rows with IDs 2 and 1 into DeadlockTest.
Both transactions simulate a delay to illustrate a scenario where deadlock might occur due to contention for the same resources.
If both transactions attempt to acquire locks on resources in a different order, a deadlock can occur.

User
better to put it out ot transaction
ChatGPT
Certainly, if the truncation of the table doesn't need to be part of the transaction, it's better to perform it outside the transaction to reduce the likelihood of deadlocks and improve concurrency. Here's how you can modify the script:

sql
Copy code
-- Truncate the table outside of the transaction
TRUNCATE TABLE DeadlockTest;

-- Transaction 1
BEGIN TRANSACTION;
INSERT INTO DeadlockTest (ID, Value) VALUES (1, 100);
WAITFOR DELAY '00:00:05'; -- Simulate a delay
INSERT INTO DeadlockTest (ID, Value) VALUES (2, 200);
COMMIT TRANSACTION;

-- Transaction 2
BEGIN TRANSACTION;
INSERT INTO DeadlockTest (ID, Value) VALUES (2, 200);
WAITFOR DELAY '00:00:05'; -- Simulate a delay
INSERT INTO DeadlockTest (ID, Value) VALUES (1, 100);
COMMIT TRANSACTION;
In this script:

The TRUNCATE TABLE DeadlockTest; statement truncates the DeadlockTest table outside of any transaction.
Transaction 1 and Transaction 2 proceed with their inserts without performing any table truncation.
Both transactions simulate a delay to illustrate a scenario where deadlock might occur due to contention for the same resources.
By removing the table truncation from the transaction, you reduce the likelihood of conflicts and deadlocks, especially in scenarios where multiple transactions are accessing the same table concurrently.