Menu
A program will SELECT several records from a table and update each row, while it can be executed many times, which will lead to several process will complete the same task simultaneously.
How can I prevent two different processes to update the same row in the table. That's to say, how can I ensure each process can SELECT different records? Is there any locks on row-select level in MySQL? Or in this situation, is there any better solution to prevent a single row updating for many times?
548
A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t. c1 is 10 . Record locks always lock index records, even if a table is defined with no indexes.
Inside your transaction, start out selecting the rows that you want to "lock", something like this: SELECT * from TABLE where id = 123 FOR UPDATE; If two different transactions try to do this at the same time, MySQL will make the second one wait until the first one has committed the transaction.
MySQL uses table locking (instead of row locking or column locking) on all table types, except InnoDB and BDB tables, to achieve a very high lock speed.
You're probably looking for with (updlock, holdlock) . This will make a select grab an exclusive lock, which is required for updates, instead of a shared lock. The holdlock hint tells SQL Server to keep the lock until the transaction ends.
You can use a SELECT FOR UPDATE. Inside your transaction, start out selecting the rows that you want to "lock", something like this:
SELECT * from TABLE where id = 123 FOR UPDATE;
If two different transactions try to do this at the same time, MySQL will make the second one wait until the first one has committed the transaction. That way, you'll be assured that the second transaction only looks at the row after the first one is done with it.
170
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
