CODEWITHSUNDEEP
mysqllockingnonblocking
Charlie Skilbeck
Charlie Skilbeck

Reputation: 1141

locking a specific record in MySQL

I am about to implement a record locking mechanism as follows:

Table1:
    Field: "ID" (AutoIncrement, Primary)
    Field: "Name" (Text)
    Field: "More fields..."

Table2:
    Field: "ID" (Unique)

In order to lock a specific record in Table1, I'll create a record in Table2 with the same ID. If this creation fails due to such a record already existing, then the record must be locked by another process and I spin. If it succeeds, I have the lock and can modify the record. When I'm done, I delete the lock record in Table2.

My question is whether this is a valid way to implement a per-record lock, and if not, is there such a thing?

Cheers, Charlie.

Upvotes: 0

Views: 717

Answers (1)

ajreal
ajreal

Reputation: 47321

You can use innodb for row-based locking,
or even read-lock instead of impose locking to another table,
(rather to let mysql to handle race-condition)

Upvotes: 1

Related Questions