Ruby Multi-threaded assistance request...

[Mel Riffe]

Hey Folks!

I have a situation that I think needs some multi-threaded-type programming, which is outside my comfort zone.

There's a long-running process my client is trying to shorten by starting multiple SSH terminal sessions and kicking off concurrent runs. During this long-running process there comes a point where things get number, more or less, sequentially. This number needs to be unique within the entire database. But I can't use an auto-incrementing database field because the value, in part, is derived from a specified date. But I digress.

I'm using a database table to contain the 'next' value, incrementing it once it's been used.

Using ActiveRecord's lock didn't prevent duplicates.

Using Thread.exclusive around the 'next' operation didn't prevent duplicates.

Using Thread.exclusive at a level higher, prevent duplicates within a set but still allows duplicates across sets.

I'm truly out of ideas. Right now all suggestions are welcomed and greatly appreciated.

--Mel

[Charles Feduke]

If you are using PostgreSQL you can use advisory locks (see http://www.postgresql.org/docs/current/static/explicit-locking.html section 13.3.5).

However you should also be able to incorporate the date into a sequence through a function and a table trigger once again, if using PostgreSQL.

Other RDBMSes should have something similar to PGSQL's advisory locks (I know both MS SQL Server and Oracle do).

Is it possible to reorder the problem so the number is generated first from a serial access number generator, and then utilize it later? That is, if a process with a reserved number fails, is it okay to potentially have holes in the sequence? (This can create edge cases where jobs that kick off around midnight may complete the following day but have the previous day's date in their identifier.)

--
You received this message because you are subscribed to the Google Groups "RefactorRVA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to refactorrva...@googlegroups.com.
To post to this group, send email to refac...@googlegroups.com.
Visit this group at http://groups.google.com/group/refactorrva.
For more options, visit https://groups.google.com/d/optout.

[Jacques Fuentes]

I'm with Charles on this one. What you want to do is better served by using explicit locking or via triggers/functions within the right transaction isolation levels. Your RDBMS (excluding MySQL MYISAM) is built to solve these types of problems much easier than in Rails.

Further, it sounds like these "multiple SSH runs" are `rake` commands that loads the Rails environment? Are they creating multiple processes? If so, threads won't save you. In the case of a forked webserver (unicorn & some versions of passenger & puma), threads won't save you.

[Justin Etheredge]

Using lock in Activerecord causes a 'select ... for update'. This should cause any operations that occur outside of the existing transaction to wait on that lock to release. Is it possible you're not using lock within an explicit transaction?

[Jamie Orchard-Hays]

Seems like the FB should be able to give you an incremented number quite easily. It won't matter which thread or process accesses when, since the FB is coordinating it.

What FB are you using?

Another strategy is to use UUIDs rather than Ints. They'll be unique no matter where you generate them. 

(sent from an iPhone)

[Bob Larrick]

+1 for using UUID if possible.

If there's a significant amount of logic around generating the number i.e. if you have to incorporate the specified date, the angle of saturn, eye of newt, etc. it might be simpler to write a small ruby process that runs outside the main app/rake task.  This could accept the specified date and other relevant data as input and output a unique number.  

Might be more comfortable to stay in Ruby rather than learn the idiosyncrasies of whatever DB you're in, and would provide a simple single point of syncronization.  

https://gist.github.com/deathbob/69c303d4d250bb75beaf

initializing with value from DB is an exercise left to the reader :)

-Bob

[Jamie Orchard-Hays]

"FB"???? I meant "D

(sent from an iPhone)

[Jamie Orchard-Hays]

"FB"? I meant "DB"!!

(sent from an iPhone)

On Nov 17, 2015, at 8:58 PM, Bob Larrick <lar...@gmail.com> wrote:

[Mel Riffe]

Thanks for the great responses!

First, I'm not using Postgres with this particular project. MySQL InnoDb. I'm not opposed to using Triggers it's just not at the top of my list.

Second, I can't use UUIDs. It's, unfortunately, a combination serial number and widget counter. Based on the a calendar quarter, it is a sequential number assigned to each widget produced. This id is then used, downstream, for data analysis. There's at least 10 years of inertia behind this construct.

Third, I was wondering what Facebook (FB) had to with anything. :-D

Fourth, it is multiple invocations of a rails runner script. However the process (and by this I mean the ruby code that gets executed) doesn't spawn other processes except for queueing jobs to DelayedJob.

Fifth, here is the actual implementation of the widget number generator: https://gist.github.com/melriffe/227fbddc82b66f6db04b I am not using it in an explicit transaction, though. And, by that, I'm assuming ActiveRecord::Base.transaction do ... end, yes?

Right now I'm contemplating moving the generation and assignment of this id outside the main script. I already have access to DelayedJob. I'm thinking I could create a single worker for a named queue, there by forcing a single process through this generation/assignment step (or, at least, that's the theory).

Thoughts? reactions?

[Christopher Wash]

Not sure your appetite for it, but Hazelcast has a construct for getting a cluster-wide unique sequence. 

http://docs.hazelcast.org/docs/3.5/manual/html/idgenerator.html

I've used it before for similar use-cases.  

[Charles Feduke]

If this will only ever execute on a single machine you can open a file exclusively for reading/writing (with a wait timeout or sleep/retry), read the last counter value from the file, increment it, write it back to the file and return the sequence number. The timeout will facilitate other workers access to the data in the file serially and the exclusive lock will permit cross process locking. I think this is the simplest solution to implement and understand but has the least level of scalability.

If this will need to run on multiple machines you'll need something like Zookeeper or another similar construct.

A queue that only ever permits a single worker will also give you the same behavior you're looking for, and you can store the counter value in MySQL without having to worry about locking constructs. (If you only want to queue requests and fire and forget, you can generate a UUID, submit it to the queue, the worker then stores that UUID with its serially generated sequence number in a MySQL table and your process polls that table for the UUID that was submitted. I don't like this solution, its too complex, but its a solution.)

[Al Tenhundfeld]

Yes, you need to wrap both database queries, the lock and the increment, in a transaction, using ActiveRecord::Base.transaction or just model.transaction.

Something like this:

EncounterNumber.transaction do

      en = EncounterNumber.where(:quarter => quarter, :year => year).lock.first_or_create!

      Rails.logger.debug("[#{Process.pid}][EXCLUSIVE][DEBUG] EncounterNumber.next!(#{quarter}, #{year}); #{en.inspect}")

      value = en.value

      en.increment! :value

end

The problem right now is that your increment isn't happening in the same transaction. So, the lock in your first query isn't really blocking other threads. By including the increment in the transaction, it should block other threads until the increment is done (because they can't take the for-update intent-exclusive lock – or whatever it's called in MySQL), and then when it's done, other threads will unblock and see the new value, one will take out a new intent-exclusive lock, blocking others, etc.

Lots of interesting ideas here, but I think it can be solved by simply using a transaction correctly, right?

-Al

On Wed, Nov 18, 2015 at 9:58 AM, Mel Riffe <mri...@gmail.com> wrote:

You received this message because you are subscribed to the Google Groups "Ruby Code Jam" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rubyjam+u...@googlegroups.com.
To post to this group, send email to rub...@googlegroups.com.
Visit this group at http://groups.google.com/group/rubyjam.

[Justin Etheredge]

The solutions in this thread are starting to get a little ridicu… wild and crazy. He can do exactly what he wants with simple database locking. To keep it simple split the creation of the records out from the querying, then wrap the querying piece in an explicit transaction (transaction do … end) and use “lock" when querying the record. You’ll want to catch exceptions from the “create” looking for race conditions where two threads try to create the record at the same time, you’ll get a unique index violation. Also, you could use increment_counter which is an atomic increment.

[Charles Feduke]

> Lots of interesting ideas here, but I think it can be solved by simply using a transaction correctly, right?

Hah yeah. I've lived too long in the world of massive scaling across sharded PGSQL and forgotten about the wonders of transactions. :)

[Mel Riffe]

I like the transaction ideas. I will try that, since it's the least amount of change to the current workflow. I will use Al's suggestion.

Thanks everyone!

[Justin Etheredge]

Yeah, Al’s solution is simpler and will work because MySQL will create a range lock on the index when you use the select … for update when the record is not found. This way you avoid a race condition on the create without any additional checks. I was thinking that because the row was not there, you would need extra checks, but I was thinking about it wrong.

[Al Tenhundfeld]

:) Yeah, I'm assuming we're talking about a single database here.

