I find a technology named "next-key locking" used in CUBRID. I have some questions about it.
Suppose an index contains the following value: 1 5 6 7 8 12, a transaction will delete 8, then both 8 and 12 will be locked.
1. I want to know just 8 and 12 be locked or the gap between 8 and 12 aslo be locked?
2. I know don't why 12 also be locked. It seems that just lock 8 is enough.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
In CUBRID, next key locking is implemented by locking the next object too.
Why 12 also?
Let's image when one transaction is doing SELECT * FROM t WHERE id>7; and the other concurrent transaction is doing INSERT INTO t(id) VALUES (10). What happens if the first transaction is doing the SELECT again? What happens if the second transaction is rolled back? I believe you can catch up the idea of "Next key locking".
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
OK, thank you, I will read the document later.
Hmm, I think for phantom problem, just lock 12 is not enough. If another transaction is doing INSERT INTO t(id) VALUES (13), and transaction 1 do SELECT * FROM t WHERE id>7 again, transaction 1 will also see the value 13?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Yes. you're right. My example is not proper.
Fixing it to "SELECT * FROM t WHERE id>7 AND id<=12.
If the first transaction execute "SELECT * FROM t WHERE id>7" then next key locking will be held on 7 and +INF, not 12.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
For SELECT * FROM t WHERE id>7 AND id<=12, lock will be held on 7 and 12, this can prevent phantom. I can understand it now.
But, when create an index, the next key will also be lock. I'm not very clear about this. For example:
if transaction 1 do INSERT INTO t(id) VALUES (2), the next key "5" will be locked. I don't know why "5" need get a lock, it seems just lock "2" is enough.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Let's see what's the problem if there's no NX lock.
Let's assume that table t has a unique index on id.
T1 is executing DELETE FROM t WHERE id=5 but not committed yet. T1 is holding X-Lock on the object whose value is 5.
T2 is executing UPDATE t SET id=5 WHERE id=6. It will be success because T2 can acquire X-Lock on the object whose value is 6.
Now, there are objects whose values are 1, 5, 7, 8, and 12, respectively.
T1 is to be ROLLBACKed. While undoing T1's actions, T1 is trying to insert the value 5 which was deleted by the T1.
Unique violation, and rollback is failed!!!!
Understandable?
OK, now you can reply to this message with the scenario which has NX-Lock.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
If there has NX lock.
T1: DELETE FROM t WHERE id=5
T1 will hold NX lock on 6
T2: UPDATE t SET id=5 WHERE id=7
T2 can acquire X-Lock on the object whose value is 7,but cannot acquire the NX lock on 6, so T2 will wait.
T1: rollback
T1 will insert value 5 and release the NX lock. Then T2 acquired the NX lock, update finished.
is it like this?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I find a technology named "next-key locking" used in CUBRID. I have some questions about it.
Suppose an index contains the following value: 1 5 6 7 8 12, a transaction will delete 8, then both 8 and 12 will be locked.
1. I want to know just 8 and 12 be locked or the gap between 8 and 12 aslo be locked?
2. I know don't why 12 also be locked. It seems that just lock 8 is enough.
The answer is why the technology is called "next key locking" or "key range locking". The main purpose of next key locking is to avoid phantom problem.
This technique is suggested by the well-know paper "ARIES/KVL" by IBM DB2. (http://www.vldb.org/conf/1990/P392.PDF)
You can read also MySQL manual, http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html.
In CUBRID, next key locking is implemented by locking the next object too.
Why 12 also?
Let's image when one transaction is doing SELECT * FROM t WHERE id>7; and the other concurrent transaction is doing INSERT INTO t(id) VALUES (10). What happens if the first transaction is doing the SELECT again? What happens if the second transaction is rolled back? I believe you can catch up the idea of "Next key locking".
OK, thank you, I will read the document later.
Hmm, I think for phantom problem, just lock 12 is not enough. If another transaction is doing INSERT INTO t(id) VALUES (13), and transaction 1 do SELECT * FROM t WHERE id>7 again, transaction 1 will also see the value 13?
Yes. you're right. My example is not proper.
Fixing it to "SELECT * FROM t WHERE id>7 AND id<=12.
If the first transaction execute "SELECT * FROM t WHERE id>7" then next key locking will be held on 7 and +INF, not 12.
For SELECT * FROM t WHERE id>7 AND id<=12, lock will be held on 7 and 12, this can prevent phantom. I can understand it now.
But, when create an index, the next key will also be lock. I'm not very clear about this. For example:
if transaction 1 do INSERT INTO t(id) VALUES (2), the next key "5" will be locked. I don't know why "5" need get a lock, it seems just lock "2" is enough.
Let's see what's the problem if there's no NX lock.
Let's assume that table t has a unique index on id.
T1 is executing DELETE FROM t WHERE id=5 but not committed yet. T1 is holding X-Lock on the object whose value is 5.
T2 is executing UPDATE t SET id=5 WHERE id=6. It will be success because T2 can acquire X-Lock on the object whose value is 6.
Now, there are objects whose values are 1, 5, 7, 8, and 12, respectively.
T1 is to be ROLLBACKed. While undoing T1's actions, T1 is trying to insert the value 5 which was deleted by the T1.
Unique violation, and rollback is failed!!!!
Understandable?
OK, now you can reply to this message with the scenario which has NX-Lock.
If there has NX lock.
T1: DELETE FROM t WHERE id=5
T1 will hold NX lock on 6
T2: UPDATE t SET id=5 WHERE id=7
T2 can acquire X-Lock on the object whose value is 7,but cannot acquire the NX lock on 6, so T2 will wait.
T1: rollback
T1 will insert value 5 and release the NX lock. Then T2 acquired the NX lock, update finished.
is it like this?