0

As an application developer I'm used to using databases transactions only as a way to play in modifications after a user has clicked "save".

That's the way most database servers I'm familiar with expect their transactions to be used and they don't like long-lived transactions. Under many circumstances, they lead to locking out other writers or even readers with very little help to deal with such blocks.

But I'm only familiar with a slice of the database world - I use mostly SQL Server and have seen some MySQL. Those databases are mostly used as an application storage with business logic in the database itself mostly being reduced to generating unique ids in one way or another.

I could imagine that other servers, such as Oracle, have different expectations.

The approach I'm interested in is that where when the user clicks "edit", a transaction is opened and all edits the user makes are immediately send to the database. All business logic is therefore applied immediately and as such visible in the user interface even before the "save" (a.k.a. the "commit").

This paradigm would make several things easier:

  • The application doing the edit doesn't have to manage preliminary ids for unsaved rows as ORMs do.
  • The user could get feedback from business logic in the database server, such as values of computed columns or the resulting effects of triggers, even before they commit to the changes.
  • Constraint violations could be detected earlier in the edit if a lot of changes are made before the commit.
  • If the database server has good support for it, conflicting editing from multiple transactions could lead to better error messages such as "user abc is editing row xyz".

I've investigated the state of SQL Server support for this approach and it's something on the verge of being possible but probably usually a bad idea in practice. The main issue is that writers there lock each other out even under snapshot isolation. In particular, the writer who writes first wins, not the one who commits first.

My question is: Are there database servers that support this scenario better? What does Oracle have to say to this, for example? In particular, the server would have to

  • Allow concurrent writes to the same row without blocking and having the first committer win.
  • Therefore, an uncommitted, dangling transaction that has written should not affect other users at all. If a different user commits a conflicting write that should work, and the dangling transaction just becomes uncommittable (or is just automatically rolled back at that point).

I'm looking into this for my generic database browser;

5
  • 4
    I hope your users don't take a break or go home after clicking edit. Search the internet for optimistic concurrency. It applies to all RDBMS products.
    – Dan Guzman
    Oct 10, 2022 at 21:13
  • @DanGuzman Such dangling transactions wouldn't be an issue under a database server that allows concurrent writes to the same row with the first committer winning. I know that's not SQL Server, but that's why I'm asking about other servers. I'm updating the question to make this more clear.
    – John
    Oct 10, 2022 at 21:20
  • 1
    "probably usually a bad idea in practice". The last committer will always win unless you code for it. In cases where concurrent edits are possible, a checksum of the original value is maintained in the user's app session for comparison. No lock on the database table, and no open transaction. When ready to commit, the app compares the original checksum to the current checksum of the column in the DB to make sure no one else has committed a change. If no other change, then commit. If a change, then handle it: prevent or allow overwrite of the other change, refresh the app, etc.
    – pmdba
    Oct 10, 2022 at 23:37
  • business logic can still be applied in the DB (where it should be), with error returned to the app to handle if there's an issue with the transaction and the commit fails.
    – pmdba
    Oct 10, 2022 at 23:38
  • Holding locks for a long time is going to be problematic whichever DBMS you use. There is no engine that deals with this correctly, unless you use SNAPSHOT and optimistic concurrency, in which case you are in for a bunch of other problems like write skew. Oct 12, 2022 at 13:03

2 Answers 2

1

Allow concurrent writes to the same row without blocking and having the first committer win

The answer is no, Oracle doesn't support this any more than SQL Server. But, as has now been pointed out several times, client-side optimistic concurrency is the way to implement this behavior: Each writer writes with a query that fails or affects zero rows if another session has modified the row since it was read.

2
  • And as I myself pointed out, real software often needs to write a bunch of related rows at the same time, the relation keys of which usually being generated by the database. To have that and other database business logic be retrievable before the changes are committed would be a great simplification and feature for database client code. Sometimes I wonder why I write so much text in my questions, apparently nobody's reading it anyway.
    – John
    Oct 11, 2022 at 21:41
  • But thanks for answering.
    – John
    Oct 11, 2022 at 21:42
0

Starting a transaction when the user clicks edit and leaving it open until he saves is a bad idea. What if the user let's his screen open for hours?

There are many ways to deal with otherwise.

For example, in the application where I work, the developers have done the following:

  • They have a Timestamp column in the table.
  • They load the Timestamp value in a variable when the user clicks "edit".
  • When they click save, they UPDATE the row WHERE LastSavedTimestamp=value
  • If @@ROWCOUNT = 0 (It means the row was updated by someone else in the meantime.) they show a message to the user saying someone else modified the row and they need to refresh the screen.

That's only an example. A lot of applications need to deal with concurrent updates. I'm sure you can find some other examples online.

5
  • How do you get the id for inserted rows to use for related rows? How do you get the value of computed columns before the commit? How do you learn about foreign key violations before the commit? There's plenty of stuff you can't easily work around this limitation.
    – John
    Oct 11, 2022 at 21:46
  • 1
    @John 1. Use an OUTPUT or RETURNING clause to get IDs within the stored procedure that saves the data. 2. Computed columns don't exist, they are computed, equally you can just compute it client side. 3. FK violations should almost never happen anyway: worst case scenario is another user deletes a row you are depending on during your transaction, in which case you have a problem either way. Oct 12, 2022 at 10:27
  • @Charlieface 1. I don't have any stored procedures - my tool works on any schema as-is. 2. I can compute T-SQL client-side? Not really. 3. Ok, FKs are a stretch, but UKs should be tested before the commit.
    – John
    Oct 12, 2022 at 12:53
  • 1
    @John Whatever, your SQL batch that inserts into multiple tables. Yes you can calculate computed columns, why wouldn't you? It's just a matter of using an ORM that can parse out the definition. Or you can return it while inserting using an OUTPUT clause as mentioned. UK violation is normally a highly unlikely scenario also (what could happen? two people entering data from the same entry sheet?) so you can just throw an error if it happens. Oct 12, 2022 at 13:02
  • @Charlieface It's a bit difficult to explain. I'm developing a generic database tool, not an application with a specific database schema. I've linked it in the question. - The tool is supposed to work with all computed column definitions for all the users of my tool, not just some specific ones that I wrote myself.
    – John
    Oct 12, 2022 at 13:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.