• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Secure transaction fail

 
giov racconi
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm trying to have a transaction t1: R1(a) , W1(a)
and t2: R2(a), W2(a)

if the sequence is R1(a) R2(a) W1(a) W2(a)

I want T2 will not be committed since (a) is changed (when t2 try to write) from the first reading.

I've tried to develop this thing, but doesn t work.
After waiting some seconds the output is:

wt1 name reaeded Massimo
wt2 name reaeded Massimo
wt1 try to write
java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
....
...


 
Ulf Dittmer
Rancher
Posts: 42969
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You need to use the two Connection objects properly - one for R1/W1 and one for R2/W2. As it is, one connection handles R1, while the other connection handles R2, W1 and W2.

The exception is an indication that the connection handling R1 is never issuing either commit or rollback.
 
giov racconi
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ulf Dittmer wrote:You need to use the two Connection objects properly - one for R1/W1 and one for R2/W2. As it is, one connection handles R1, while the other connection handles R2, W1 and W2.

The exception is an indication that the connection handling R1 is never issuing either commit or rollback.



It doesn't seem to me..

here the output with other logs:

conn com.mysql.jdbc.JDBC4Connection@384065
wt1 name readed Massimo
conn com.mysql.jdbc.JDBC4Connection@1a68ef9
wt2 name readed Massimo
wt1 try to write
conn com.mysql.jdbc.JDBC4Connection@384065
java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
...
....



 
Ulf Dittmer
Rancher
Posts: 42969
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're right, what I wrote earlier isn't correct; somehow I was thinking about a static context.

It might be because the isolation level is set to serializable. The DB doesn't release the lock before the transaction is committed or rolled back, because any write happening in the record that was read during the SELECT would make the transaction NOT serializable. You can try what happens with a less strict isolation level.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The outcome of your scenario depends on the database (and generally on the isolation level, but you've set it to serializable). Which, by the way, is why it is incredibly hard to get "database agnostic" software right even in quite straightforward cases.

There are databases that might lock rows when you select or read them to ensure consistency, which is your case. Ulf explained it nicely.

Other databases don't lock rows that were read and provide consistency by other means (multiversioning), among these eg. Oracle or some later versions of MS SQL Server, which can be configured to run in this mode too. In these databases the w1 write will succeed, since the row was not locked by the reads. Update of the row will cause it to be locked, but there is a commit in w1, so the lock is immediately freed. The w2 update will subsequently fail, in Oracle the error code is "ORA-08177: can't serialize access for this transaction" (I've tried it using sqlplus).

I assume this is the outcome you expected from your scenario.
 
giov racconi
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ulf Dittmer wrote:You're right, what I wrote earlier isn't correct; somehow I was thinking about a static context.

It might be because the isolation level is set to serializable. The DB doesn't release the lock before the transaction is committed or rolled back, because any write happening in the record that was read during the SELECT would make the transaction NOT serializable. You can try what happens with a less strict isolation level.




Martin Vajsar wrote:The outcome of your scenario depends on the database (and generally on the isolation level, but you've set it to serializable). Which, by the way, is why it is incredibly hard to get "database agnostic" software right even in quite straightforward cases.

There are databases that might lock rows when you select or read them to ensure consistency, which is your case. Ulf explained it nicely.

Other databases don't lock rows that were read and provide consistency by other means (multiversioning), among these eg. Oracle or some later versions of MS SQL Server, which can be configured to run in this mode too. In these databases the w1 write will succeed, since the row was not locked by the reads. Update of the row will cause it to be locked, but there is a commit in w1, so the lock is immediately freed. The w2 update will subsequently fail, in Oracle the error code is "ORA-08177: can't serialize access for this transaction" (I've tried it using sqlplus).

I assume this is the outcome you expected from your scenario.



Yes, I want the outcome discripted by Martin Vajsar

I' using mysql 5.5

So my DBMS wait for all the reader try to access to the locked resources and then choose one of them releasing the lock. Only one transaction can commit, others rollback.
I think this beacause running in separate threads only one transaction commits





Another thing: if I use the one-thread sample with a less strictly level, such as repeteable read, the two transaction are committed
 
giov racconi
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A thing doesn't sound good:
if the record is locked (read lock) after R1, R2 at least W1 or W2 could be committed without waiting, but it isn't true
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, I want the outcome discripted by Martin Vajsar

I' using mysql 5.5

I guess that the exact behaviour as described is unfeasible in MySQL, but someone more knowledgeable of MySQL might shed more light on it - I don't use MySQL.



So my DBMS wait for all the reader try to access to the locked resources and then choose one of them releasing the lock. Only one transaction can commit, others rollback.
I think this beacause running in separate threads only one transaction commits

It has more to do with separate database connections than threads. Database does not know about your threads, it knows only sessions (connections) and transactions. Your transactions are in effect deadlocked and by failing one of them, the other is given possibility to finish successfully.



Another thing: if I use the one-thread sample with a less strictly level, such as repeteable read, the two transaction are committed

Isolation level is important here, not the threads.
And while the transactions are committed, the situation you've simulated is called "lost update". Changes written by W1 were overwritten by W2 without W2 being alerted that it happened.



if the record is locked (read lock) after R1, R2 at least W1 or W2 could be committed without waiting, but it isn't true

Try to put rollback into your catch clause. When W1 fails due to lock timeout, if it frees its locks, W2 could succeed in updating the data. I cannot try it myself, I don't have MySQL. Two notes though:

1) It might be sensitive to timing. If both transaction are started close to each other, locks from R2 might time out (causing an exception) before rollback frees the lock from R1.

2) In this case, the update that came LATER will succeed and the update that was attempted EARLIER will fail. It might not be exactly what you'd want.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic