[MariaDB] Dosegljivost podatkov med DELETE + INSERT operacijo.

Pozdravljeni.

Evo, nov dan, nov izziv.

V tabeli (MariaDB, InnoDB) izvajam "update" na način, da skupaj zvežem prvo DELETE, potem pa INSERT.

Na začetku uporabim START TRANSACTION, potem sledita DELETE in INSERT, ter COMMIT na koncu.

Zanima me, kako (in če) bi se dalo narediti, da so podatki v taki tabeli vedno na volja?

Se pravi med izvajanjem COMMIT-a, kaj se takrat dogaja v bazi?

Ali lahko pride do "vmesne" situacije, ko SELECT ne bi nič vrnil, ker se je DELETE že izvedel, INSERT pa še ne, in je takrat prazna tabela?

Za "vmesne" situacije sploh imamo transakcije, a ne. Drugače pa obstajajo locki na več nivojih in drugi queryji pridno čakajo, da se lock sprosti.

Lahko pa še tako dobro bazo enostavno uničiš performance s slabimi queryji.

Je pa zanimivo koliko je razlike med oraclom in mssqlom kar se deadlockov tiče. Isti SW (in podobna uporaba) samo drugi DB provider in na mssqlu pogosto kakšen deadlock v logih, na oraclu pa nobenega. (ima pa Oracle žal druge cvetke)

Malo offtopic ampak vseeno paše v kontekst: Kako bi razvrstili različne DB serverje po "zmogljivosti" (velikost baze, št. sočasnih zahtevkov): oracle, postgree, mssql, DB2?, mariaDB ?

Če nekomu prihaja do deadlockov, pa ne ve zakaj, je bolj verjetno problem v njegovi kodi kot v nekem sql serverju. Če pa je problem v sql serverju, programer, ki je prišel do tega limita, bi moral to vedet v čem je problem.
Ponavadi je problem, če imaš neko kodo izven sql-a, ki odpira transakcije, potem pa rabi "pol ure", da naredi svoje, ta čas pa pride drug klient, ki bi rad isto. Tu je navidezno problem v sql-u, ki kao nekaj brez veze zaklepa, ampak v resnici ni, dokler smo v rangu hotbureka, problem je v kodi, ki tisto karkoli že dela, zelo verjetno dela nepotrebno dolgo. Dvomim da ima on neko stored proceduro, ki se izvaja tako dolgo, da bi bil to problem, če pa ima, je verjetno spet problem v proceduri, ki je očitno zanič.
V glavnem...če nekdo to sprašuje in ima problem z locki, bi jaz dal dober denar na to da je problem v njegovi kodi, ne pa v sql serverju.

Za select ni neke potrebe, da bi tabela lockala. Tabela je stalno v veljavnem stanju, ni razloga, da ne bi smel selectat iz nje. Poskusi namesto selecta pognat update recimo. In to istih vrstic, ki jih brišeš.

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.

verjetno za eksperimentiranje

To, kar zgoraj pišeš je idiotija! Iz tehničnega vidika možgansko bruhanje brez repa in glave.

kuall je 1. feb 2024 ob 22:34 izjavil:

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

Ti pa zanič! Ker bi moral to vedeti preden si se sploh usedel za računalnik.
Deadlocke še v šoli učijo.