| Understanding locking [message #648891] |
Sun, 06 March 2016 16:38  |
 |
gorants
Messages: 85
Registered: May 2014
Location: ATL
|
Member |
|
|
Hello experts,
I was reading through online resources to understand locking mechanism. i didnt get the high lighted lines below, why U2 is rollbacked here ? i expected after U2 commit U3 will commit. I have attached PNG will which will explains the below. please help me understand this.
In this case, we have 3 transactions that are all attempting to make changes to a single row in Table A. U1 obtains an exclusive lock on this table when issuing the first update statement. Subsequently, U2 attempts to update the same row and is blocked by U1's lock. U3 also attempts to manipulate this same row, this time with a delete statement, and that is also blocked by U1's lock.
When U1 commits its transaction, it releases the lock and U2's update statement is allowed to complete. In the process, U2 obtains an exclusive lock and U3 continues to block. Only when U2's transaction is rolled back does the U3's delete statement complete.
[mod-edit: imagine inserted into messaged body by bb]
-
Attachment: Capture.PNG
(Size: 27.97KB, Downloaded 1434 times)
[Updated on: Sun, 06 March 2016 20:50] by Moderator Report message to a moderator |
|
|
|
| Re: Understanding locking [message #648892 is a reply to message #648891] |
Sun, 06 March 2016 17:16  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
You are looking for things that are not there. U2 just happens to rollback. Same way U2 could have committed. It doesn't matter rollback/commit - all that matters is U2 completed transaction which in turn released the lock and then and only then U3 can delete the row.
SY.
[Updated on: Sun, 06 March 2016 17:17] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Understanding locking [message #648904 is a reply to message #648891] |
Mon, 07 March 2016 01:58 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
What is the document you are studying? The reason I ask is is that there is no "start transaction" in Oracle SQL. Perhaps your doc is describing how rings work in MySQL, or something else.
|
|
|
|
| Re: Understanding locking [message #648915 is a reply to message #648904] |
Mon, 07 March 2016 07:09 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Well, there is no physical "start transaction" command however there is transaction definition:
Beginning of a Transaction
A transaction begins when the first executable SQL statement is encountered.
An executable SQL statement is a SQL statement that generates calls to a database instance, including DML and DDL statements and the SET TRANSACTION statement.
And you can find "Transaction begins" in many diagrams within Oracle documentation just to tell readers there are no pending uncommitted changes.
SY.
|
|
|
|
| Re: Understanding locking [message #648916 is a reply to message #648915] |
Mon, 07 March 2016 07:31 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Yes, but I think Oracle breaks the rules: it doesn't count a SELECT as starting a transaction, though (I think) ANSI SQL does. Or to put it another way, Oracle's READ COMMITTED isolation level doesn't conform to the REPEATABLE READ standard.
I suppose one could say that SET TRANSACTION is the equivalent of START TRANSACTION, but how many people ever use that?
|
|
|
|
|
|
| Re: Understanding locking [message #648931 is a reply to message #648916] |
Mon, 07 March 2016 16:14 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
John Watson wrote on Mon, 07 March 2016 08:31Yes, but I think Oracle breaks the rules: it doesn't count a SELECT as starting a transaction, though (I think) ANSI SQL does. Or to put it another way, Oracle's READ COMMITTED isolation level doesn't conform to the REPEATABLE READ standard.
I suppose one could say that SET TRANSACTION is the equivalent of START TRANSACTION, but how many people ever use that?
Not completely true. Readers never block writers, except if the select is done with a FOR UPDATE on the select. Then it will block writers.
|
|
|
|
|
|
|
|
| Re: Understanding locking [message #648971 is a reply to message #648966] |
Wed, 09 March 2016 00:57 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
gorants wrote on Wed, 09 March 2016 03:58Thanks for the details .. As mentioned in the table diagram , rollback happen if there is an exceptions not because of locking -- is my understanding is correct?
If I understand this correctly, the answer is that a statement (not the transaction) will be automatically rolled back if it raises an error.
|
|
|
|
| Re: Understanding locking [message #648992 is a reply to message #648971] |
Wed, 09 March 2016 09:38 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
|
Your understanding is incorrect. A rollback ONLY occurs if a rollback command is given or the user terminates the session. And terminating the session will commit or rollback depending on the settings in the application. If you want to commit, issue a COMMIT command. If you want to rollback, issue a ROLLBACK command.
|
|
|
|
|
|
| Re: Understanding locking [message #649006 is a reply to message #649005] |
Wed, 09 March 2016 11:43 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
|
If it hits an error, it never gets fired. But if you have 10 statements that are executed and it fails on the 11th, the preceding 10 are NOT rolled back.
|
|
|
|
|
|
| Re: Understanding locking [message #649009 is a reply to message #649008] |
Wed, 09 March 2016 11:49 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
|
Yes, I agree. However he said that he assumed that an error rolled back everything. It only rolls back the one statement. That was what I was trying to say. If it was not clear, sorry.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
