New answers tagged

0

Add the 'available' check? And didn't you mean to use the same table? UPDATE stock set sold = sold + 4 where id = 1 AND sold + x <= available; The FOR UPDATE say "This process may change this row(s); keep your hands off." and "If any other process is busy mucking with this row(s), I should wait." If the WHERE clause on the UPDATE fails, ...


1

For SQL Server: But what's about single statements? In SQL Server, there is 4 transaction isolation levels (in pessimistic locking model). Default transaction isolation level is Read Committed, and locks are placed on a statement level. If you have a transaction that has 2 statements inside it that retrieve same data, and in the middle of that ...


1

Serializable means that there is some order the transactions can be run in without overlapping and we'll end up with the same answers and the same state of the data base as we get by running the transactions in parallel with serializable isolation level. Given two transactions, A and B, the only valid states of the system are All of A followed by all of B, ...


1

Give this a try: BEGIN; SELECT * FROM products WHERE exported = 0 INTO OUTFILE "x/y/z" FOR UPDATE; UPDATE products SET exported = 1 WHERE exported = 0; COMMIT; And I don't think it matters what the isolation mode is. I am not sure of mixing INTO OUTPUT with FOR UPDATE; if it give you trouble, make it two SELECTs. Note that this does obviate your program ...


3

There is no "first update", "second update" here, there is one statement, one atomic update. And this update produces 2 rows that are the same. If you want "the second" update to not succeed you should write 2 explicit updates, not one, inside 1 transaction. But in this case nolock would have no sense because the same transaction sees its own uncommitted ...


2

On the contrary .. I think your database server DOES read the updated values... to elaborate: simply visualize (if needed on a piece of paper), what happens during your update before (1,1),(1,2),(2,1),(2,2) 1st update -> (3,1),(1,2),(2,1),(2,2) 2nd update -> (3,1),(3,2),(2,1),(2,2) 3rd update -> (3,1),(3,2),(3 ***,1),(2,2) If I understand your ...


Top 50 recent answers are included