CockroachDB and Postgres - Releasing Locked Rows with idle_in_transaction_session_timeout
Download full source code.
There are many scenarios where you might want to lock a row in a table. You might need to read the row, perform some other operations, and then update the row. During the time between reading and updating, you don’t want another transaction to update the row.
In CockroachDB, you can use the SELECT...FOR UPDATE statement to lock the row. This will prevent other transactions from updating the row until the transaction that locked the row commits or rolls back.
This is great when another transaction uses SELECT...FOR UPDATE...SKIP LOCKED because the second query will skip right past the rows locked by the first query, no waiting for the rows to unlock.
But, what if the application that locks the row crashes or freezes? The row will remain locked. You don’t want to end up in a scenario where you have to release the lock manually.
The idle_in_transaction_session_timeout value can be set for a transaction, role, or even database. Once the time is exceeded by an idle transaction, the database will abort the transaction and unlock any locked rows.
This can be set in a few different ways - - inside an explicit transaction - at the role level - at the database level When a transaction has been idle for the specified time, the database will release any locked rows.
Here are the three ways in SQL -
1SET idle_in_transaction_session_timeout=5000; -- in a transaction
2
3ALTER ROLE somerole SET idle_in_transaction_session_timeout = 5000; -- role level
4
5ALTER DATABASE somedb SET idle_in_transaction_session_timeout = 5000; -- database levelTo demonstrate how to use this in an application requires a bit of a contrived example. I have to create two DbContexts so I can start two transactions.
- The first method will use a transaction to lock a row, set the
idle_in_transaction_session_timeoutto 2 seconds and then sleep. - The second method will use a transaction to select and update the same row, but it is locked, so it will wait until the first lock is released, and then it will update the row.
- The first method resume, but the transaction has lost its lock on the row, and fails.
The output of the application looks like this, with MethodA using the first transaction, and MethodB using the second transaction -
MethodA_LockAndUpdateRow: Timeout set to 3000ms
MethodA_LockAndUpdateRow: got row - 1 - Practical Cotton Car - Electronics - $87.43 - 94442342
MethodA_LockAndUpdateRow: delaying for 3000ms...
MethodB_WaitForRowToBeReleased: Waiting for row to be released...retrying 1.
MethodB_WaitForRowToBeReleased: Waiting for row to be released...retrying 2.
MethodB_WaitForRowToBeReleased: Waiting for row to be released...retrying 3.
MethodB_WaitForRowToBeReleased: After waiting 2594ms, got row - 1 - Practical Cotton Car - Electronics - $87.43 - 94442342
MethodA_LockAndUpdateRow: delay over, resuming...
MethodA_LockAndUpdateRow: will try to update row...
MethodA_LockAndUpdateRow: can't update - An established connection was aborted by the software in your host machine.
MethodA_LockAndUpdateRow: can't rollback - An established connection was aborted by the software in your host machine.As you can see, the first method grabs a row from the database and goes to sleep. During that sleep, the second method tries to grab the same row, but can’t, so it retries.
Once the idle_in_transaction_session_timeout is reached, the first method loses its lock on the row, and the second method can grab the row and update it.
Then the first method resumes and tries to update the row but can’t.
Here is the source code -
1using CockroachReleaseLockedRows.Data;
2using Microsoft.EntityFrameworkCore;
3using System.Diagnostics;
4
5SalesContext salesContext1 = new SalesContext(new DbContextOptionsBuilder<SalesContext>()
6 .UseNpgsql("Host=localhost;Port=26257;Database=salesdb;Username=root;Password=admin;Include Error Detail=true").Options);
7
8SalesContext salesContext2 = new SalesContext(new DbContextOptionsBuilder<SalesContext>()
9 .UseNpgsql("Host=localhost;Port=26257;Database=salesdb;Username=root;Password=admin;Include Error Detail=true").Options);
10
11// await salesContext1.Database.EnsureDeletedAsync(); // ensure the database is deleted
12await salesContext1.Database.EnsureCreatedAsync();
13await salesContext1.SeedAsync().ConfigureAwait(false);
14
15var task1 = MethodA_LockAndUpdateRow(1, 3000, salesContext1);
16var task2 = MethodB_WaitForRowToBeReleased(1, 500, salesContext2);
17
18await Task.WhenAll(task1, task2).ConfigureAwait(false);
19
20/// <summary>
21/// Method starts a transaction with a time limit of 2 seconds.
22/// Then it selects a row and locks it.
23/// The method waits for 3 seconds before trying to update the row.
24async Task MethodA_LockAndUpdateRow(int product_id, int delay, SalesContext salesContext)
25{
26 using var transaction = salesContext.Database.BeginTransaction();
27 try
28 {
29 salesContext.Database.ExecuteSqlRaw("SET idle_in_transaction_session_timeout=2000;"); // the transaction will be closed after 2 seconds
30 Console.WriteLine($"MethodA_LockAndUpdateRow: Timeout set to {delay}ms");
31
32 var product = salesContext.Products.FromSql($"SELECT * FROM products WHERE product_id={product_id} FOR UPDATE SKIP LOCKED").SingleOrDefault();
33 Console.WriteLine($"MethodA_LockAndUpdateRow: got row - {product}");
34
35 Console.WriteLine($"MethodA_LockAndUpdateRow: delaying for {delay}ms...\n");
36 await Task.Delay(delay); // simulate some processing time
37 Console.WriteLine($"\nMethodA_LockAndUpdateRow: delay over, resuming...");
38
39 Console.WriteLine($"MethodA_LockAndUpdateRow: will try to update row...");
40 var updateProductResult = salesContext.Database.ExecuteSql($"UPDATE products set name='SOME NEW NAME' WHERE product_id={product_id}");
41
42 transaction.Commit(); // the commit won't work, because the timeout has been reached
43 }
44 catch (Exception ex)
45 {
46 Console.WriteLine($"MethodA_LockAndUpdateRow: can't update - {ex.GetBaseException().Message}");
47 try
48 {
49 transaction.Rollback();
50 }
51 catch (Exception rollbackException)
52 {
53 Console.WriteLine($"MethodA_LockAndUpdateRow: can't rollback - {rollbackException.GetBaseException().Message}");
54 }
55 }
56}
57
58// Method starts a transaction.
59// Tries to select a row and lock it, and retries every 500ms until it gets the row.
60async Task MethodB_WaitForRowToBeReleased(int product_id, int delay, SalesContext salesContext)
61{
62 int retryCount = 0;
63 Stopwatch stopwatch = new Stopwatch();
64 stopwatch.Start();
65 using var transaction = salesContext.Database.BeginTransaction();
66 try
67 {
68 Product? product = null;
69 await Task.Delay(delay);
70 while (true)
71 {
72 product = salesContext.Products.FromSql($"SELECT * FROM products WHERE product_id={product_id} FOR UPDATE SKIP LOCKED").FirstOrDefault();
73 if (product is not null)
74 {
75 break;
76 }
77 else
78 {
79 Console.WriteLine($"\t\tMethodB_WaitForRowToBeReleased: Waiting for row to be released...retrying {++retryCount}.");
80 }
81 await Task.Delay(delay);
82 }
83 stopwatch.Stop();
84 Console.WriteLine($"\t\tMethodB_WaitForRowToBeReleased: After waiting {stopwatch.ElapsedMilliseconds}ms, got row - {product}");
85 }
86 catch (Exception ex)
87 {
88 Console.WriteLine($"MethodB_WaitForRowToBeReleased: {ex.GetBaseException().Message}");
89 }
90}
91_Download [full source code.](media/CockroachReleaseLockedRows.zip)_