A simple transaction should work, for the reasons Justin mentions.

If it doesn't, I'd look at two things:

1) The transaction isolation level used by your Rails queries. 

InnoDB uses Repeatable Read by default, and I don't think Rails overrides that unless you explicitly tell it to. Using the for-update lock should still make this work, even if your queries are running under Read Committed, but it could be worth checking.

2) The indexes on the encounter_numbers table.

MySQL should take out a next-record lock or gap lock (AKA range locks), but I know there can be some nuances depending on what indexes exist. I think it'll still work without an index covering quarter and year, albeit less efficiently, but if you still have a problem, it's worth experimenting in this area. Read a summary here: https://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.html

Good luck. Please let us know if this approach does in fact solve the issue.

-Al

[Ryan Shriver]

Mel,

I'm sure Al's beautiful DB locking solution will work just fine :-)

But just in case you want option #14 on how to do this...

I'd look towards moving the logic to create the unique ID outside the same database transaction that also inserts the row in the DB. Decouple the problem. A simple service can return the next ID (in-memory call) and this ID is included in the newly inserted row, thus avoiding the DB locking problem you have by doing the next sequence and insert in same transaction.

I've implemented services like this before and they typically have a small persistent table to store values between service stop/start. On start-up, the service updates the table to "reserve the right" to hand out the next 100 sequences independently. Any calls to this service for the next sequence is an in-memory lookup to a local variable to return the next sequence. The variable read should be synchronized to avoid concurrent threads returning the same sequence. In Java, this is done with the synchronized keyword. I believe in Ruby this is done with Mutex. 

The major benefit to this approach is it's easily scalable by starting up more services, on same server or across many servers. Each service just needs to coordinate using the same persistent table, which they only update every 100 transactions to reserve the next sequence range.The 100 sequence range size is configurable of course, I'm just using it here for illustration. 

The downside to this approach is you can have gaps in your unique ID's. If the service reserved the sequence range 0-100 in the table on start-up, then gave out 1-37 and crashed, when it started up next it would reserve 101-200 and start again. There would be no sequences 38-100. 

So if you absolutely have to be contiguous with no gaps, this approach won't work. But if its OK, then this approach is efficient and scales nicely regardless of the underlying DB. You can lower the threshold to minimize data gaps, raise it to increase concurrency and throughput. 

I fully admit this is overkill if you have low transaction volumes or low concurrency! But, if not it's an approach you or others may consider.

-ryan

@ryanshriver

[Mel Riffe]

So far, Al's suggestion is the winner. But I want to thank everyone that offered suggestions.

--Mel

[Jacques Fuentes]

ACID is your friend for both data integrity and psychedelic experiences.